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.