VBA - How to run procedure to unprotect excel sheet if there could be 3 different password option

[PaulskinX1]

Dear all,

I put together a VBA procedure that does the following:

* opens a bunch of excel files (separately not at once) from a specific folder (user indicates the folder)
* excel files are password protected so it inputs the password , opens the file , then unprotects a specific sheet (as sheets are also pass protected) and does some changes to the file.

My problem is that for the changes to be applied to each excel file the sheet has to be unprotected and there is unfortunately 3 different options for pass (dont ask me why). I know the pass options so i put it in my code as per below snippet:



However the procedure still stops if the first pass option is incorrect and I have to manually drag the execution line to the next valid pass for the procedure to continue.

Essentially my questions are:
* is the code set up above ok and
* is there a way of making VBA to attempt another password to unprotect sheet if the first pass is not the correct one (and a third attempt if the 2nd pass is also incorrect)?

Uff, hope the above makes sense

 

[Jimmypop]

Hi there and welcome to the forum...

I would not suggest doing it that way seeing as it might be considered "bad programming"

Try the below...

Option Explicit
Sub test()
    Dim ws As Worksheets
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        ActiveSheet.Unprotect "green2022"
        ActiveSheet.Unprotect "green2021"
        ActiveSheet.Unprotect "green2020"
    Next
End Sub

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA - How to run procedure to unprotect excel sheet if there could be 3 different password option - OzGrid Free Excel/VBA Help Forum

Dear all,I put together a VBA procedure that does the following:* opens a bunch of excel files (separately not at once) from a specific folder (user indicates the folder)* excel files are password protected so it inputs the password , opens the file ,…

forum.ozgrid.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[PaulskinX1]

Hi there and welcome to the forum...

I would not suggest doing it that way seeing as it might be considered "bad programming"

Try the below...

Option Explicit
Sub test()
    Dim ws As Worksheets
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        ActiveSheet.Unprotect "green2022"
        ActiveSheet.Unprotect "green2021"
        ActiveSheet.Unprotect "green2020"
    Next
End Sub

Thank you very much for this code - it pushed me in the right direction.

Perhaps i was not very clear in my question as I just needed 1 specific sheet to be unprotected.

However your code opened my eyes how things can be done - great help.

I ended up with the following (and it works a charm):

With ActiveSheet

On Error Resume Next

.Unprotect "green2022"

.Unprotect "green2021"

.Unprotect "green2020"

End With

 

[Jimmypop]

Glad to be of help and appreciate the feedback...