Yes, Excel has a Before Close event that runs VBA code just before a workbook closes. You add it to the ThisWorkbook module, and it fires every time the user closes the file, whether by clicking the X, pressing Ctrl+W, or using File > Close. The event is named Workbook_BeforeClose(Cancel As Boolean), and it is the standard place to prompt for saving, clean up temporary data, or log who last used the file.
What the BeforeClose Event Does
The Workbook_BeforeClose event is one of several workbook-level events in Excel’s VBA object model. It triggers when the workbook is about to close, before the close action is completed. The event receives a Cancel argument: if you set Cancel = True inside the procedure, Excel aborts the close and keeps the workbook open. This is how you can force a user to confirm something or prevent accidental closing.
Because the event runs before the close, you can still interact with the workbook’s cells, save files, or show dialog boxes. It is different from the Workbook_BeforeSave event, which fires when saving, and from Workbook_Deactivate, which fires when the user switches to another workbook.
How to Add a BeforeClose Macro
To write a BeforeClose event, you must place the code in the ThisWorkbook module, not a regular module. Here’s how:
- Press Alt+F11 to open the VBA editor (on Mac: Fn+Option+F11).
- In the Project Explorer, double-click ThisWorkbook.
- In the code window, choose Workbook from the left dropdown and BeforeClose from the right dropdown. Excel inserts the empty procedure.
- Add your code between the
SubandEnd Sublines.
Example that asks the user to confirm closing:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Ask user if they really want to close
If MsgBox("Close this workbook?", vbYesNo + vbQuestion) = vbNo Then
Cancel = True ' abort the close
End If
End Sub
If you set Cancel = True, Excel does not close the workbook, and the event ends. Note that the event does not automatically save; you must call ThisWorkbook.Save if you want to save without prompting.
Common Uses for BeforeClose
- Auto-save before closing – save the workbook without showing the Save dialog.
- Clean up temporary data – clear cells, remove filters, or delete temporary sheets.
- Log user activity – write the current user name and timestamp to a separate file.
- Validate data before exit – check that required fields are filled, and cancel close if not.
- Reset application settings – restore screen updating or calculation mode.
For example, to save automatically on close:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Save without prompting
Me.Save
End Sub
This works even if the workbook has never been saved, because Me.Save will open the Save As dialog if needed.
How to Choose Between BeforeClose and Other Events
If you need to run code when the user closes the workbook, BeforeClose is the right choice. But if you only need to react to saving, use Workbook_BeforeSave. If you need to react to the workbook being opened, use Workbook_Open. Here’s a quick comparison:
- Workbook_Open – runs when the workbook is opened.
- Workbook_BeforeSave – runs before saving, can cancel the save.
- Workbook_BeforeClose – runs before closing, can cancel the close.
- Workbook_Deactivate – runs when the user switches to another workbook.
If you want to run code both before save and before close, you can call a common procedure from both events to avoid duplication.
Common Problems and How to Fix Them
One frequent issue is that the BeforeClose event does not fire when the user closes Excel entirely (e.g., clicking the X on the Excel application window). In that case, the event still fires, but the workbook may close before your code finishes if you don’t handle it properly. To ensure your code runs completely, avoid using DoEvents or long operations that could be interrupted.
Another problem: if you set Cancel = True but the user is closing Excel, Excel may still close if the user confirms the close in a subsequent dialog. Test your code in a copy of the workbook.
If your code causes an error, the event may not complete. Use error handling to ensure the close proceeds:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
' your code
On Error GoTo 0
End Sub
But be careful: On Error Resume Next can hide problems. Better to use a structured error handler that logs the error and then lets the close continue.
Security and Macro Settings
BeforeClose code only runs if macros are enabled. If the workbook is opened with macros disabled, the event does not fire. To ensure your event runs, you must either save the workbook as a macro-enabled file (.xlsm) and have the user enable content, or digitally sign your macro. Excel’s Trust Center settings control macro execution; you can find them under File > Options > Trust Center > Trust Center Settings > Macro Settings.
Be aware that some organizations disable macros entirely for security reasons. If your BeforeClose code is critical, you may need to provide instructions to enable macros. Also, never store sensitive data in VBA code, as it can be viewed by anyone with access to the file.
Alternatives to VBA BeforeClose
If you cannot use VBA (e.g., on Excel for the web or on a locked-down machine), you have limited options. Excel for the web does not support VBA events. In Excel for Microsoft 365 on Windows, you can use the Workbook_BeforeClose event only via VBA. There is no built-in non-VBA way to run code on close.
For simple tasks like saving automatically, you can rely on Excel’s AutoRecover feature, but it does not run custom code. If you need to prompt for confirmation, you could use a Data Validation rule that checks on entry, but that doesn’t run on close.
For users who cannot enable macros, consider using a template that guides them to close properly, or use a separate script outside Excel (e.g., PowerShell) that monitors the file, but that is much more complex.
Frequently Asked Questions
Q: Does BeforeClose fire when Excel crashes?
No. If Excel crashes or is force-closed, the event does not run. Only normal close operations trigger it.
Q: Can I prevent the user from closing the workbook at all?
Yes, by setting Cancel = True unconditionally. But this can trap the user; it’s better to provide a way to exit, such as checking a cell value or a password.
Q: How do I save the workbook without the Save dialog in BeforeClose?
Use Me.Save or ThisWorkbook.Save. If the file has never been saved, it will prompt for a location.
Q: Does BeforeClose work on Excel for Mac?
Yes, the event is supported in Excel for Mac. The VBA editor shortcut is Fn+Option+F11. The code is identical.
Q: Can I run a BeforeClose macro that also closes other workbooks?
Yes, you can close other workbooks from within the event, but be careful: closing another workbook may trigger its own BeforeClose event, leading to recursion. Use a flag to prevent infinite loops.
Q: What happens if I set Cancel = True and then close again?
The event will fire again each time you attempt to close. If you set Cancel = True and then later want to allow close, you need to reset a flag or condition.
Related
- Creating a Split Form in Access: Steps and Tips
- String Array Split in Java: Methods, Examples, and Tips
- SQL String Split: How to Separate Delimited Values
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Export a CSV File in Excel Without Losing DataTo export a CSV from Excel for Microsoft 365 on Windows, open the workbook, select the worksheet you…
- Assembly Split in CATIA: How to Separate a ProductYes. In CATIA, you can split an assembly by reorganizing its components into separate CATProduct documents or subassemblies,…
- Flowcharts in PowerPoint: The Best Way to Make OneFor most people, the best way to make a flowchart in PowerPoint is to build it with native…
- Excel PivotTable Dates: How to Ungroup Them EasilyTo ungroup dates in an Excel PivotTable, click a grouped date label, then choose PivotTable Analyze > Ungroup…
- Split Junction Box: Types, Uses and SafetyA split junction box is an electrical enclosure made in two parts or divided into separate sections, intended…
- How to Create a Signature in Outlook Desktop on WindowsTo create a signature in Outlook desktop on Windows, open Settings > Accounts > Signatures in new Outlook,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.