When you are using sheet passwords in Excel it can be handy to have a macro to unprotect all the sheets in one step. This can make maintenance easier.
Using a macro that re-applies the password can save time. This does require you have the password entered in the VBA code, which also means someone can look at the code and find out the password.

It is advisable that you password protect the VBA code to stop unauthorised viewing of the VBA code. I have written a blog post that provides instructions on password protecting your VBA code – click here to view the blog post. Remember to note down your VBA password.

The two macros below can speed up protecting and unprotecting all the sheets in a file. Note: passwords in Excel are not bullet-proof and can be hacked. They will stop most people from doing what they shouldn’t.

Both macros below use the word password as the password. You need to change “password” in the code to the password you require.

The new password needs to be placed within the quotation marks.

Sub Protect_All_Sheets()
'applies a password to all sheets in the workbook
Dim strPW As String
Dim ws As Worksheet
strPW = "password"
  For Each ws In Worksheets
    ws.Protect Password:=strPW
  Next ws
Set ws = Nothing
End Sub

Sub Unprotect_All_Sheets()
'unprotects all sheets in a workbook
Dim strPW As String
Dim ws As Worksheet
strPW = "password"
  For Each ws In Worksheets
    ws.Unprotect Password:=strPW
  Next ws
Set ws = Nothing
End Sub

Click here to see how you can run a Macro off a Quick Access Toolbar icon.