Excel 2010 Bible

Part VI: Programming Excel with VBA

846

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 an event. Selecting an item in a list box control

also triggers an 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 general form is

the control’s name, followed by an underscore, and then the event name. For example, the proce-

dure that is executed when the user clicks a button named MyButton is MyButton_Click.

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 upon the event-handler procedures that you create.

A UserForm Example

The preceding section is, admittedly, rudimentary. This section demonstrates, in detail, how

to develop a UserForm. This example is rather simple. The UserForm displays a message to the

user — something that can be accomplished more easily by using the MsgBox function. However,

a UserForm gives you more flexibility in terms of formatting and layout of the message.

On the CD
This workbook is available on the companion CD-ROM. The file is named show message.xlsm.