VBA Unprotect in Excel — Remove Sheet Protection Safely (and the Password Trap)

TL;DR — Worksheet.Unprotect is the master switch turned off. It stops Excel from enforcing the Locked labels, but it does not touch those labels — unprotecting a sheet does not unlock its cells, it just stops honoring the locks for now. If the sheet was protected with a password you must pass the exact same one, or you get run-time error 1004 (wrong password) or a modal dialog that hangs an unattended macro. Almost every editing macro wraps its work in an unprotect ▸ change ▸ protect sandwich — and the one rule that matters is: re-protect in an error handler, so a crash never leaves the sheet open.

Sub UpdateAndReLock()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Form")

    ws.Unprotect Password:="ac"          ' switch enforcement OFF (exact password)
    On Error GoTo ReLock                 ' whatever happens next...
    ws.Range("C4:C12").ClearContents     ' ...do the work...
ReLock:
    ws.Protect Password:="ac"            ' ...always switch it back ON
End Sub

Unprotect is the code behind Review ▸ Unprotect Sheet, and it is the necessary first move whenever a macro has to write to a sheet you have locked down. It looks trivial — one line, one optional argument — and it is, right up until the sheet has a password or the macro errors halfway through. Both of those turn a one-liner into a support ticket. The mental model that keeps you out of trouble is that Unprotect is temporary and paired: you take the lock off to do a job, and you are responsible for putting it back.

What you'll learn

The mental model — Unprotect deactivates enforcement, it does not change the Locked labels
The one rule that matters — re-protect in an error handler so a crash never leaves the sheet open
The password trap: why a wrong or missing password throws 1004 or hangs on a dialog
Why calling Unprotect on an already-open sheet is a harmless no-op you can rely on
How to unprotect every sheet in a workbook with one loop
The honest limit — VBA cannot recover a password you have forgotten

The mental model: switch off, labels untouched

Protection is two layers: the per-cell Locked label (the decision about who may edit what) and Protect/Unprotect (the switch that enforces or ignores those labels). Unprotect operates only on the switch. Every cell keeps the exact Locked state it had; you have simply told Excel to stop enforcing it.

This matters because people expect Unprotect to "open the cells up," and then are surprised when they Protect again and the same cells are locked as before. Nothing was unlocked — enforcement was paused and then resumed. If you want to change which cells are editable, that is a job for the Locked property while the sheet is unprotected, not for Unprotect itself.

The rule that matters most: re-protect in an error handler

The standard shape of any macro that edits a protected sheet is a sandwich: unprotect, do the work, protect again. The danger lives in the middle slice. If the "do the work" step raises an error — a bad reference, a missing sheet, a type mismatch — execution stops, the closing Protect never runs, and the sheet is left wide open. The user closes the file none the wiser, and your carefully locked template is now editable by anyone.

The fix is to make the re-protect unconditional with an error handler, so it runs whether the work succeeds or blows up:

Sub SafeEdit()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Form")

    ws.Unprotect Password:="ac"
    On Error GoTo CleanExit
    ' ... the real work, which might fail ...
    ws.Range("C4:C12").ClearContents
CleanExit:
    ws.Protect Password:="ac", UserInterfaceOnly:=True
    If Err.Number <> 0 Then MsgBox "Update failed: " & Err.Description
End Sub

This is the same CleanExit discipline that every reliable macro uses for closing files or restoring ScreenUpdating — a single exit label where the "put things back" code lives. See error handling for the full pattern. The judgment is simple: the line that re-protects the sheet must not be reachable only on the happy path.

The password trap

If a sheet was protected with a password, Unprotect needs that exact password, and getting it wrong fails in two different and equally annoying ways. Pass the wrong password and you get run-time error 1004, "The password you supplied is not correct." Pass no password to a password-protected sheet and, in an interactive session, Excel pops the password prompt dialog — which silently hangs an unattended or scheduled macro forever, because there is nobody there to type it.

ws.Unprotect Password:="ac"     ' right: exact password, runs clean
ws.Unprotect                    ' on a password sheet: pops a modal dialog - hangs a batch job
ws.Unprotect Password:="wrong"  ' run-time error 1004

The rule that follows: an automated macro must always pass the password explicitly, and it must never rely on a human being present to answer a dialog. If you protect with a password anywhere, store it in a constant your unprotect code can read, so the two never drift apart.

Unprotect on an open sheet is a safe no-op

A small fact that makes defensive code cleaner: calling Unprotect on a sheet that is not protected does nothing and raises no error. That means you can Unprotect at the top of a routine without first checking ws.ProtectContents — if the sheet was already open, the call is a harmless pass-through; if it was locked, it opens it. This is unusually forgiving for VBA, and it lets you write the unprotect step without a guard. (The reverse is not symmetric: Protect on an already-protected sheet does raise an error, so re-protecting blindly needs more care.)

Unprotecting every sheet in one loop

A frequent real task is clearing protection across a whole workbook before a bulk rebuild. Loop the Worksheets collection and unprotect each, passing the shared password:

Sub UnprotectAllSheets()
    Const PW As String = "ac"
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect Password:=PW      ' no-op on any sheet that was not protected
    Next ws
End Sub

Because Unprotect is a no-op on unprotected sheets, the loop is safe even when only some tabs were locked. Pair it with a matching protect-all loop at the end of the rebuild. If different sheets used different passwords, this simple loop will not work — which is a good argument for using one password (or none) across a workbook you have to automate. See Worksheets for iterating the collection.

The honest limit: forgotten passwords

It is worth stating plainly, because it is one of the most common searches behind this topic: Unprotect requires the password that was set. VBA gives you no supported way to recover or reveal a password you have genuinely forgotten — the language can only apply a password you already know. If you protected your own sheet and lost the password, the practical path is to restore from a backup or an earlier version of the file. Build the habit of keeping the password in a constant next to the code that uses it, so "I forgot it" never happens to a workbook you maintain.

How ExcelMaster helps

Unprotecting is easy to get almost right and then have it bite you: a macro errors between the unprotect and the re-protect and silently leaves your template open, a scheduled job hangs forever on a password dialog nobody can see, or a loop trips over sheets that each had a different password.

ExcelMaster lets you describe the job — "update these cells on the locked sheet and put the protection back" — and it writes the unprotect-work-protect sandwich with the re-protect in a CleanExit handler so a crash can never leave the sheet open, passes the password explicitly so nothing hangs on a dialog, and reminds you that a forgotten password cannot be recovered from code. You keep the workbook and the code.

Frequently asked questions

How do I unprotect a sheet with a password in VBA?

Pass the exact password to the Password argument: ws.Unprotect Password:="ac". It must match the password used when the sheet was protected. A wrong password raises run-time error 1004; omitting the password on a password-protected sheet pops a modal dialog that will hang an unattended macro. Always pass the password explicitly in automated code.

Why do I get error 1004 when unprotecting a sheet?

The most common cause is a wrong password — "The password you supplied is not correct." Check that the Unprotect password matches the one used to Protect, ideally by storing both in a single constant. Error 1004 can also appear if you reference a sheet that does not exist; confirm the Worksheets("...") name is spelled exactly as the tab.

Does unprotecting a sheet unlock its cells?

No. Unprotect only switches off enforcement of the Locked labels; every cell keeps the exact Locked state it had. When you protect the sheet again, the same cells are locked as before. To change which cells are editable, set the Locked property while the sheet is unprotected — unprotecting alone changes nothing about the locks.

How do I unprotect all sheets in a workbook?

Loop the collection and unprotect each with the shared password: For Each ws In ThisWorkbook.Worksheets: ws.Unprotect Password:="ac": Next ws. Because Unprotect is a no-op on any sheet that was not protected, the loop is safe even when only some tabs were locked. This assumes one password across the workbook; different passwords per sheet would each need their own value.

Can VBA recover a forgotten sheet-protection password?

No. VBA can only apply a password you already know; it has no supported way to reveal or recover a forgotten one. If you lost the password to your own sheet, restore the workbook from a backup or an earlier version. Keep the password in a constant next to your unprotect code so it never gets lost for a file you maintain.

Tested in

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