Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.
Creating Template Controls,
Using the RefEdit Control
Creating a Template
One thing you will find very often when creating UserForms via Excel VBA is that you will probably use the same types of Controls numerous time over. For instance, nearly every UserForm will have a Cancel button and quite possibly an OK button. Excel allows us to easily create Template Controls which we can format and name appropriately so that each time the Control is needed, rather than repeat the process over and over again, we can simply select the Template Control that we have created and place it on to a UserForm. This is extremely easy to do and can save many hours of repetitious work in the long run.
For example, as we have mentioned above a Cancel button and an OK button are very common buttons to have on a UserForm. Lets see now how we can easily create these two buttons and then make them into Templates.
First of all, insert a new UserForm and on this UserForm place two CommandButtons. Change the name of CommandButton1 to CancelButton (one word), size it accordingly and change any other appearance Properties that you would normally associated with it, eg; bold font, background colour etc. Do exactly the same for the OK button, but of course change the name Property of this button to OKButton (one word). Now, with the Control Toolbox visible, right click on the tab named Controls and select New Page. Now right click on this New Page that has been created and select Rename, and give it a meaningful name such as Templates. All you simply need to do now is to left click to select the Control on your UserForm and drag and drop the button or whichever Control you have used as a Template on to the new page called Templates. Each time you now use Excel (providing of course that you have saved) you will have a new page on your Control ToolBox called Templates and on that Control Toolbox page will be your most commonly used Controls all pre-named and pre-formatted. You can also, if you wish, right click on these Controls that you have placed onto your Template sheet, select Customise New<ControlName> and :
Type in some ToolTip text (this is the text you would see when hovering your mouse pointer over the Control)
Load a new picture to represent the Control on the Templates sheet. Important to note that the picture you use will only be seen while it is residing on the Control ToolBox. Once the Control is dragged and dropped onto the UserForm, it will appear as it did before.
Possibly the only thing that we cannot do and it would be nice to, is also to have code pre-built into the Control as well. Unfortunately, saving the Control as that type of Template does not allow this flexibility. If we do want to give ourselves this extra flexibility, possibly the simplest way to do this is to insert a UserForm and on this UserForm insert or place all the commonly used Controls, name them and change any other Properties appropriately, then go through and code the Events for the Controls as you see fit and then give the UserForm perhaps a name such as ControlsTemplate. Right click on the ControlsTemplate form while within the Project Explorer and select Export File. Save the file to a location (it is probably best to create a folder in its default location) and click Save. Each time you need a specific Control(s) you simply right click within the Project Explorer and select Import File and import the UserForm housing all your Controls with all Properties and coding pre-done.
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.
©2002 ozgrid - microsoftexceltraining - David & Raina Hawley. All right reserved