Microsoft Office Tutorials and References
In Depth Information
Chapter 11. Excel Events
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.
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 later, to enable this event, we must write special code.)
11.1 The EnableEvents Property
It is important to note that no Excel event will fire unless the EnableEvents property is set to True
(although it is set to True by default). Thus, the programmer has control over whether Excel
events are enabled. The EnableEvents property is a property of the Application object, so, for
instance, to prevent the Save event from firing when the active workbook is saved, we can write:
Application.EnableEvents = False
Application.EnableEvents = True
11.2 Events and the Excel Object Model
The Excel object model contains several objects that exist simply as a convenience, in order to
include the Excel events in the object model. (We do not actually program with these objects.)
These objects are AppEvents, DocEvents, ChartEvents, WorkBookEvents, OLEObjectEvents, and
RefreshEvents. The events associated with a worksheet, for instance, are methods of the
DocEvents object, which is a child of the Worksheet object and the Chart object.
11.3 Accessing an Event Procedure