Excel 2010 Bible

Chapter 43: Working with Excel Events

879

When the user attempts to save the workbook, the Workbook_BeforeSave procedure executes.

If the save operation brings up the Save As dialog box, the SaveAsUI variable is TRUE. The pre-

ceding procedure checks this variable and displays a message only if the Save As dialog box is dis-

played. In this case, the message is a reminder about how to name the file.

The BeforeSave event procedure also has a Cancel variable in its argument list. If the proce-

dure sets the Cancel argument to TRUE, the file is not saved.

Using the BeforeClose event

The BeforeClose event occurs before a workbook is closed. This event often is used in conjunc-

tion with a Workbook_Open event handler. For example, use the Workbook_Open procedure to

initialize items in your workbook, and use the Workbook_BeforeClose procedure to clean up

or restore settings to normal before the workbook closes.

If you attempt to close a workbook that hasn’t been saved, Excel displays a prompt that asks

whether you want to save the workbook before it closes.

Caution
A problem can arise from this event. By the time the user sees this message, the BeforeClose event has
already occurred. This means that the Workbook_BeforeClose procedure has already executed. n

Working with Worksheet Events

The events for a Worksheet object are some of the most useful. As you’ll see, monitoring these

events can make your applications perform feats that otherwise would be impossible.

Table 43.2 lists the more commonly used worksheet events, with a brief description of each.

Remember that these event procedures must be entered into the code module for the sheet. These

code modules have default names like Sheet1, Sheet2, and so on.

TABLE 43.2

Worksheet Events

Event Action That Triggers the Event

Activate The worksheet is activated.

BeforeDoubleClick The worksheet is double-clicked.

BeforeRightClick The worksheet is right-clicked.

continued