VBA Workbook_BeforeSave in Excel — Validate or Block a Save, and What SaveAsUI Really Tells You

TL;DR — Workbook_BeforeSave is an event Excel fires the moment a save is requested — Ctrl+S, the Save button, or a macro's .Save — before it writes to disk. It hands you two arguments: SaveAsUI (is the Save As dialog about to appear?) and Cancel (set it to True to block the save). So it's a quality gate on the save action: inspect the workbook, and either let the write through or refuse it. The one thing that bites everyone — if your handler calls ThisWorkbook.Save, that save fires BeforeSave again and loops forever unless you gate it with Application.EnableEvents.

' Lives in ThisWorkbook — NOT a Module.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If ThisWorkbook.Sheets("Invoice").Range("Total").Value = "" Then
        MsgBox "Enter a Total before saving.", vbExclamation
        Cancel = True                         ' <-- block the save; nothing is written
        Exit Sub
    End If
    ThisWorkbook.Sheets("Invoice").Range("LastSaved").Value = Now   ' stamp — no .Save call here
End Sub

Saving feels like the safest thing a user can do, which is exactly why it's the right place to enforce rules. Workbook_BeforeSave is the event behind "you can't save with a blank required field," auto-stamped "last modified" cells, forced filenames, and save-time audit logs. It's also where two subtle traps live: a save that silently does nothing, and a handler that saves itself into an infinite loop.

What you'll learn

The mental model — a gate on the save, with a Cancel veto and a SaveAsUI signal
What SaveAsUI actually means, and how to use it to force a filename or folder
The auto-stamp pattern — write "who and when" into the file being saved
The recursion trap — why .Save inside BeforeSave loops, and the EnableEvents fix
Why a silent Cancel = True is a bug, not a feature

The mental model: a gate on the save, not a receipt

Workbook_BeforeSave fires when a save is requested and runs before Excel writes the file. You don't call it — Excel calls you, waits, then decides what to do based on what you did. It passes two arguments by reference:

SaveAsUI As Boolean — True when Excel is about to show the Save As dialog (the user picked "Save As", or it's a brand-new unsaved file), False for a plain save over the existing file. It's information: which kind of save is this?
Cancel As Boolean — your veto. Set Cancel = True and Excel abandons the save; nothing is written.

So the handler is a gate, not a receipt. A receipt would confirm a save already happened; this runs first and gets to say "no." (If you want the receipt — to know a save succeeded — that's the separate Workbook_AfterSave event, which fires afterward with a Success flag.)

The signature is fixed and, like every workbook event, it lives in ThisWorkbook: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean). In a standard Module it never fires.

The rule that makes it useful: `Cancel` blocks, `SaveAsUI` decides how

The two arguments do two different jobs, and mixing them up is where people stall.

Use Cancel to enforce a precondition — refuse to save until the data is valid:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Sheets("Order").Range("CustomerID").Value = "" Then
        MsgBox "Can't save: Customer ID is required.", vbExclamation
        Cancel = True            ' the save stops here
    End If
End Sub

Use SaveAsUI to control how the file is saved — most often to stop users scattering copies with random names, or to force a naming convention:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
        MsgBox "Use File > Save only. This file must keep its name.", vbCritical
        Cancel = True            ' block "Save As", allow plain "Save"
    End If
End Sub

That handler lets a normal Ctrl+S through but refuses "Save As" — a small policy that's impossible without knowing which save is in progress. The rule: SaveAsUI tells you the intent, Cancel decides the outcome.

The pattern worth memorising: stamp the file before it's written

The single most useful thing BeforeSave does is write metadata into the file at the last possible moment, so the saved copy carries it. Because your code runs before the disk write, anything you change is captured by the save that's about to happen:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    With Sheets("Log")
        .Range("LastSavedBy").Value = Environ("Username")
        .Range("LastSavedAt").Value = Now
    End With
    ' No .Save here — the save that triggered this event will include the stamps.
End Sub

Notice what's missing: there is no ThisWorkbook.Save call. You don't need one — the save is already happening; you're just editing the workbook a moment before it lands on disk. Adding a .Save here is not only redundant, it's the setup for the next trap.

The trap that freezes Excel: calling Save inside BeforeSave

If your handler calls ThisWorkbook.Save (a natural instinct — "stamp it, then save it"), that save fires Workbook_BeforeSave again, which saves again, which fires it again — the same infinite-loop shape as Worksheet_Change writing to a cell. The event has no built-in guard against reacting to its own action.

When you genuinely must trigger a save from inside a save handler — or from any event that then writes — switch events off around it and always restore them in an error handler so a crash can't leave them stuck off:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    On Error GoTo CleanExit
    Application.EnableEvents = False        ' our own actions won't re-fire this event
    ' ... work that might itself save or write ...
CleanExit:
    Application.EnableEvents = True         ' runs whether we succeeded or errored
End Sub

Application.EnableEvents is a single, application-wide switch that Excel does not reset for you. If a handler sets it to False and errors before restoring it, every event in Excel goes quiet until you restart or run Application.EnableEvents = True in the Immediate window — the same failure mode described in VBA On Error.

The rule people forget: don't cancel silently

A Cancel = True with no explanation is a genuine bug. The user presses Ctrl+S, sees nothing, and walks away believing the file is saved — but BeforeSave quietly blocked it and Workbook.Saved is still False. Hours of work can evaporate on the next crash. Whenever you set Cancel = True, tell the user why with a MsgBox, so a blocked save is a visible decision, not a silent one. A gate the user can't see is a trap.

BeforeSave guards the save; its sibling Workbook_BeforeClose guards the close with the same kind of Cancel veto, and both belong to the workbook event family alongside Workbook_Open.

How ExcelMaster helps

A save gate has a lot of small correct-or-broken details: the handler in ThisWorkbook, Cancel used for validation but not silently, SaveAsUI read the right way round, the stamp written without a .Save call, and an EnableEvents guard if anything does save.

ExcelMaster lets you state the rule instead. Say "don't let this file save if the Total cell is empty, and stamp who saved it and when," and it writes a Workbook_BeforeSave that blocks the save with a clear message, stamps the log sheet before the write, and guards against the self-trigger loop. You keep the workbook and the code; you skip the freeze you'd otherwise cause teaching yourself the rule.

Frequently asked questions

How do I stop a workbook from saving in VBA?

Set the Cancel argument to True inside Workbook_BeforeSave: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) … Cancel = True. Excel checks Cancel after your handler runs; True abandons the save and writes nothing. Always show a message explaining why, so the block isn't silent.

What does the SaveAsUI parameter mean?

SaveAsUI is True when Excel is about to show the Save As dialog — because the user chose "Save As" or the file has never been saved — and False for a plain save over the existing file. Read it to treat the two cases differently, for example to allow Ctrl+S but block "Save As" so the file keeps its name.

Why does my BeforeSave macro cause an infinite loop?

Because it calls ThisWorkbook.Save (or another save), and that save fires Workbook_BeforeSave again, endlessly. Usually you don't need a .Save at all — edits you make in the handler are captured by the save already in progress. If you truly must save, wrap it in Application.EnableEvents = False … = True and restore events in an error handler.

How do I add a "last saved" timestamp automatically?

In Workbook_BeforeSave, write the value into a cell without calling Save: Sheets("Log").Range("LastSavedAt").Value = Now. Because the handler runs before Excel writes to disk, the save that triggered the event includes your stamp. Add Environ("Username") for who saved it.

Where does Workbook_BeforeSave code go?

In the ThisWorkbook code module — double-click ThisWorkbook under "Microsoft Excel Objects" in the Project Explorer and paste Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean). It does not fire from a standard Module, and macros must be enabled.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-05.