How to Protect or Unprotect All Sheets in an Excel Workbook at Once

You need to lock down or unlock multiple worksheets in an Excel file. Manually protecting each sheet is slow and error-prone. Excel’s interface does not have a single button to protect all sheets. This article explains how to use a VBA macro to secure or unsecure every worksheet in your workbook with one click.

Using a VBA Macro for Bulk Sheet Protection

Excel’s standard protection feature applies only to the active worksheet. To protect all sheets, you must write a Visual Basic for Applications macro. A macro automates the task by looping through every worksheet in the workbook. You can assign a password or leave it blank for simple locking. The macro can also unprotect all sheets if they are already secured.

Prerequisites for Running Macros

Your Excel file must be saved as a macro-enabled workbook with the .xlsm extension. You must also enable the Developer tab in the ribbon. Macros are disabled by default for security. You may need to adjust your Trust Center settings to allow macro execution.

Steps to Create and Run the Protection Macro

1. Enable the Developer tab
   Go to File > Options > Customize Ribbon. In the right panel, check the box for Developer. Click OK.
2. Open the VBA editor
   Click the Developer tab. In the Code group, click Visual Basic. You can also press Alt + F11.
3. Insert a new module
   In the VBA editor, from the menu, click Insert > Module. A blank code window will appear.
4. Paste the macro code
   Copy and paste the following code into the module window.

   Sub ProtectAllSheets()
Dim ws As Worksheet
Dim pwd As String
pwd = InputBox("Enter a password (leave blank for no password):", "Protect All Sheets")
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=pwd
Next ws
MsgBox "All sheets have been protected."
End Sub

   Sub UnprotectAllSheets()
Dim ws As Worksheet
Dim pwd As String
pwd = InputBox("Enter the password to unprotect:", "Unprotect All Sheets")
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:=pwd
Next ws
MsgBox "All sheets have been unprotected."
End Sub

5. Close the VBA editor
   Click the X in the top-right corner or press Alt + Q to return to Excel.
6. Run the macro
   Click Developer > Macros or press Alt + F8. Select ProtectAllSheets or UnprotectAllSheets from the list. Click Run.
7. Enter a password if prompted
   A dialog box will appear. Type a password for protection or unprotection. Click OK. A message confirms the action is complete.
8. Save the workbook
   Go to File > Save As. Choose a location. For Save as type, select Excel Macro-Enabled Workbook (*.xlsm). Click Save.

Common Mistakes and Limitations

Macro Fails with “Run-time error ‘1004’”

This error often means the macro tried to protect a chart sheet or a very hidden worksheet. The code provided only loops through standard worksheets. Modify the code line “For Each ws In ThisWorkbook.Worksheets” to “For Each ws In ThisWorkbook.Sheets” to include chart sheets.

Forgotten Password for Unprotection

The UnprotectAllSheets macro requires the correct password. Excel sheet protection is not strong encryption. If you forget the password, you cannot use the macro. You would need to use a third-party password recovery tool or recreate the workbook.

Macro Does Not Protect Workbook Structure

This macro protects worksheet contents from editing. It does not protect the workbook structure, like preventing sheet deletion. For that, use Review > Protect Workbook. You can run both protections for complete security.

Manual Method vs. VBA Macro: Key Differences

Item	Manual Protection	VBA Macro
Time Required	High: Must repeat steps for each sheet	Low: One-time setup, instant execution
Password Consistency	Risk of using different passwords per sheet	Ensures the same password is applied to all sheets
Skill Level Needed	Basic Excel knowledge	Requires copying code and enabling macros
Reusability	Steps must be repeated for every workbook	Macro can be saved in Personal.xlsb for use in any workbook
Error Handling	None: User must notice if a sheet was missed	Automated loop ensures no sheet is skipped

You can now secure an entire Excel workbook with a single command. Use the ProtectAllSheets macro to apply uniform security settings. For advanced use, assign the macro to a button on your Quick Access Toolbar. This lets you protect all sheets without opening the Developer tab.