Wauvel

Excel like a finance pro.

← All VBA snippets

Run something the moment a cell changes

An audit trail beside every input — without the event re-firing itself forever.

1What it does

`Worksheet_Change` runs every time a cell on that sheet is edited, which makes it the natural home for an audit trail: someone changes an assumption, and the macro stamps who and when in the next column. It also has the most famous trap in VBA. Writing that stamp is itself a change, which fires the event again, which writes again — until Excel gives up with an out-of-stack error or simply hangs. `Application.EnableEvents = False` around your own write stops the loop, and putting it back under a cleanup label stops a failure from leaving every event in the workbook switched off.

2The code

' Goes in the WORKSHEET's code module (double-click the sheet in the
' VBA editor), not in a normal module — or it never fires.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim inputs As Range, hit As Range, c As Range
    Set inputs = Me.Range("B4:B20")

    ' Only react to edits inside the input block.
    Set hit = Intersect(Target, inputs)
    If hit Is Nothing Then Exit Sub

    On Error GoTo Cleanup
    ' Our own writes below are changes too. Without this, they re-fire this
    ' event, which writes again, forever.
    Application.EnableEvents = False

    For Each c In hit.Cells              ' a paste can change many cells at once
        c.Offset(0, 1).Value = Environ$("USERNAME")
        c.Offset(0, 2).Value = Now
        c.Offset(0, 2).NumberFormat = "yyyy-mm-dd hh:mm"
    Next c

Cleanup:
    ' ALWAYS back on — or no event in the workbook fires again this session.
    Application.EnableEvents = True
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Stamp who changed an assumption, and when, beside the input.
  • Validate an entry the moment it's typed and put back a bad one.
  • Recalculate a dependent block only when its inputs change.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

AssumptionValueChanged byWhen
Growth rate6%

5Pitfalls

The event writes to the sheet, which fires the event, which writes — until Excel hangs or throws out-of-stack.

Fix: Application.EnableEvents = False before your own writes, as above.

The macro failed between switching events off and on, so no event fires again until Excel restarts.

Fix: Restore EnableEvents under a Cleanup label that the error path reaches.

It never fires at all.

Fix: It lives in the worksheet's own code module, not a normal Module. Also check events weren't left disabled by an earlier crash.

A paste of 200 cells is treated as one, and only the first gets stamped.

Fix: Target can be many cells. Loop the cells of the intersected range.

Every macro edit wipes Excel's undo stack.

Fix: That's unavoidable — any VBA write clears undo. Worth telling the people using the sheet.

6No-code alternatives

  • Track Changes / version history — OneDrive and SharePoint keep who-changed-what without code, at the file level rather than beside each cell.
  • Data Validation — For rejecting bad input, validation stops it before it's entered and needs no macro. See the tip.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.