Excel VBA to Protect and Unprotect Sheet: Full Code

VBA & macros · Updated November 22, 2026

The fastest way to protect or unprotect a sheet in Excel VBA is Worksheets("Sheet1").Protect and Worksheets("Sheet1").Unprotect. For a password, add Password:="YourPassword" inside the parentheses, as in Worksheets("Sheet1").Protect Password:="abc123".

Why Use VBA Instead of the Ribbon

Excel’s built-in protection (Review > Protect Sheet) works fine for one-off tasks, but VBA gives you three advantages: automation, consistency, and conditional logic. You can protect every sheet in a workbook with a loop, unprotect before a macro makes changes and re-protect afterward, or toggle protection based on a cell value. VBA also lets you set granular options—like allowing users to select locked cells but not edit them—without clicking through dialog boxes each time.

For example, a common workflow is to unprotect a sheet, update data, then protect it again. Without VBA, you’d manually enter the password twice each time. With a macro, it’s one click.

Basic Protect and Unprotect Syntax

The core methods are simple. Here’s the full syntax with the most useful parameters:

' Protect with password and allow some actions
Worksheets("Sheet1").Protect Password:="secret", _
    UserInterfaceOnly:=True, _
    AllowFormattingCells:=True

' Unprotect with the same password
Worksheets("Sheet1").Unprotect Password:="secret"
  • Password: Optional string. If omitted, anyone can unprotect via the ribbon (no password prompt).
  • UserInterfaceOnly: If True, protection only blocks manual user actions; VBA code can still modify cells. This is extremely useful—you can leave protection on while macros run.
  • AllowFormattingCells, AllowSorting, AllowFiltering, etc.: Set to True to let users do those actions even when the sheet is protected.

Note: UserInterfaceOnly is not saved when you close and reopen the workbook—you must reapply it each time. If you need it permanently, run the protection code in the Workbook_Open event.

Protecting All Sheets in a Workbook

To protect every sheet (or a list of specific ones), loop through the Worksheets collection:

' Protect all sheets with the same password
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Protect Password:="secret", UserInterfaceOnly:=True
Next ws

To unprotect all, change Protect to Unprotect in the loop. If you only want certain sheets, check the name:

If ws.Name = "Data" Or ws.Name = "Summary" Then
    ws.Protect Password:="secret"
End If

How to Run the Code

You don’t need a separate file—just open the VBA editor and run the macro directly:

  1. Press Alt+F11 (or Option+F11 on Mac) to open the Visual Basic Editor.
  2. In the Project Explorer, double-click the sheet you want to attach the macro to (e.g., Sheet1) or insert a new module (Insert > Module).
  3. Paste the code into the code window.
  4. Place the cursor inside the subroutine and press F5 to run it, or close the editor and run it from the Macros dialog (Alt+F8).

If you want a button to toggle protection, you can assign a macro to a shape or Form Control button. For example, create a rectangle, right-click it, choose Assign Macro, and select your subroutine.

Common Problems and Solutions

Here are the issues people run into most often:

  • “Unprotect Sheet” is grayed out: That means the workbook itself is protected (Review > Protect Workbook) or the sheet is part of a protected structure. Unprotect the workbook first with ThisWorkbook.Unprotect.
  • Password forgotten: There is no built-in way to recover a lost sheet password. Microsoft does not provide a backdoor. You can try third-party tools, but they are not officially supported and may not work with newer file formats.
  • UserInterfaceOnly not working: As mentioned, this setting resets when the workbook closes. If you expect it to persist, you must reapply it in the Workbook_Open event.
  • Macro fails with “Protect method of Worksheet class failed”: This usually happens if the sheet is already protected with a different password. Check the existing protection state first using If ws.ProtectContents Then.

Another common misconception: protecting a sheet does not secure your data. It only prevents casual changes. Anyone with basic VBA knowledge can unprotect a sheet without a password by copying the sheet to a new workbook and removing the protection—or by using the Unprotect method with no password if none was set. For real security, you need to encrypt the workbook file itself (File > Info > Protect Workbook > Encrypt with Password).

Comparing Protect, ProtectContents, and Workbook Protection

Excel has several layers of protection, and they’re often confused:

  • Worksheet protection (Protect): Locks cells, prevents editing, sorting, etc. This is what most people mean.
  • Workbook structure protection (ThisWorkbook.Protect): Prevents adding, deleting, moving, or hiding sheets. It does not lock cells.
  • File encryption: Requires a password to open the file. Set in the Save As dialog or via File > Info.

You can combine them. For example, protect the worksheet to stop edits and protect the workbook structure to stop someone from deleting the sheet. In VBA, workbook structure protection uses ThisWorkbook.Protect Password:="secret" and ThisWorkbook.Unprotect.

Best Practices and Safety

Before running any macro that modifies protection, save a copy of your workbook—macros cannot be undone with Ctrl+Z. If your code makes an error, you could lock yourself out of your own data. Also, avoid hard-coding passwords in plain text if the workbook will be shared; anyone who opens the VBA editor can read them. Consider using a simple user input box for the password:

Dim pwd As String
pwd = InputBox("Enter password to protect sheet")
If pwd <> "" Then
    Worksheets("Sheet1").Protect Password:=pwd
End If

If you’re protecting a sheet that contains formulas, remember that locked cells (default) prevent users from seeing the formula bar. If you want to allow viewing but not editing, set Locked to False on the formula cells and only lock the input cells.

Frequently Asked Questions

Q: How do I unprotect a sheet without a password in VBA?
If the sheet was protected without a password, simply use Worksheets("Sheet1").Unprotect with no arguments. If a password was set, you need the password—there’s no legitimate bypass.

Q: Can I protect only specific cells, not the whole sheet?
Yes. By default, all cells are locked when you protect the sheet. To leave some cells editable, select those cells, open Format Cells (Ctrl+1), go to the Protection tab, and uncheck Locked. Then protect the sheet. In VBA, you can set Range("A1").Locked = False before protecting.

Q: Why does my macro fail to edit a protected sheet even with UserInterfaceOnly:=True?
That parameter only works when you run the macro from the same Excel session that applied it. If you close and reopen the workbook, protection reverts to full. Also, if the workbook is protected (structure), you need to unprotect it first.

Q: How do I protect a sheet but allow sorting and autofilter?
Use the AllowSorting and AllowFiltering parameters. For example: Worksheets("Data").Protect Password:="x", AllowSorting:=True, AllowFiltering:=True.

Q: Is there a way to know if a sheet is already protected?
Yes, check the ProtectContents property: If Worksheets("Sheet1").ProtectContents Then MsgBox "Protected". This returns True if the sheet is protected.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.