VBA Workbook_BeforeClose in Excel — Cancel the Close, Prompt to Save, and Where the Code Must Live

TL;DR — Workbook_BeforeClose is an event Excel fires the instant someone tries to close the workbook — before any window is torn down. It hands you one argument, Cancel, and that argument is the whole point: set Cancel = True and the close is called off, the file stays open. So this isn't a farewell note you run on the way out — it's a checkpoint that can turn the user around at the door. The code must live in ThisWorkbook, and if you want to run your own save prompt without Excel also asking, set ThisWorkbook.Saved = True to silence its built-in dialog.

' Lives in ThisWorkbook — NOT a Module.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If ThisWorkbook.Sheets("Form").Range("Approved").Value <> "Yes" Then
        MsgBox "Set Approved = Yes before closing this file.", vbExclamation
        Cancel = True          ' <-- call off the close; the workbook stays open
    End If
End Sub

Most macros run when a user asks — a button, a shortcut. Workbook_BeforeClose runs when a user tries to leave, and that timing is what makes it useful and dangerous in equal measure. It's the event behind "you have unsaved changes," forced sign-offs, clean-up-on-exit, and "log who closed the file." It's also the event people wire up wrong most often — because they treat it as a notification when Excel actually offers them a veto.

What you'll learn

The mental model — a doorman event that hands you a Cancel veto, not just a heads-up
How to actually stop a close (and why a vbYesNo prompt alone doesn't)
The double-prompt trap — and the ThisWorkbook.Saved = True lever that fixes it
Why BeforeClose must never be your data-integrity safety net (crashes skip it)
Sheet-close vs app-quit — and where the code has to live

The mental model: a doorman, not a farewell note

Workbook_BeforeClose fires the moment a close is requested — the window's X, File ▸ Close, Application.Quit, or another macro calling .Close — and it fires before Excel does anything irreversible. Crucially, it passes a single Boolean by reference called Cancel. You never call this Sub; Excel calls you, waits for you to finish, then looks at what Cancel is set to.

That one argument reframes everything. If you leave Cancel alone, the close proceeds as normal. If you set Cancel = True, Excel abandons the close and the workbook stays open exactly as it was. So the handler is a doorman: it can wave the user through, or it can stop them and send them back to fix something. Read it as "notify me before closing" and you'll miss the entire power of the event.

The signature is fixed and the location is not negotiable: Private Sub Workbook_BeforeClose(Cancel As Boolean), in the ThisWorkbook code module — double-click ThisWorkbook under "Microsoft Excel Objects" in the Project Explorer. Put it in a standard Module and it will never fire, which is the same "where does the code live" rule as Workbook_Open.

The rule that matters most: a prompt without `Cancel` is theatre

Here is the mistake almost everyone makes first. You want to confirm before closing, so you write:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "Are you sure you want to close?", vbYesNo   ' asks... and ignores the answer
End Sub

The dialog appears, the user clicks No — and the workbook closes anyway. Nothing in that code touches Cancel, so Excel proceeds. The prompt was pure theatre. The rule: a decision you want to enforce must be written back into Cancel. Capture the answer and act on it:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If MsgBox("Close without exporting the report?", vbYesNo + vbQuestion) = vbNo Then
        Cancel = True          ' user said No -> abort the close
    End If
End Sub

Now "No" actually keeps the file open. This is the difference between an event that informs and an event that guards: the guard is only real when Cancel = True can be reached.

The trap that annoys users: Excel asks to save twice

The second classic bug shows up the moment your handler saves. You add a "save before you go" step, and now closing a changed workbook produces two dialogs: your prompt, then Excel's own "Do you want to save your changes?" The reason is that both you and Excel are trying to handle the same unsaved state.

The lever is the workbook's Saved property. Saved is Excel's flag for "there are no unsaved changes." When you set it to True, you are telling Excel the file is clean — so it closes without showing its own save prompt, even if there genuinely are edits. Handle the save yourself, then flip Saved:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim answer As VbMsgBoxResult
    answer = MsgBox("Save changes before closing?", vbYesNoCancel + vbQuestion)

    If answer = vbCancel Then
        Cancel = True                 ' don't close at all
    ElseIf answer = vbYes Then
        ThisWorkbook.Save             ' save, then let the close continue
    Else ' vbNo
        ThisWorkbook.Saved = True     ' discard: tell Excel it's clean so it won't re-ask
    End If
End Sub

Setting Saved = True doesn't save anything — it suppresses the prompt by claiming the workbook is already saved. Use it deliberately: it's the switch that makes your own save logic the only save logic the user sees.

The rule that keeps your data safe: `BeforeClose` is not a safety net

Workbook_BeforeClose fires on every orderly close — the X, File ▸ Close, .Close, Application.Quit. It does not fire when Excel crashes, when the process is killed, or on a power loss. That makes one temptation dangerous: never make BeforeClose the only thing that saves your data. If the "save the only copy" logic lives here and Excel dies, the copy is gone. Treat BeforeClose as a courtesy (prompt, tidy up, log), and keep real persistence on an explicit save or a timed autosave, not on the exit path.

One more edge to know: when the user is quitting Excel itself with several workbooks open, each workbook gets its own BeforeClose. Setting Cancel = True in one of them stops that workbook from closing, but siblings that already ran their handler may be in a half-closed state. If you need "all or nothing" on quit, coordinate from Workbook_BeforeClose sparingly and test the multi-file case.

The distinction that trips people: BeforeClose vs BeforeSave

BeforeClose guards the exit; its sibling Workbook_BeforeSave guards the save and hands you the same kind of Cancel veto (plus a SaveAsUI flag). They pair naturally — validate on save, confirm on close — and both share the event family's golden rule: if a handler writes to cells or saves, wrap that action in Application.EnableEvents = False … = True and restore it in an error handler, exactly as Worksheet_Change does, so your own write can't re-trigger the machinery.

How ExcelMaster helps

A close guard is four small decisions that are easy to get subtly wrong: the code in ThisWorkbook (not a Module), the Cancel write that actually stops the close, the Saved = True flip that avoids the double prompt, and the judgment call about what not to trust the exit path with.

ExcelMaster lets you describe the behaviour instead. Say "before this file closes, if the Approved cell isn't Yes, stop the close and tell the user," and it writes a Workbook_BeforeClose in ThisWorkbook that sets Cancel correctly, handles the save prompt without doubling it, and guards any write. You keep the workbook and the code; you skip the trial-and-error of learning which lever does what.

Frequently asked questions

How do I stop a workbook from closing in VBA?

Set the Cancel argument to True inside Workbook_BeforeClose: Private Sub Workbook_BeforeClose(Cancel As Boolean) … Cancel = True. Excel passes Cancel by reference and checks it after your handler runs; True aborts the close and the file stays open. A MsgBox on its own does nothing — you must write the decision back into Cancel.

Why does Excel ask me to save twice when I close?

Because your BeforeClose handler prompts to save and Excel still thinks there are unsaved changes, so it shows its own dialog too. Handle the save yourself, then set ThisWorkbook.Saved = True to tell Excel the workbook is clean — that suppresses its built-in "Do you want to save?" prompt.

Where does Workbook_BeforeClose code go?

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

Does Workbook_BeforeClose run if Excel crashes?

No. It fires only on an orderly close — the window X, File ▸ Close, .Close, or Application.Quit. A crash, a killed process, or a power loss skips it entirely. Never rely on BeforeClose as the only thing that saves your data.

How is BeforeClose different from Workbook_BeforeSave?

BeforeClose fires when the file is closing and lets you cancel the close; Workbook_BeforeSave fires when the file is being saved and lets you cancel the save. Closing a changed file usually triggers a save prompt, so the two often appear together, but they are separate events with separate Cancel flags.

Tested in

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