Unprotect All Excel Sheets

When I’m working on an Excel file for a client, it’s nice to have a quick way to unprotect all the worksheets, so I can make changes.
It’s easy to unprotect the sheets with a macro, like this one.

Sub UnprotectAllSheets()
Dim ws As Worksheet
 For Each ws In ThisWorkbook.Worksheets
   ws.Unprotect Password:="MyPassword"
 Next ws
End Sub

It’s even easier to run that code to unprotect the sheets, if I add a shortcut for the macro, like Ctrl + Shift + U
However, I don’t want to make it too easy to unprotect the sheets, or the users might do it accidentally. (I’m not going to worry about the few people who are determined to unprotect the sheets – that’s a different problem!)

Ask for Password in the Unprotect Macro

To help prevent users from accidentally running the macro, I add a few lines to the macro, to prompt for a password. When the macro starts, it prompts you for a password. If you enter the correct password, the sheets are unprotected.

Sub UnprotectAllSheets()
Dim ws As Worksheet
Dim strPwd As String
Dim strCheck As String
strCheck = "MyPassword"
strPwd = InputBox("Enter Password", "Password", "Enter Password")
If strPwd = strCheck Then
  For Each ws In ThisWorkbook.Worksheets
    ws.Unprotect Password:=strPwd
  Next ws
  MsgBox "Incorrect Password"
End If
End Sub

Protect the VBA Code

In the UnprotectAllSheets macro, the password, “MyPassword”, is shown. Anyone who peeks at the code could easily see that password, and use it to unprotect the sheets.
To help keep the password hidden, you can protect the VBA Project. In the Visual Basic Editor, click the Tools menu, and click VBAProject properties. (If you have named your project, you’ll see its name, instead of VBAProject.)
On the Protection tab,

  1. add a check mark to Lock Project for Viewing,
  2. enter and confirm a password,
  3. then click OK.

And remember what the password is, so you’ll be able to open the project later!