Selecting an Excel VBA Range
In some cases, you may want the user to select an Excel VBA range while a dialog box is displayed. An example of this type of Excel VBA range selection occurs in the Create Table dialog box, which is displayed when you choose Home → Insert → Tables → Table. The Create Table dialog box has a range selector control that contains Excel’s guess regarding the range to be converted — but you can use this control to change the range by selecting cells in the worksheet.
To allow an Excel VBA range selection in your dialog box, add a RefEdit control. The following example displays a dialog box with the current region’s range address displayed in a RefEdit control. The current region is the block of nonempty cells that contains the active cell. The user can accept or change this range. When the user clicks OK, the procedure makes the range bold.
This Excel VBA example assumes the following:
- You have a UserForm named UserForm1.
- The UserForm contains a CommandButton control named OKButton.
- The UserForm contains a CommandButton control named CancelButton.
- The UserForm contains a RefEdit control named RefEdit1.
The code is stored in a VBA module and shown here. This code does two things: initializes the dialog box by assigning the current region’s address to the RefEdit control and displays the UserForm.
Sub BoldCells() ' Exit if worksheet is not active If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub ' Select the current region ActiveCell.CurrentRegion.Select ' Initialize RefEdit control UserForm1.RefEdit1.Text = Selection.Address ' Show dialog UserForm1.Show End Sub
The following procedure is executed when the OK button is clicked. This procedure does some simple error checking to make sure that the range specified in the RefEdit control is valid.
Private Sub OKButton_Click() On Error GoTo BadRange Range(RefEdit1.Text).Font.Bold = True Unload UserForm1 Exit Sub BadRange: MsgBox "The specified range is not valid." End Sub
If an error occurs in Excel VBA (most likely an invalid range specification in the RefEdit control), the code jumps to the BadRange label, and a message box is displayed. The dialog box remains open so the user can select another range.
If getting a user selected range is the only function performed by your UserForm, you can simplify things by using the Application InputBox method.