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 an Excel VBA macro, like the one shown below.
Excel Macro Code
Here is the code I use to unprotect all the sheets in a workbook, when they all have the same password.
Sub UnprotectAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="MyPassword" Next ws End Sub
Run Macro with Shortcut
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!
_____________
This concept is very interesting and I’ve been using variants of it for some time.
I have a similar macro for un-hiding all sheets in a dashboard workbook.
In that workbook, I have an “Interface” sheet which is the only one to be kept visible at most times.
For Each sht In ActiveWorkbook.Sheets
sht.Visible = True
Next
Note: This code assumes that the workbook structure is not password-protected.
If the structure *is* protected you would have to add a line to unprotect it before running this code.
As a complement to this macro, I also have an event-driven macro in each sheet’s code module, which is triggered on the Worksheet Deactivate event.
E.g. on moving away from a sheet named ‘Data’, the code for the sheet would be :
Private Sub Worksheet_Deactivate()
Sheets(“Data”).Visible = False
End Sub
If I don’t wish to trigger the worksheet hide macro, I simply click on the Design Mode button (which is setup on my QAT).
This helps to keep such sheets visible until such time that I want to ‘re-hide’ those sheets.
And finally, if I have lots of sheets visible after browsing through the workbook, I have a HideAllSheets macro.
This macro hides all the sheets in the workbook, except the “Interface” sheet.
For Each sht In ActiveWorkbook.Sheets
If sht.Name = “Interface” Then
sht.Visible = True
Else
sht.Visible = False
End If
Next sht
It seems that it would be simpler to just keep the code in a Personal.xls(b) and change Thisworkbook.worksheets to Activeworkbook.worksheets. This way there is no need to prevent users from accidentally running the unprotect procedure, and no need to protect the VBA module since they can’t access your Personal.xls(b) file.
I use the unprotect worksheets macro, however when I’m online training and I don’t want anyone to see my password how can I set the default characters to *** instead of the actual password?
Could you temporarily change your pw to ***** or 12345 and change back when done?