Excel 2010 Bible

873

CHAPTER

Working with

Excel Events

IN THIS CHAPTER

Understanding events

Using workbook-level events

Working with worksheet

events

Using non-object events

I

n 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.

Understanding Events

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.

l 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.

l (^) 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 Sheet1).
l (^) Events not associated with objects: The final category consists of
two useful application-level events: OnTime and OnKey. These
work differently from other events.