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

UnprotectAllSheets01

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.)

VBAProtect01

On the Protection tab,

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

VBAProtect02

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

_____________

You may also like...

4 Responses

  1. Khushnood Viccaji says:

    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

  2. Reuvain says:

    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.

  3. Jane says:

    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?

  4. Candy Gonzalez says:

    Could you temporarily change your pw to ***** or 12345 and change back when done?

Leave a Reply to Candy Gonzalez Cancel reply

Your email address will not be published. Required fields are marked *