Sometimes, you might want to use a UserForm as a type of menu. In other words, the UserForm presents some options, and the user makes a choice. This section presents two ways to do this: using CommandButtons or using a ListBox.
Figure 14-1 shows an example of a UserForm that uses CommandButton controls as a simple menu. Setting up this sort of thing is very easy, and the code behind the UserForm is very straightforward. Each CommandButton has its own event handler procedure. For example, the following procedure is executed when CommandButton1 is clicked:
Private Sub CommandButton1_Click() Call Macro1 Unload Me End Sub
This procedure simply calls Macro1 and closes the UserForm. The other buttons have similar event handler procedures.
Figure 14-2 shows another example that uses a ListBox as a menu. Before the UserForm is displayed, its Initialize event handler procedure is called. This procedure, which follows , uses the AddItem method to add six items to the ListBox:
Private Sub UserForm_Initialize() With ListBox1 .AddItem "Macro1" .AddItem "Macro2" .AddItem "Macro3" .AddItem "Macro4" .AddItem "Macro5" .AddItem "Macro6" End With End Sub
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.
CD-ROM | The two examples in this section are available on the companion CD-ROM. The filename is userform menus .xlsm . |
CROSS-REFERENCE | Chapter 15 shows a similar example in which you can use a UserForm to simulate a toolbar. |