For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Workbook and worksheet events in VBA

VBA.D6 Worksheet_Change(ByVal Target)
Workbook and worksheet events in VBA — Excel example preview

Running event macros automatically when a cell changes or a workbook opens — with Application.EnableEvents = False to prevent infinite loops.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

Events let VBA code run itself, without anyone clicking a button — when a workbook opens, when a cell changes, when the user selects something new. The catch is that event code has to live in a specific place, and it can very easily trigger itself in a loop if you’re not careful.

Where this code goes

Event procedures do not go in a regular Module. They belong in the specific object the event fires on:

  • Workbook-level events (like Workbook_Open) go in the ThisWorkbook module.
  • Worksheet-level events (like Worksheet_Change) go in that specific sheet’s module — e.g. Sheet1 (Sheet1) in the Project Explorer — not in ThisWorkbook and not in a Module.

Code with the exact right name and signature placed in a plain Module simply won’t run as an event — VBA never wires it up.

The easy way to get the right signature

Double-click ThisWorkbook (or the sheet) in the Project Explorer to open its code window. At the top of that window are two dropdowns: the left one lists objects (Workbook, or for a sheet, Worksheet), and the right one lists that object’s events. Pick the object, then pick the event, and VBA inserts an empty, correctly-named procedure for you — no need to type the signature from memory.

Workbook_Open

Runs once, when the workbook finishes opening:

Private Sub Workbook_Open()
    MsgBox "Welcome back!"
End Sub

Worksheet_SelectionChange

Runs whenever the user selects a different cell or range on that sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.StatusBar = "Selected cell: " & Target.Address
End Sub

Worked example: auto-timestamp column B when column A changes

Worksheet_Change runs whenever a cell on that sheet is edited. Target is the range that changed — it can be a single cell or many at once (for example, if the user pastes a block or deletes several rows), so check it with Intersect rather than assuming it’s one cell:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo CleanFail

    Dim rngA As Range, cell As Range
    Set rngA = Intersect(Target, Me.Columns("A"))
    If rngA Is Nothing Then Exit Sub

    Application.EnableEvents = False
    For Each cell In rngA
        Me.Cells(cell.Row, "B").Value = Now
    Next cell

CleanFail:
    Application.EnableEvents = True
End Sub

Me refers to the worksheet the code is running in. Intersect returns Nothing if the edited cells don’t overlap column A at all, so the Exit Sub skips everything else for edits elsewhere on the sheet.

The classic gotcha: infinite self-triggering

Here’s the trap: Worksheet_Change fires whenever a cell changes — and writing Me.Cells(cell.Row, "B").Value = Now is itself a change to the sheet. Without protection, that write fires Worksheet_Change again, which writes to column B again, which fires the event again, and so on until Excel hangs or throws a stack error.

Application.EnableEvents = False is what breaks the cycle: it tells Excel not to fire any events while it’s off, so your own code’s edits don’t re-trigger the handler. The part that’s easy to get wrong is turning it back on reliably — if an error happens between False and True, events silently stay off for the rest of the session, and no other event macro in the workbook will fire until someone notices and fixes it. That’s why the On Error GoTo CleanFail label above jumps straight to the line that restores EnableEvents = True no matter whether the code succeeded or failed. If you ever suspect events are stuck off, you can type Application.EnableEvents = True directly into the Immediate window (Ctrl+G) and press Enter to force them back on.

The events worth knowing

Beyond the three above, these come up constantly:

Event Fires when Typical use
Workbook_BeforeClose Before the file closes Last-chance validation, cleanup
Workbook_BeforeSave Before a save completes Stamp a “last saved by” cell
Workbook_SheetChange A cell changes on any sheet One handler for the whole workbook
Worksheet_BeforeDoubleClick Double-click on a cell A tick-box column, drill-down
Worksheet_BeforeRightClick Right-click on a cell A custom menu
Worksheet_Activate The tab is selected Refresh a view, reset filters
Workbook_NewSheet A sheet is added Apply house formatting

Workbook_SheetChange deserves attention: it fires for every sheet and passes Sh as well as Target, so a rule that should apply workbook-wide lives in one place rather than being copied into eight sheet modules. The trade is that it also fires on sheets you did not have in mind, so test Sh.Name early and exit.

Cancel: events that can say no

Several events pass a Cancel argument. Setting it to True stops the action from happening — this is how you prevent a save, a close, or a right-click menu.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Me.Worksheets("Input").Range("B4").Value = "" Then
        MsgBox "Enter the reporting period in B4 before saving.", vbExclamation
        Cancel = True
    End If
End Sub

Use this sparingly. A workbook that refuses to save is infuriating if the condition is wrong, and the user’s escape hatch — holding Shift while opening to suppress events — is not something most people know.

Worksheet_BeforeDoubleClick with Cancel = True is the friendlier case: it stops the double-click from entering edit mode, so you can use it as a click action instead.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Me.Range("D2:D200")) Is Nothing Then Exit Sub
    Cancel = True
    Target.Value = IIf(Target.Value = "x", "", "x")
End Sub

Keep handlers short and fast

An event handler runs on the user’s keystroke, so its cost is felt directly. A Worksheet_SelectionChange that takes 300ms makes the whole sheet feel broken, because it runs every time anyone moves the cursor.

Three rules:

  1. Exit early. The Intersect ... Is Nothing Then Exit Sub pattern should be the first thing in the procedure, so edits outside your range of interest cost almost nothing.
  2. Do the work elsewhere. Put real logic in a normal module and have the handler call it. The handler stays readable and the logic becomes testable on its own.
  3. Guard against bulk changes. Target can be thousands of cells when somebody pastes a column or deletes rows. Check the size before looping:
If Target.Cells.Count > 1000 Then Exit Sub

Without that, one paste can put you in a loop long enough to look like a hang.

Debugging events, which is genuinely awkward

Events fire while you are working, which makes them harder to debug than normal macros.

  • Debug.Print beats MsgBox. A message box inside a SelectionChange handler requires you to dismiss it before you can do anything — including stop the macro. Print to the Immediate window (Ctrl + G) instead.
  • If Excel feels stuck, press Ctrl + Break, then Ctrl + G and run Application.EnableEvents = True. This is the first thing to try when event code has mysteriously stopped working.
  • Hold Shift while opening a workbook to suppress Workbook_Open. The standard way back into a file whose open handler is broken.
  • Events do not fire when the macro that caused the change set EnableEvents = False — including another workbook’s macro. “My handler didn’t run” is often someone else’s code leaving events off.

A single F8 step-through is still the fastest diagnosis; put a breakpoint (F9) on the first line of the handler and then make the edit that should trigger it. VBA editor basics covers the tools.

When not to use an event

Events add invisible behaviour to a file, and invisible behaviour surprises people. Before writing one, check whether something plainer does the job:

And remember the file has to be .xlsm, which many organisations block by default. Event code that nobody can run because macros are disabled is worse than no event code, because the workbook silently behaves differently depending on who opened it.

Finally: a macro cannot be undone, and an event handler that writes to cells destroys the user’s undo stack every time it fires. Somebody typing in column A who then presses Ctrl + Z will not get their previous value back. That is a real cost worth weighing against the convenience of the automatic timestamp.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Turn the idea into a repeatable Excel workflow you can explain, rebuild, and review later. For this article, the goal is to practice: How to run code automatically on Workbook_Open, Worksheet_Change, and Worksheet_SelectionChange, where that code has to live, and the infinite-loop gotcha that trips up nearly every beginner.

Practice workbook setup

Create a small practice workbook with one raw-data sheet, one working sheet, and one final output sheet.

Practice workflow

  1. Rebuild the example once exactly as described, then repeat it with different labels, dates, or amounts.
  2. Write a short note beside the result explaining what each step is doing and why it matters.
  3. Change one input value and confirm the output updates in the way you expected.
  4. Save a clean copy of the workbook before experimenting further.

Quality checks

  • Inputs, calculations, and final outputs are separated clearly.
  • Headings describe the data without relying on memory or hidden context.
  • The final result can be understood by someone who did not build the workbook.

Common mistakes

  • Mixing raw data and manual adjustments in the same cells.
  • Skipping a quick review after the result looks correct.
  • Building a one-off fix instead of a repeatable workflow.

Next actions

  • Apply the same pattern to a real workbook with 20 to 50 rows of sample data.
  • Add one note that explains when you would not use this approach.

Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.