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

PaulskinX1

New Member
Joined
Mar 28, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • vba pass.PNG
    vba pass.PNG
    32.4 KB · Views: 21

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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...

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

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.
 
Upvote 0
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...

VBA Code:
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
 
Upvote 0
Glad to be of help and appreciate the feedback...
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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