VBA Protect Worksheet on Close: Re-Lock Automatically

This guide is part of the FinDataPro Excel VBA Workbook Events series — it pairs directly with unprotecting the same sheet on open.

What Is Protect-on-Close?

Paste this into the ThisWorkbook module:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wsData As Worksheet
    Set wsData = ThisWorkbook.Sheets("Data Entry")

    If Not wsData.ProtectContents Then
        wsData.Protect Password:="YourPasswordHere", _
                       UserInterfaceOnly:=True
    End If
End Sub

The sheet that was unprotected on open goes straight back to locked the instant the file closes — no manual re-lock step for anyone to skip.

Where the Code Goes

Like every workbook-level event, this belongs in the ThisWorkbook module, not a standard module or the sheet's own module. It is the natural bookend to Workbook_Open, which unprotects the same sheet when the file opens.

Finance Use Case: Locking a Shared Template Between Uses

A month-end template shared across preparers needs to be editable while someone is actively entering data, and locked the rest of the time so formulas and headers cannot be accidentally overwritten. Tying protection to the open and close events means the sheet is only ever unlocked while it is genuinely in use — never left open indefinitely by a forgotten manual step.

The risk this closes off is specific: a file left unprotected between sessions on a shared drive is one accidental keystroke away from a broken formula that nobody notices until the next month's figures look wrong. Re-locking automatically on close means that window of vulnerability shrinks to exactly the time the file is genuinely open in someone's hands, rather than however long it happens to sit idle on the network afterward.

Guarding Against a Double-Protect Error

Calling Protect on a sheet that is already protected raises a runtime error. Checking wsData.ProtectContents first — as in the snippet above — means the close event does not crash if a user manually protected the sheet mid-session before the macro got to it.

UserInterfaceOnly: Letting Macros Still Write to a Locked Sheet

Plain Protect blocks every write, including from other VBA code the next time the file opens. UserInterfaceOnly:=True blocks manual edits while still letting the Workbook_Open macro unprotect and write to the sheet on the next open. Without it, a workbook that protects itself on close can end up unable to unprotect itself on the next open.

Combining With BeforeClose's Other Job

Workbook_BeforeClose can only be declared once per workbook, so if the file also needs to block closing while entries are unposted, both checks live in the same sub. Run the unposted-entries check first and set Cancel = True if needed — the protection code only needs to run once the close is actually going ahead.

Full reference: Microsoft Learn Worksheet.Protect method reference.

Frequently Asked Questions

Conclusion

Protect-on-close is the other half of the unprotect-on-open pattern — together they make sure a shared sheet is only ever editable while genuinely in use. Guard against the double-protect error, use UserInterfaceOnly if other macros need write access later, and combine it with the unposted-entries check in the same event where both are needed.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

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.