VBA Loop Protect sheets with password only works on first 4 sheets

[Roy_Excel_Island_Apps]

Hi guys!

So I have this code to loop through all sheets and protect them with a public const "pw":

Public Sub ProtectStructure()

ThisWorkbook.Protect pw, True, False

Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
Debug.Print sht.Name
sht.Protect pw, , , , True, , , , , , , , , True, True, True

Next sht

End Sub

After running this code all sheets are protected, but only the first 4 sheets are protected WITH the password!? What's the issue here? Workbook corrupt? I can't find a reason why the code would stop using the password in the protection after using is 4 times... Anyone who has an idea?

Regards, Roy

 

[Fluff]

That works for me, do you have any other code that could be unprotecting some sheets?

 

[Roy_Excel_Island_Apps]

That works for me, do you have any other code that could be unprotecting some sheets?

Hi Fluff, good way of thinking, but no other code in there that could relate.
I found a solution in meantime:

1. Go to the sheets that are protected without password.
2. Unprotect it (no pop-up with password-this shows up because this is the problem).
3. Protect is manually with a password.
4. Do the same for all sheets.
5. Run the code again.
6. You'll see that the sheets are protected with the password from the macro!

Protecting them manually with a password seems to bring back the possibility to protect the sheet with a password via the macro. Magic!

 

[Fluff]

Glad you sorted it & thanks for the feedback.