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
Trueto 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:
- Press Alt+F11 (or Option+F11 on Mac) to open the Visual Basic Editor.
- 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).
- Paste the code into the code window.
- 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
- Excel Spreadsheet Zoom Lock: How to Freeze Zoom Level
- Excel Staff Schedule Template: Free Options & How to Build One
- Excel Spreadsheet Templates for Tracking Time
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Code to Refresh Pivot Tables Automatically in ExcelTo refresh every PivotTable in the workbook that contains your VBA, run a macro that loops through each…
- Excel VBA InputBox Function: Syntax and ExamplesThe Excel VBA InputBox function displays a dialog that asks a user for a response and returns that…
- Excel VBA Data Validation List: Setup and ExamplesIn Excel for Microsoft 365 on Windows, create a VBA dropdown by applying data validation with Type:=xlValidateList and…
- Using MsgBox in VBA: Syntax, Buttons, and Return ValuesIn Excel VBA, use MsgBox to show a pop-up message or ask a user a simple question. Call…
- Excel Macro Step Into: How to Debug VBA Line by LineIn Excel VBA, Step Into runs a macro one statement at a time and enters any procedure or…
- Excel VBA Paste Values: Methods, Code, and Common FixesTo paste values with Excel VBA, copy a source range and use PasteSpecial Paste:=xlPasteValues, or assign the source…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.