Chapter 42: Working with Excel Events
In This Chapter
Using workbook-level events
Working with worksheet events
Using non-object events
In the preceding chapters, I presented a few examples of VBA event-handler procedures. These procedures are the keys to making your Excel applications interactive. This chapter provides an introduction to the concept of Excel events and includes many examples that you can adapt to meet your own needs.
Excel can monitor a wide variety of events and execute your VBA code when a particular event occurs. This chapter covers the following types of events:
• Workbook events: These occur for a particular workbook. Examples include
Open (the workbook is opened or created),
BeforeSave (the workbook is about to be saved), and
NewSheet (a new sheet is added). VBA code for workbook events must be stored in the
ThisWorkbook code module.
• Worksheet events: These occur for a particular worksheet. Examples include
Change (a cell on the sheet is changed),
SelectionChange (the cell pointer is moved), and
Calculate (the worksheet is recalculated). VBA code for worksheet events must be stored in the code module for the worksheet (for example, the module named
• Events not associated with objects: The final category consists of two useful application-level events:
OnKey. These work differently from other events.
Entering Event-Handler VBA Code
Every event-handler procedure must reside in a specific type ...