Using the RefEdit Control. Excel Training VBA 2 lesson 20

The RefEdit Control by default is not available on the Control Toolbox. To locate and add this Control to the Toolbox, right click over any Control on the Toolbox, select Additional Controls and scroll down until you see the Control RefEdit.Ctrl. Simply check the box and click OK. Unfortunately, there is no documentation in the Excel Help for this Control.

Normally used on a user form, the RefEdit control will display the address of a range, or single cell, that you've entered (typed in) or selected, while the UserForm is collapsed, on one or more worksheets. To select a range, click the button in the control to collapse the UserForm , select the range, and then click the button in the control again to expand the UserForm. To select non-contiguous ranges, hold down the Ctrl key or use the comma (or your windows default argument separator) to separate ranges.

If the RefEdit control is embedded on a worksheet, you can link the contents of the control to a cell on any worksheet in that workbook.

You cannot use a RefEdit control on a modeless user form (applies only to Excel 2000+). You can use the ShowModal property to set a user form to modal.

Basically, the RefEdit Control works in the same way as the Collapse Dialog buttons do in many of Excel's built-in Dialog and Edit boxes. If you are unsure what I mean by this, on an Excel Worksheet activate the Function Wizard, select any Function and click OK and you will notice that each argument box on the right hand side has a small button with an arrow on the right hand side. If you click on this button, the main dialog box disappears, giving you a clearer view of your spreadsheet and you are easily able to use your mouse pointer to select various cells. Clicking the button again expands the original dialog box, bringing things back to how they originally were.

Lets now use the RefEdit Control on a UserForm and see how we can use it to allow the user to apply formatting to a specified range of cells on a worksheet. To do this, follow the steps as shown below:

The following example illustrates how to create a UserForm that contains a RefEdit control and a CommandButton control. When the UserForm appears, you enter a reference in the RefEdit control, you do this using your mouse pointer. You then click the CommandButton and the range that you referenced is formatted with a red background, a thick black outline and any font is bolded.

          'Get the address, or reference, from the RefEdit control.
          'Note that a String is returned by the Value Property
          strAddr = RefEdit1.Value

          'Set the rRange Range variable to the range nominated by the
          'RefEdit control. If the Sheet name is also include (eg Sheet2!A1:A10)
          'It will act on that range, even if the sheet is not active at the time.
          Set rRange = Range(strAddr)

          'Apply a red pattern to the SelRange.
          'Bold any font and apply a thick border
           With rRange
                .Interior.ColorIndex = 3
                .Font.Bold = True
                .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
           End With

As commented in the code above, you should note that the Value returned by the RefEdit Control is always a String. This means that we can use it only to collect the range address of the cells as a String and not directly as a Range Object. You may also have noted that the Worksheet name is included in the selection, which means we do not need to know which sheet the user has selected to perform our operation on, as it is part of the address.

When designing a UserForm which requires the user to nominate a range of cells, it is prudent to use the RefEdit Control so we can be sure the user has a good chance of nominating the correct range. Leaving the user to type things such as dates, addresses etc., is almost always prone to error as the format in which the text is typed is critical and Murphy's law states that if they can get it wrong, they probably will.

Even when we use a RefEdit Control to collect the range from a user, we should still check to ensure that the address returned to the RefEdit Control as a string is correct. The easiest way for us to do this is to use the IsObject Function which simply returns a Boolean Value indicating whether an identifier (an element of an expression that refers to a Constant or Variable) represents an Object Variable. To do this we would modify the original code as shown below:

Sub CommandButton1_Click()
Dim rRange As Range
Dim strAddr As String
Dim bIsRange As Boolean
          'Get the address, or reference, from the RefEdit control.
          'Note that a String is returned by the Value Property
          strAddr = RefEdit1.Value

          'Use IsObject to find out if the string is a valid address.
          On Error Resume Next
          bIsRange = IsObject(Range(strAddr))
          On Error GoTo 0

          If bIsRange = False Then 'Not Valid
            MsgBox "The range is not valid"
            RefEdit1.Value = vbNullString
            RefEdit1.SetFocus
            Exit Sub
          End If

          'Set the rRange Range variable to the range nominated by the
          'RefEdit control. If the Sheet name is also include (eg Sheet2!A1:A10)
          'It will act on that range, even if the sheet is not active at the time.
          Set rRange = Range(strAddr)

Notice that also in this case we must use the On Error Resume Next statement or else our Procedure would return a run-time error if strAddr was not a valid address string.

In Excel 97 and also to some degree 2000, the RefEdit Control contains numerous problems. The main one being that if the user collapsed the button and then clicked the X in the top right hand control of the collapsed RefEdit Control, the UserForm and the RefEdit Control would both disappear and lock up the PC. This was particularly true when the RefEdit was used within a Frame Control on a UserForm. This problem appears to have been fixed within Excel XP.

While the default Event for the RefEdit Control is the BeforeDragOver it will be unlikely that you will ever use this. The text below is from the Excel help and does not even mention that it applies to the RefEdit Control. You will also note the the arguments for the BeforeDragOver Event are different to any that are shown below. According to Microsoft this Event applies to CheckBox control, ComboBox control, CommandButton control, Frame control, Image control, Label control, ListBox control, MultiPage control, OptionButton control, ScrollBar control, SpinButton control, TabStrip control, TextBox control, ToggleButton control, UserForm object. Just another of Microsoft's mysteries :o)

For Frame
Private Sub object_BeforeDragOver( ByVal Cancel As MSForms.ReturnBoolean, ctrl As Control, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)

For MultiPage
Private Sub object_BeforeDragOver( index As Long, ByVal Cancel As MSForms.ReturnBoolean, ctrl As Control, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)

For TabStrip
Private Sub object_BeforeDragOver( index As Long, ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)

For other controls
Private Sub object_BeforeDragOver( ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As fmShiftState)

Part	Description
object	Required. A valid object name.
index	Required. The index of the Page in a MultiPage that the drag-and-drop operation will affect.
Cancel	Required. Event status. False indicates that the control should handle the event (default). True indicates the application handles the event.
ctrl	Required. The control being dragged over.
Data	Required. Data that is dragged in a drag-and-drop operation. The data is packaged in a DataObject.
X, Y	Required. The horizontal and vertical coordinates of the control's position. Both coordinates are measured in points. X is measured from the left edge of the control; Y is measured from the top of the control..
DragState	Required. Transition state of the data being dragged.
Effect	Required. Operations supported by the drop source.
Shift	Required. Specifies the state of SHIFT, CTRL, and ALT.

Constant	Value	Description
fmDragStateEnter	0	Mouse pointer is within range of a target.
fmDragStateLeave	1	Mouse pointer is outside the range of a target.
fmDragStateOver	2	Mouse pointer is at a new position, but remains within range of the same target.

Constant	Value	Description
fmDropEffectNone	0	Does not copy or move the drop source to the drop target.
fmDropEffectCopy	1	Copies the drop source to the drop target.
fmDropEffectMove	2	Moves the drop source to the drop target.
fmDropEffectCopyOrMove	3	Copies or moves the drop source to the drop target.

Constant	Value	Description
fmShiftMask	1	SHIFT was pressed.
fmCtrlMask	2	CTRL was pressed.
fmAltMask	4	ALT was pressed.

Use this event to monitor the mouse pointer as it enters, leaves, or rests directly over a valid target. When a drag-and-drop operation is in progress, the system initiates this event when the user moves the mouse, or presses or releases the mouse button or buttons. The mouse pointer position determines the target object that receives this event. You can determine the state of the mouse pointer by examining the DragState argument.

When a control handles this event, you can use the Effect argument to identify the drag-and-drop action to perform. When Effect is set to fmDropEffectCopyOrMove, the drop source supports a copy (fmDropEffectCopy), move (fmDropEffectMove), or a cancel (fmDropEffectNone) operation.

When Effect is set to fmDropEffectCopy, the drop source supports a copy or a cancel (fmDropEffectNone) operation.

When Effect is set to fmDropEffectMove, the drop source supports a move or a cancel (fmDropEffectNone) operation.

When Effect is set to fmDropEffectNone. the drop source supports a cancel operation.

Most controls do not support drag-and-drop while Cancel is False, which is the default setting. This means the control rejects attempts to drag or drop anything on the control, and the control does not initiate the BeforeDropOrPaste event. The TextBox and ComboBox controls are exceptions to this; these controls support drag-and-drop operations even when Cancel is False.

ByVal References in Microsoft Forms

The ByVal keyword in Microsoft Forms indicates that an argument is passed as a value; this is the standard meaning of ByVal in Visual Basic. However, in Microsoft Forms, you can use ByVal with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString object. When you do, the value passed is not a simple data type; it is a pointer to the object.

When used with these objects, ByVal refers to the object, not the method of passing parameters. Each of the objects listed above has a Value property that you can set. You can also pass that value into and out of a function. Because you can change the values of the object's members, events produce results consistent with ByRef behavior, even though the event syntax says the parameter is ByVal.

Assigning a value to an argument associated with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString is no different from setting the value of any other argument. For example, if the event syntax indicates a Cancel argument used with the ReturnBoolean object, the statement Cancel=True is still valid, just as it is with other data types.

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Training VBA 2 Lesson 20