Microsoft Office Tutorials and References
In Depth Information
Selecting Ranges from a UserForm
Unfortunately, the RefEdit control has a few quirks that still haven’t been fixed. You’ll
find that this control doesn’t allow the user to use shortcut range selection keys (for
example, pressing End, followed by Shift+ ↓ will not select cells to the end of the
column). In addition, after clicking the small button on the right side of the control (to
temporarily hide the dialog box), you’re limited to mouse selections only. You can’t use
the keyboard at all to make a selection.
Figure 14-3 shows a UserForm that contains a RefEdit control. This dialog box enables the user
to perform a simple mathematical operation on all nonformula (and non-empty) cells in the
selected range. The operation that’s performed corresponds to the selected OptionButton.
This example is available on the companion CD-ROM in a file named
range selection demo.xlsm .
Figure 14-3: The RefEdit control shown here allows the user to select a range.
Following are a few things to keep in mind when using a RefEdit control:
h The RefEdit control returns a text string that represents a range address. You can
convert this string to a Range object by using a statement such as
Set UserRange = Range(RefEdit1.Text)
h Initializing the RefEdit control to display the current range selection is good practice.
You can do so in the UserForm_Initialize procedure by using a statement such as
RefEdit1.Text = ActiveWindow.RangeSelection.Address