Part VI: Automating Excel
of the Properties window contains a drop-down list of all the controls on the form. You can
also click a control to select it and display its properties.
When you set properties via the Properties window, you’re setting properties at design
time. You can also use VBA to change the properties of controls while the UserForm is dis-
played (that is, at run time).
A complete discussion of all of the properties is well beyond the scope of this book—and
it would indeed be dull reading. To find out about a particular property, select it in the
Properties window and press F1 for help.
Handling events
When you insert a UserForm, that form can also hold VBA Sub procedures to handle the
events that are generated by the UserForm. An event is something that occurs when the
user manipulates a control. For example, clicking a button causes a click event. Selecting
an item in a list box control also triggers a click event, as well as a change event. To make a
UserForm useful, you must write VBA code to do something when an event occurs.
Event handler procedures have names that combine the control with the event. The gen-
eral form is the control’s name, followed by an underscore and then the event name. For
example, the procedure that is executed when the user clicks a button named MyButton
is MyButton_Click. You don’t have to remember how they’re named, however. Just right-
click the control and choose View Code. The Private Sub and End Sub keywords will
be inserted for you, and the correct name of one of the control’s events will already be
constructed. Use the drop-downs at the top of the code pane to change the event from the
default.
Displaying a UserForm
You also need to write a procedure to display the UserForm. You use the Show method of
the UserForm object. The following procedure displays the UserForm named UserForm1:
Sub ShowDialog()
UserForm1.Show
End Sub
This procedure should be stored in a regular VBA module (not the code module for the
UserForm). If your VB project doesn’t have a regular VBA module, choose Insert ➪ Module to
add one.
When the ShowDialog procedure is executed, the UserForm is displayed. What happens
next depends on the event handler procedures that you create.
