Excel VBA Workbook EventsThe Complete Automation Series

The moment a workbook opens, Excel fires Workbook_Open. A cell changes, it fires Worksheet_Change. The workbook is about to close, it fires Workbook_BeforeClose. You write the code once in the correct module and it executes automatically, for every user, every time.

In finance, this is not a productivity trick. It is infrastructure. Shared workbooks (month-end close files, budget trackers, reconciliation templates, journal entry logs) fail at the human steps, not the accounting. Someone forgets to re-apply sheet protection. Unsaved changes vanish when a colleague clicks "Don't Save." A macro breaks because the bank statement CSV was moved to a different folder. Event-driven VBA removes those failure points entirely.

This series covers twenty topics across three tiers. Every snippet is tested, finance-specific, and written for accountants who need to understand the code, not just copy it.

A Quick Example: Workbook_Open

Paste this into the ThisWorkbook module (Alt + F11, then double-click ThisWorkbook under Microsoft Excel Objects). It unprotects a named sheet every time the file opens:

Option Explicit

Private Sub Workbook_Open()
    ' Unprotect the data entry sheet on open
    ' Combine with Workbook_BeforeClose to re-protect on close
    Dim wsData As Worksheet
    Set wsData = ThisWorkbook.Sheets("Data Entry")
    wsData.Unprotect Password:="YourPasswordHere"
End Sub

Combine it with the Workbook_BeforeClose guide to re-apply protection on close. That is the standard protect-and-unprotect pattern for shared finance workbooks.

Where Does the Code Go?

The single most common mistake in event-procedure setups: pasting code into the wrong module. Workbook_Open and Workbook_BeforeClose belong in ThisWorkbook. Worksheet_Change belongs in the module for the specific sheet you want to monitor. Standard modules (Module1, Module2) are for subs you call manually. An event procedure pasted into Module1 will never fire, with no error and no warning.

Basic Tier: Events and Methods

Start here if you are new to event-driven VBA. These eleven guides cover the core events and methods used daily in finance workbooks: open, close, cell-change, new workbooks, and file selection.

Intermediate Tier: Patterns and Performance

Once you are comfortable with the basic event model, move to these. They cover the techniques that matter when workbooks grow: looping through folders, proper error handling with On Error GoTo, and the performance gap between Range and array operations on large datasets.

Advanced Tier: Automation Beyond the Worksheet

These four go past the worksheet entirely: deduplicating with a Dictionary object, exporting individual PDFs, sending email straight from Outlook, and building a proper UserForm for non-technical staff.

Frequently Asked Questions