Macro to Protect and Unprotect All Sheets With a Password

These VBA macros protect or unprotect every worksheet in the workbook with one password, instead of doing it sheet by sheet.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Protect all sheets

Sub ProtectAllSheets()
    Dim ws As Worksheet, pwd As String
    pwd = InputBox("Enter a password:")
    For Each ws In Worksheets
        ws.Protect Password:=pwd
    Next ws
End Sub

If you leave the password empty, the sheets are protected without one.

Unprotect all sheets

Sub UnprotectAllSheets()
    Dim ws As Worksheet, pwd As String
    pwd = InputBox("Enter the password:")
    On Error Resume Next
    For Each ws In Worksheets
        ws.Unprotect Password:=pwd
    Next ws
End Sub

On Error Resume Next stops the macro from failing if one sheet has a different password. Those sheets simply stay protected.

Protect but still allow filtering and formatting

Sub ProtectAllowFiltering()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect Password:="1234", AllowFiltering:=True, AllowFormattingCells:=True
    Next ws
End Sub

Change 1234 to your password. Other options include AllowSorting, AllowInsertingRows and AllowDeletingRows.

Protect the workbook structure

Sheet protection stops cells being edited. Workbook structure protection stops sheets being added, deleted, renamed or unhidden:

Sub ProtectWorkbookStructure()
    ActiveWorkbook.Protect Password:="1234", Structure:=True
End Sub

Things to know

  • Write the password down. If you forget it, Excel can’t recover it.
  • Sheet protection is meant to prevent accidents, not to secure data. For sensitive files, use File → Info → Protect Workbook → Encrypt with Password.
  • Only locked cells are protected. To let users type in some cells, unlock them first with Format Cells → Protection.

Related macros