Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
Many of Excel's built-in dialog boxes allow the user to specify a range. For example, the Goal Seek dialog box asks the user to select two single- cell ranges. The user can either type the range names directly or use the mouse to point and click in a sheet to make a range selection.
Your UserForms can also provide this type of functionality, thanks to the RefEdit control. The RefEdit control doesn't look exactly like the range selection control used in Excel's built-in dialog boxes, but it works in a similar manner. If the user clicks the small button on the right side of the control, the dialog box disappears temporarily, and a small range selector is displayed - which is exactly what happens with Excel's built-in dialog boxes.
| Note | Unfortunately, the RefEdit control has a few quirks that still haven't been fixed. You'll find that this control does not 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. The keyboard can't be used 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.
| CD-ROM | This example is available on the companion CD-ROM in a file named |
Following are a few things to keep in mind when using a RefEdit control:
-
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)
-
It's a good practice to initialize the RefEdit control to display the current range selection. You can do so in the UserForm_Initialize procedure by using a statement such as
RefEdit1.Text = ActiveWindow.RangeSelection.Address
-
For best results, do not put a RefEdit control inside of a Frame or a MultiPage control. Doing so may cause Excel to crash.
-
Don't assume that RefEdit will always return a valid range address. Pointing to a range isn't the only way to get text into this control. The user can type any text and can also edit or delete the displayed text. Therefore, you need to make sure that the range is valid. The following code is an example of a way to check for a valid range. If an invalid range is detected , the user is given a message, and focus is set to the RefEdit control so the user can try again.
On Error Resume Next Set UserRange = Range(RefEdit1.Text) If Err.Number <> 0 Then MsgBox "Invalid range selected" RefEdit1.SetFocus Exit Sub End If On Error GoTo 0
-
The user can also click the worksheet tabs while selecting a range with the RefEdit control. Therefore, you can't assume that the selection is on the active sheet. However, if a different sheet is selected, the range address is preceded by a sheet name . For example:
Sheet2!$A:$C
-
If you need to get a single cell selection from the user, you can pick out the upper-left cell of a selected range by using a statement such as
Set OneCell = Range(RefEdit1.Text).Range("A1")
| CROSS-REFERENCE | As I discuss in Chapter 12, you can also use Excel's InputBox method to allow the user to select a range. |