Changing a VBA applied password

[sparky2205]

Hi,
I have a spreadsheet that is write password protected for certain cells.
I assign a password via VBA to each worksheet.
I have the same password assigned to the project in the project explorer window.
This worked perfectly until I tried to change the password.
Now it keeps telling me the password is not correct when I open the file.
There appears to be a mismatch somewhere with passwords but I don't know where.
Is there somewhere else I need to change the password that I'm not aware of?
I have changed it in the code and in the project explorer window.

Many thanks.

 

[RoryA]

Which password are you trying to change?

 

[sparky2205]

Interesting development.
I tried to use the password to unlock the sheets manually but it didn't work.
So I used the old password and this does work.
So what's happening is:
I set a new password in my code and in the project explorer window.
Then I save the file, close it and reopen it.
I get a password mismatch error but the password I use to open the project explorer and the password in the vba code are the same.
When I unlock the worksheets manually I have to use the old password.
It looks like the old password is being held onto somewhere.
I want to change both passwords;
in the piece of code that sets the passsword for each sheet
in the project explorer window that hides my code
Having these passwords different has caused me problems in the past.

 

[RoryA]

What code are you using to set the passwords? Changing the VBAProject password in code is not trivial.

 

[sparky2205]

Hi Rory,
sorry I'm not making myself clear.
I'm not changing the project explorer password in vba.
I'm protecting all worksheets on the worksheet open event with the following code:

Private Sub Workbook_Open()
    Dim wSheetName As Worksheet
           
    For Each wSheetName In Worksheets
        wSheetName.Protect Password:="MyPassword", UserInterFaceOnly:=True, AllowFiltering:="True"
    Next wSheetName
    
End Sub

Then I manually set the project explorer password to match the password in the code.

Sounds pretty simple right? I thought so anyway.

 

[RoryA]

If your worksheets are currently protected with a different password, that code should cause an error.

 

[sparky2205]

Hi Rory,
I obviously did something wrong initially.
I went back to the start and did everything over and it works fine now.
Thanks for your time.