Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Understanding Excel’s Events
h Chart events: Events that occur for a particular chart. These events include Select (an
object in the chart is selected) and SeriesChange (a value of a data point in a series is
changed). To monitor events for an embedded chart, you use a class module, as I
demonstrate in Chapter 18.
h Application events: Events that occur for the application (Excel). Examples include
NewWorkbook (a new workbook is created), WorkbookBeforeClose (any workbook
is about to be closed), and SheetChange (a cell in any open workbook is altered). To
monitor Application -level events, you need to use a class module.
h UserForm events: Events that occur for a particular UserForm or an object contained on
the UserForm. For example, a UserForm has an Initialize event (occurs before the
UserForm is displayed), and a CommandButton on a UserForm has a Click event
(occurs when the button is clicked).
h Events not associated with objects: The final category consists of two useful
Application -level events that I call On events: OnTime and OnKey . These work in a
different manner than other events.
This chapter is organized according to the preceding list. Within each section, I provide examples
to demonstrate some of the events.
Understanding event sequences
Some actions trigger multiple events. For example, when you insert a new worksheet into a
workbook, this action triggers three Application -level events:
h WorkbookNewSheet : Occurs when a new worksheet is added.
h SheetDeactivate : Occurs when the active worksheet is deactivated
h SheetActivate : Occurs when the newly added worksheet is activated.
Event sequencing is a bit more complicated than you might think. The preceding events
are Application -level events. When adding a new worksheet, additional events occur
at the Workbook level and at the Worksheet level.
At this point, just keep in mind that events fire in a particular sequence, and knowing what the
sequence is can sometimes be critical when writing event-handler procedures. Later in this
chapter, I describe how to determine the order of the events that occur for a particular action (see
“Monitoring Application-level events”).
Where to put event-handler procedures
VBA newcomers often wonder why their event-handler procedures aren’t being executed when
the corresponding event occurs. The answer is almost always because these procedures are
located in the wrong place.