Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 14Automating Procedures with Workbook Events

In Lesson 13, you learned about worksheet-level events and how they are triggered by actions relating to individual worksheets. Workbooks themselves can also recognize and respond to a number of events that take place at the workbook level. This lesson describes how you can further customize your workbooks with VBA procedures for the most commonly used workbook events.

Workbook Events: An Overview

Workbook events occur within a particular workbook. Many workbook events occur because something happened to an object in the workbook, such as a worksheet—any worksheet—that was activated, or a cell—any cell—that was changed. Other workbook events occur because the workbook was imposed upon to do something, such as to open or close, or to be saved or printed.

NOTE Unless the VBA code itself purposely refers to other workbooks, event procedures at the workbook level affect only the workbook within which the code resides.

Where Does the Workbook Event Code Go?

You saw in Lesson 13 that each individual worksheet has its own module. Workbooks are similar to worksheets in this respect because a workbook is also an Excel object, and it has its own module already present and accounted for when the workbook is created.

NOTE Workbook-level event code always goes into the workbook module. You never need to create a workbook module or a worksheet module; Excel creates those modules automatically with every new workbook. If a workbook-level ...