VBA code to protect all worksheets with password when workbook is opened

[dredre609]

Hi,

I am using the following code to protect all the worksheets in my workbook when the workbook is opened.

Please Login or Register  to view this content.

It works great, but only if I stick to the same password. If I try to change the password I get a Run-time error '1004' saying the password I supplied is not correct. However, the new password which I've added to the code does work when I manually unprotect the sheets with the Unprotect Sheet function. I've tried deleting the code completey, unprotecting each worksheet, then saving and reopening and adding the code back and I still get the error.

Does anyone know what might be happening or how to get rid of the Run-time error?

Please let me know.

Best,

Andrej

[cytop]

Stab in the dark here - but you have Unprotected it using the OLD password before trying to add the new one...?

[Logit]

.
.
The following works fine here. Very similar to your macro.

#1 Once opening the workbook, individually unprotect all sheets with the old password.

#2 Change the password in the macro to something different.

#3 Go back through the workbook and manually change the password for each sheet.

#4 Then save and close.

When reopening the workbook, the changed password worked ... here.

????

Please Login or Register  to view this content.

I believe this is the same thing cytop is saying ??

[dredre609]

Hi,

I have tried both suggestions. I unprotected all the sheets with the old password, then replaced the password in the VBA code, then re-locked all the sheets with the new password and then saved, closed, and re-opened and still got the error.

When I tried the suggested code there is no error message but it does not lock the worksheets when the workbook is opened. I put the code in the ThisWorkbook object, is this correct?

[Logit]

.
.
The macro code would go into a Routine Module, not in the ThisWorkbook module.

The code that should be in ThisWorkbook module would be :

Please Login or Register  to view this content.

[xlnitwit]

Hi,

Are you sure you don't have any hidden sheets in the workbook, and that the password exactly matched the one you entered manually?

[dredre609]

It was a hidden sheet I forgot about! My original code works fine now. Thanks everyone for the help.