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,
- add a check mark to Lock Project for Viewing,
- enter and confirm a password,
- then click OK.
And remember what the password is, so you’ll be able to open the project later!