Protect a Worksheet on Workbook CloseAutomatic Sheet Protection with Excel VBA
Stop manually protecting sheets before you share your workbooks. This VBA macro uses the Workbook_BeforeClose event to lock your worksheet automatically — every single time the workbook is closed.
ACA | FMVA® | 19 Years in Finance
Why Automatic Protection Beats Manual
If you distribute Excel workbooks to colleagues or clients, you've probably experienced this: you forget to re-protect a sheet before closing, email the file out, and now someone has accidentally (or intentionally) edited formulas that should have been locked.
This is especially common when you're constantly protecting and unprotecting worksheets during your own edits. It's easy to leave a sheet unprotected by mistake. The solution is to take the decision out of your hands entirely — let VBA handle protection automatically using the Workbook_BeforeClose event.
🎯Why This Matters
The BeforeCloseevent fires every time someone tries to close the workbook — whether they use the X button, File → Close, or a keyboard shortcut. Your protection code runs without fail, every single time. No more "I forgot to lock it" moments before sending.
The Complete VBA Code
Two lines of logic, zero complexity. Here's the complete macro — ready to paste:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Protect the sheet with a password of your choice
Sheets("Sheet1").Protect Password:="RED"
'Step 2: Save the workbook
'You can also use ThisWorkbook instead of ActiveWorkbook here
ActiveWorkbook.Save
End Sub⚠️ This Code Does NOT Go in a Module
Unlike regular macros, this code must be pasted into the ThisWorkbook object inside the VBA Editor — not in a standard Module. If you paste it in the wrong place, the event will never fire. See the setup instructions in Section 6 for exactly where to put it.
How It Works — Step by Step
The macro is short but there's important logic behind every line:
BeforeClose event fires
The moment the user attempts to close the workbook — via X, File → Close, or ALT + F4 — Excel triggers the Workbook_BeforeClose event, running this code before the workbook actually closes.
Sheet1 is protected with a password
Sheets("Sheet1").Protect Password:="RED"locks the sheet and requires the password "RED" to unprotect it. The Password argument is optional — omit it and the sheet will still be protected but anyone can remove it without entering a password. Important: Excel passwords are case-sensitive."RED" and "red" are different passwords.
Workbook is saved immediately
ActiveWorkbook.Save commits the protection to disk. This step is critical — if we skip it, the protection is applied in memory but not persisted. The next time the workbook opens, the sheet will be unprotected again as if nothing happened.
Customizing the Macro
The base code is easy to adapt. Here are the most common modifications:
Change the Sheet Name or Password
Replace "Sheet1" with whatever your sheet tab is actually named. Replace "RED" with your chosen password, or remove the Password:="RED" argument entirely to protect without a password:
'Protect a sheet named "Dashboard" without a password
Sheets("Dashboard").Protect
'Protect a sheet named "Rates" with a strong password
Sheets("Rates").Protect Password:="Finance2024!"Protect Multiple Sheets at Once
Add additional Sheets().Protect statements before the save command. Each sheet can have its own password — or no password at all:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Protect Password:="RED"
Sheets("Summary").Protect Password:="BLUE"
Sheets("Formulas").Protect 'No password on this one
ActiveWorkbook.Save
End Sub💡Pro Tip — Pair With a New Workbook Macro
For full automation, combine this with a macro that creates a fresh workbook on demand. See Create or Add a New Workbook using Excel VBA to build the complete protect-on-close + create-on-open workflow.
Practical Applications
This pattern is genuinely useful in real-world Excel work — not just as a safety net:
- Shared Finance Templates: Lock formula sheets before distributing to the team. Users fill in input cells; critical formula rows stay protected without anyone having to remember to lock them.
- Client-Facing Reports:Protect presentation sheets automatically so clients can't accidentally break the layout, charts, or formulas when reviewing the file.
- Audit Trails: Use with a logging macro to record when the workbook was closed and by whom, then lock the log sheet before saving.
- Data Entry Workbooks:Keep the data entry area open for editing during the session, then automatically lock it on close so historical entries can't be changed retroactively.
Step-by-Step Setup Instructions
This macro goes in a specific location — follow these steps exactly:
Save as Macro-Enabled Format
Go to File → Save As and choose Excel Macro-Enabled Workbook (.xlsm). A regular .xlsx file cannot store VBA code — Excel will silently strip it every time you save.
Open the VBA Editor
Press ALT + F11 to open the Visual Basic Editor. Or go to the Developer tab and click Visual Basic.
Open the ThisWorkbook Object
In the Project window on the left, find your project or workbook name. Click the + sign to expand it, then double-click ThisWorkbook. This opens the code window for workbook-level events.
Select the BeforeClose Event
At the top of the code window, use the two dropdown menus. Set the left dropdown to Workbook and the right dropdown to BeforeClose. VBA will automatically create the event stub for you.
Paste and Customize the Code
Copy the macro from Section 2 above and paste it inside the event stub. Update the sheet name and password to match your workbook, then save with CTRL + S. Test it by closing the workbook — the sheet should be protected and the file saved automatically.
Troubleshooting Common Errors
The Sheet Isn't Getting Protected
If closing the workbook doesn't trigger protection, check these common mistakes:
- Code is in a Module, not ThisWorkbook: The most common error — move the code to the ThisWorkbook object
- Wrong sheet name: The name in quotes must exactly match the sheet tab name, including capitalization
- Macros are disabled: Check Trust Center settings; macros must be enabled for events to fire
- File saved as .xlsx: Event code is stripped from non-macro-enabled formats on save
Runtime Error on ActiveWorkbook.Save
If you get an error on the Save line, it's usually a permissions issue or the workbook is read-only. Swap ActiveWorkbook.Save for ThisWorkbook.Save — it always refers to the workbook containing the code, not whatever happens to be active at that moment.
⚠️ Already-Protected Sheets Will Cause an Error
If the sheet is already protected when BeforeClose fires (e.g., you protected it manually earlier in the session), calling .Protect again will throw a runtime error. Add On Error Resume Next before the Protect call, or unprotect first: Sheets("Sheet1").Unprotect Password:="RED" then re-protect.
Frequently Asked Questions
Why does the code go in ThisWorkbook and not a Module?
BeforeClose is a workbook-level event — it only fires when the handler lives inside the ThisWorkbook object. Code in a regular Module won't run automatically on close; it can only be called manually. Events are tied to specific objects in VBA.
Is the Password argument required?
No — it's completely optional. Omit it and the sheet will still be protected (cells locked, structure frozen) but anyone can unprotect it without a password. Use a password only when you need to control who can edit the sheet.
How do I protect multiple worksheets at once?
Add additional Sheets().Protect statements before the Save line. Each sheet can have its own password. For example: Sheets("Sheet1").Protect Password:="RED" then Sheets("Summary").Protect Password:="BLUE" then ActiveWorkbook.Save.
Can I prompt the user before closing?
Yes. Add a MsgBox with vbYesNo before the protect code. If the user clicks No, set Cancel = True — this cancels the close entirely and the workbook stays open. If they click Yes, continue with the protection and save.
Do I need to save as .xlsm for this to work?
Yes. VBA events only work in .xlsm (Excel Macro-Enabled Workbook) or .xlsb (Excel Binary Workbook) formats. If you save as .xlsx, Excel silently removes all VBA code and the BeforeClose event will never fire.
Download the Working Example
Get the example workbook with the Workbook_BeforeClose macro already configured — open it, close it, and see the protection fire instantly.
Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.