I have several workbooks that contain about 100 sheets each. Some sheets are
not protected while the rest are protected with one of three passwords. I'm
trying to write a macro that will allow me to protect all sheets and assign
them all a new password. So I want it to first check if the sheet is
protected. If not, protect and assign "newpass" as password. If protected,
try to unprotect with "pass1". If that doesn't work, try "pass2" and then
"pass3" if needed. Once the sheet is unprotected, reprotect it and assign
"newpass" as the password. Then move on to the next sheet and repeat.

The following code is what I've come up with so far:

Sub I_Hope_This_Works()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = False Then
ws.Protect (newpass)
Else: ws.Unprotect (pass1)
If ws.ProtectContents = True Then
ws.Unprotect (pass2)
If ws.ProtectContents = True Then
ws.Unprotect (pass3)
ws.Protect (newpass)
End If
ws.Protect (newpass)
End If
ws.Protect (newpass)
End If
Next ws
End Sub

I keep getting an error that just says "400". I've never written an Excel
macro before, so I don't have a clue if I'm doing this correctly. I plan to
save the macro in one workbook and run it on all the others. Once I have the
above code set correctly, I'd also like to add a message that would appear if
one of the three original passwords didn't work. I'd like that message to
show me the sheet name on which the macro failed. Any help would be
appreciated. Thanks!