Workbook and worksheet events in VBA

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.