Page 280 - Excel Progamming Weekend Crash Course

n540629 ch20.qxd 9/2/03 9:35 AM Page 255

Session 20 — Controls for User Forms 255

6. From the list at the top left of the editing window, select CommandButton1.
7. The editor may automatically enter the Click event procedure (because it is the
control’s default event). If not, select Click from the list at the top right of the
editing window.
8. Enter the single line of code Me.Hide in the Click event procedure.
9. From the list at the top left of the editing window, select UserForm.
10. From the list at the top right of the editing window, select Initialize.
11. Enter the code from Listing 20-1 in the Initialize event procedure.
12. Save the project.

Listing 20-1 Code for the user form’s Initialize event procedure

Private Sub UserForm_Initialize()

ComboBox1.AddItem “Vanilla”
ComboBox1.AddItem “Chocolate”
ComboBox1.AddItem “Coffee”
ComboBox1.AddItem “Strawberry”
ComboBox1.AddItem “Cherry”
ComboBox1.ListIndex = 0

End Sub
This completes designing the form for the demonstration. You must now create the pro-
gram to display the form and retrieve the selected value from the ComboBox:
1. Open the code-editing window for a module in your project (or, if necessary, select
Insert ➪ Module to add a new module).
2. Select Insert ➪ Procedure to add a new procedure. Name the procedure
TestComboBoxDemo.
3. Add the code from Listing 20-2 to the new procedure.

Listing 20-2 A procedure to test the user form and ComboBox control

Public Sub TestComboBoxDemo()

Dim frm As New ComboBoxDemo
frm.Show
MsgBox “You selected “ & frm.ComboBox1.Value

End Sub
When you run the TestComboBoxDemo procedure, the user form displays. Make a selection
in the ComboBox (see Figure 20-1) and click Close. The program pops up a Message Box with
the selection you made.