Excel 2019 Bible

997

C H A P T E R

46

Working with Excel Events

IN THIS CHAPTER

Understanding events

Using workbook-level events

Working with worksheet events

Using Special Application events

I

n the preceding chapters, we presented a few examples of VBA event-handler procedures for

ActiveX controls. These procedures are the keys to making your Excel applications interactive.

This chapter introduces the concept of events for Excel objects and includes many examples that

you can adapt to meet your own needs.

Understanding Events

Excel monitors a variety of events and can execute your VBA code when any of these events occur.

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). You must store VBA code for workbook events 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 selection on the worksheet is changed), 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).

Special Application events The final category consists of two useful application-level events:

OnTime and OnKey. These are different from other events because the code isn’t in a class module.

Rather, you set the events by calling a method of the Application object.

Many events exist at both the worksheet and workbook levels. For example, Sheet1 has an event

called Change that fires when any cell on Sheet1 is changed. The workbook has an event called

SheetChange that fires every time any cell on any sheet is changed. The workbook version of this

event has an additional argument that lets you know which sheet was affected.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.