Creating a UserForm


Creating a UserForm "Menu"

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.

Using CommandButtons in a UserForm

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 
image from book
Figure 14-1: This dialog box uses CommandButtons as a menu.

This procedure simply calls Macro1 and closes the UserForm. The other buttons have similar event handler procedures.

Using a ListBox in a UserForm

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 
image from book
Figure 14-2: This dialog box uses a ListBox as a menu.

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 image from book  userform menus .xlsm .

CROSS-REFERENCE  

Chapter 15 shows a similar example in which you can use a UserForm to simulate a toolbar.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net