Writing Excel Macros with VBA, 2nd Edition

Chapter 11. Excel Events

During the course of using Excel, certain events happen. For instance, when a worksheet is created, that is an event. When a chart is resized, that is an event. Microsoft Excel defines a total of 63 different events. When an event occurs, programmers like to say that the event fires.

The purpose of an event is simply to allow the VBA programmer to write code that will execute whenever an event fires. As we will see, this code is placed in an event procedure . The code itself is referred to as event code . We wrote some simple event code for the Open and BeforeClose workbook events when we created the SRXUtils add-in in the previous chapter.

Most Excel events break naturally into five groups, as indicated in Table 11-1 through Table 11-5. These groups partially reflect the level at which the event takes place—the application level (highest), the workbook level (middle), or the worksheet/chartsheet level (lowest).

To illustrate, when a worksheet is activated by the user or through code (by calling the Activate method) several events will fire. They are, in firing order:

• The Activate event of the worksheet. This event fires whenever the worksheet is activated.

• The SheetActivate event of the workbook. This event fires whenever any worksheet in the workbook is activated.

• The SheetActivate event of the application. This event fires whenever any worksheet in any workbook in the currently running instance of Excel is activated. (However, as we will discuss ...