 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
RefEdit Control
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.
Here is an overview of 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.
The default property for a RefEdit control is Value.
The default event for a RefEdit control is BeforeDragOver.
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.
End of Overview
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.
Insert a new UserForm (UserForm1)
In the Control Toolbox, click RefEdit Control and place the control on your UserForm1.
If you are unsure which button is the RefEdit button, wave your mouse over the button and you will see the a ToolTip with the button name.
In the Control Toolbox, click the CommandButton Control and place the CommandButton on your UserForm.
With the CommandButton selected, display the Properties window (F4). Change the Caption property of the control to OK.
Double-click the CommandButton to get to the Private Module of the UserForm.
Place in the following code in the Click Event of the CommandButton.
Sub CommandButton1_Click()
Dim rRange As Range
Dim strAddr As String
Run your UserForm (F5)
Now try your RefEdit Control in the same way as you would use the Collapse dialog within Excel normally.
Move your UserForm out the way so you can see the range that you selected, and click the OK button.
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:
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)
BeforeDragOver Event From Excel Help
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)
The BeforeDragOver event syntax has these parts:
| 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. |
Settings
The settings for DragState are:
| 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. |
The settings for Effect are:
| 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. |
The settings for Shift are:
| Constant | Value | Description |
|---|---|---|
| fmShiftMask | 1 | SHIFT was pressed. |
| fmCtrlMask | 2 | CTRL was pressed. |
| fmAltMask | 4 | ALT was pressed. |
Remarks
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.
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.