Unprotecting Worksheets

[Storm]

Hello.

I have a file that has multiple worksheets, each are protected (locked) with
a similar password for each worksheet. Is there a way to unprotect all
worksheets all at once without having to click on each sheet and unprotect it
that way? Is there a Macro? Or something?

Thank you!
Storm

 

[Jim Thomlinson]

That fact that the passwords are different makes things a bit trickier...
Give this a try... You will need to copy the one line indicated to accomodate
all of the passwords that exist in the spreadsheet...

Sub UnprotectAll()
Call UnprotectSheets("Password1") 'Copy this
Call UnprotectSheets("Password2")
Call UnprotectSheets("Password3")
End Sub

Sub UnprotectSheets(ByVal strPassword As String)
Dim wks As Worksheet

On Error Resume Next
For Each wks In Worksheets
wks.Unprotect Password:=strPassword
Next wks
On Error GoTo 0

End Sub

 

[Storm]

Hello Jim,

Thank you for your response. Actually, we have only 1 password for each
worksheet. By the way, I'm not so familiar with creating macros thru VB
script but I will try. All I need to do is copy paste your script below but
since I have only 1 password for each worksheet, I just need one line. By
the way, how is this macro executed? I'm so sorry if this sounds like a
stupid question. Does it execute upon opening the spreadsheet?

Thank you again Jim,
Storm

 

[Jim Thomlinson]

I am lost. One workbook contains many worksheets. Each worksheet can have a
different password associated with it. Do each of your sheets have different
passwords, or do you use the same password on each sheet? In any case if you
only have one password then you can just use the one line. If you have
multiple passwords then you can use multiple lines...

To call the procudure do the following (I am assuming that you would like to
have a button).

Open the VBE (Alt+F11) -> Select Insert|Module -> A code module will be
added to your project. Paste in the code that I provided. Go back to XL. On a
sheet copy a command button from the Forms Toolbar to your sheet. You will be
prompted to assign a macro. Select UnprotectAll.