Page 291 - Microsoft Office Excel 2003 Programming Inside Out

Chapter 12

Understanding and

Using Events

Enabling and Disabling Events . . . . . . . 266 Worksheet Events . . . . . . . . . . . . . . . . . 273
Workbook Events . . . . . . . . . . . . . . . . . 267 Application Events . . . . . . . . . . . . . . . . . 276

Within Microsoft Excel, any actions that occur, such as opening a workbook, saving the file
or recalculating a worksheet, are referred to as events. Some events are triggered by the appli­
cation and some by the user, but no matter how the event is initiated, by assigning procedures
to these events you can enhance how the user interacts with your Excel application.
When a trigger occurs, Excel will look for an event procedure named Object_EventName, in
which Object is the object that generates and contains the event, and EventName is the name
of the specific trigger. For example, when the user changes the selection, either by clicking a
cell in the workbook or by using the navigation keys, Excel generates a SelectionChange event
and will execute any code in a procedure named Object_SelectionChange. Similarly, changing
a cell’s value, either by typing a new value into the cell or by changing the Value property
from a Visual Basic for Applications (VBA) procedure, will cause Excel to trigger a Change
event and execute the Object_Change event procedure.
In this chapter, you will learn how to enable and disable events and use Workbook, Worksheet,
and Application events effectively.

For information on Chart events, refer to Chapter 15, “Charts,” and for information on UserForm events,
refer to Chapter 19, “Creating User Forms."
Excel can monitor many different events that occur. These events are grouped into the
following categories:
● Workbook events Events that occur for a particular workbook. Some examples are
the Open, Activate, and NewSheet events.
● Worksheet events Events that occur for a particular worksheet. Some common exam­
ples used at the worksheet level are the Calculate, Change, and BeforeRightClick events.
● Chart Events Events that occur for a particular chart. Some examples are the Select,
Activate, and SeriesChange events.
● Application Events Events that occur for the application, Excel. Several examples
would be the SheetChange, NewWorkbook, and WorkbookBeforeClose events.
● UserForm Events Events that occur for a particular User Form or an object contained
on the User Form. Some commonly used events are Click and Initialize.

265
Part 4: Advanced VBA