Microsoft Office Tutorials and References
In Depth Information
Selecting Ranges from a UserForm
The Execute button also has a procedure to handle its Click event:
Private Sub ExecuteButton_Click()
Select Case ListBox1.ListIndex
Case -1
MsgBox “Select a macro from the list.”
Exit Sub
Case 0: Call Macro1
Case 1: Call Macro2
Case 2: Call Macro3
Case 3: Call Macro4
Case 4: Call Macro5
Case 5: Call Macro6
End Select
Unload Me
End Sub
This procedure accesses the ListIndex property of the ListBox to determine which item is
selected. The procedure uses a Select Case structure to execute the appropriate macro. If the
ListIndex is –1 , nothing is selected in the ListBox, and the user sees a message.
In addition, this UserForm has a procedure to handle the double-click event for the ListBox.
Double-clicking an item in the ListBox executes the corresponding macro.
The two examples in this section are available on the companion CD-ROM. The filename
is userform menus.xlsm .
Chapter 15 shows a similar example in which you can use a UserForm to simulate a
Selecting Ranges from a UserForm
Many of Excel’s built-in dialog boxes allow the user to specify a range. For example, the Goal
Seek dialog box (displayed by choosing Data Data Tools What-If Analysis Goal Seek) asks
the user to select two single-cell ranges. The user can either type the range addresses (or 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.
Search JabSto ::

Custom Search