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.
Please note: I reserve the right to delete comments that are offensive or off-topic.