Unprotect All Excel Sheets

from blog.contextures.com

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
Else
   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!

_____________