VBA Issue with Worksheet Protection Passwords

[grahamd_41]

Hi all, I'm new to the forum although have been helped by it on many occasions.

I have come across a problem while trying to use VBA to protect my worksheet and was wondering if anyone else has had a similar issue.

Essentially I am adding two levels of protection; one level for the end user of the spread sheet and another level for my colleagues to modify the data without deleting or moving formulas/cells.
There is probably other ways of achieving what I have done, but after much Google and my limited knowledge of coding this is what I came up with.

Using a loop activated by a button press I UNLOCK editing capabilities:

• Request a password from the user (pwd)
• Loop through my worksheets
• Unprotect worksheet with pwd
• Unlock some cells
• Protect without any password

On a second press of the button I LOCK editing capabilities

• Loop through my worksheets
• Unprotect the worksheet without password
• Lock some cells
• Protect with pwd

pwd is stored hidden in my workbook


With many breakpoints and step by step processing I found the problem I'm experiencing is at the exact point when trying to protect the worksheet (in BOLD), Excel throws up the "Unprotect Sheet" dialog box and asks for a password.

Why when trying to protect a sheet does it ask for an unprotect password?

Any ideas anyone? I've tried all sorts but the only cure I have found is using a password at this point. I don't want to have to do this as I want my colleagues to have the ability when required to gain full access to the spread sheet without needing a password.

 

[Andrew Poulsom]

Does it only happen if IPwd is not the correct password?

 

[grahamd_41]

If Ipwd is incorrect then the error handler notifies that is the case and exits so it doesn't get to that point.

 

[grahamd_41]

If Ipwd is incorrect then the error handler notifies that is the case and exits so it doesn't get to that point.

Sorry, should explain that it goes to error handler at the point it attempts to unlock the worksheet (at the beginning of sub "unprotect")

 

[grahamd_41]

So it seems to me I may have to proceed with entering a password in for unprotecting the sheets while in editing mode.
Thanks for trying to help with my problem and the tips!