Password unprotect sheets - multi passwords.

[wmtsub]

I have a number of workbooks with different passwords.
Sometimes worksheets within a workbook.
I am not allowed to change them. So what I would like to do is loop thru all worksheets an try all the PW's
But I cannot get this to work.

Sub Un_Protect_All()
awsn = ActiveSheet.Name
For Each Sheet In Application.Worksheets
Sheet.Activate
ActiveSheet.Unprotect

ActiveSheet.Unprotect "1"
ActiveSheet.Unprotect "2"
ActiveSheet.Unprotect "3"​

Next
Worksheets(awsn).Select
End Sub

 

[gallen]

Re: Paswword unprotect sheets - multi passwords.

Why would you try all the passwords? Do you not know the password for each?

 

[wmtsub]

Re: Paswword unprotect sheets - multi passwords.

So I do not need to unlock each worksheet one at a time/

 

[gallen]

Re: Paswword unprotect sheets - multi passwords.

Yes but your code tries every password on every sheet almost like trying to work out the password.

Your code is confusing. No need to activate:

Sub Un_Protect_All()
    Dim Sheet As Worksheet
    awsn = ActiveSheet.Name
    For Each Sheet In Application.Worksheets
        'Sheet.Activate 'no need to activate. just use the sheet variable. Helps if you declare the sheet variable
        Sheet.Unprotect
        Sheet.Unprotect "1"
        Sheet.Unprotect "2"
        Sheet.Unprotect "3"
    Next
    Worksheets(awsn).Select
End Sub

However I'd be writing a sub to unprotect all sheets something like this, ensuring the constants are at the top of the code module before any subs are written:

'constants to denote password for each sheet

Const sSheet1Pass As String = "Password1"
Const sSheet2Pass As String = "Password2"
Const sSheet3Pass As String = "Password3"
Sub Un_Protect_All()
    Dim Sheet As Worksheet
    Dim aswn As String
    
    'store active sheet name
    awsn = ActiveSheet.Name
    
    'loop through all sheets
    For Each Sheet In Application.Worksheets
        Select Case Sheet.Name
            Case "Sheet1"
                Sheet.Unprotect sSheet1Pass
            Case "Sheet2"
                Sheet.Unprotect sSheet2Pass
            Case "Sheet3"
                Sheet.Unprotect sSheet3Pass
            
        End Select
    Next
    Worksheets(awsn).Select
End Sub

 

[wmtsub]

Re: Paswword unprotect sheets - multi passwords.

What if I am unsure as to how may sheets are in the book? I keep getting sent books from co-workers to work on. each book has various sheets. Some protected, some not.
I was trying to set a macro to remember all the supplied PW and let me run 1 macro to unlock it

 

[philwojo]

Re: Paswword unprotect sheets - multi passwords.

Why not ask your co-workers to send the passwords when they send you the sheet, seems easier than trying to figure them out each time.

 

[wmtsub]

Re: Paswword unprotect sheets - multi passwords.

Tied, thier idiots and forget or mispell. So I want an easy button....

 

[gallen]

Re: Paswword unprotect sheets - multi passwords.

If you are supplied with the passwords then that's 90% of the work. My method should be no more difficult than your method, as both rely on the code having all possible passwords, for all possible sheets.

Another option would be to have a separate Workbook to hold the passwords, then you could use a vlookup to retrieve the password, or search for it in code.

Looking at your code, you say in your opening post that you can't get it to work. I'm guessing it's because you have sheet.inprotect first. That will need to be first. You will also need to ignore any errors as each wrong attempt will cause an issue:

Sub Un_Protect_All()
    Dim Sheet As Worksheet
    Dim awsn As String
    awsn = ActiveSheet.Name
    For Each Sheet In Application.Worksheets
[COLOR=#ff0000]        On Error Resume Next 'ignore any errors[/COLOR]
        'Sheet.Activate 'no need to activate. just use the sheet variable. Helps if you declare the sheet variable
        Sheet.Unprotect "1"
        Sheet.Unprotect "2"
        Sheet.Unprotect "3"
[COLOR=#ff0000]        Sheet.Unprotect 'do this last[/COLOR]
    Next
    on Error Goto 0 'don't ignore errors
    Worksheets(awsn).Select
End Sub