Chapter 12: Understanding and Using Events

---

Overview

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 application 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 examples 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.

Not all events are defined by all objects. In general, if an object has an event, its parent object will also have the same event. For example, the Change event is contained at the “lowest” level by the Worksheet object. The Worksheet object’s parent is the Workbook object; it also has the same event called Workbook_SheetSelectionChange. The Workbook object’s parent, the Application, also has a Change event. The “lower” event does not need to be programmed in order to receive the event at a higher level. For example, the Workbook_SheetChange event is triggered regardless of whether you have a Worksheet_Change event coded.

When evaluating the results you want to achieve with your event procedure, keep in mind at which level it should occur. A common mistake is programming the event procedure in the wrong location. The Visual Basic Editor window displays all open projects, arranging all components in a collapsible list, as shown in Figure 12-1. Ensure you have the correct object active before programming your event procedure.

Figure 12-1: The components for each VBA Project are displayed in an expandable list.

---