Password unprotect sheets - multi passwords.

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Re: Paswword unprotect sheets - multi passwords.

Why would you try all the passwords? Do you not know the password for each?
 
Upvote 0
Re: Paswword unprotect sheets - multi passwords.

So I do not need to unlock each worksheet one at a time/
 
Upvote 0
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:

Code:
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:

Code:
'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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Re: Paswword unprotect sheets - multi passwords.

Tied, thier idiots and forget or mispell. So I want an easy button....
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top