'Unlock' Code is Unprotecting Sheet

Hi,

On my AFRsInput sheet there are 2 yellow shapes upper left. One for 'Lock' which locks the cells D5:D19 and L4:L7 and fills them with gray. This works like it should.

The other yellow shape 'Unlock' asks for password ('clerk') to unlock those cells which it does. But it also unprotects the sheet which is bad.

Can someone please make it so the sheet remains protected ?

Thank you,
Fred

Can you post the code that runs off the yellow shape? This is going to be a quick one and.....at 4.30pm after a long day of listening to the fella at the desk next to mine sniff, cough and sneeze his way through today I really can't be bothered to download your attachment.

Yes sorry:

Please Login or Register  to view this content.

gonna need 'FormUnlock' and 'FormLock' as well pls.

Maybe all of this:

Please Login or Register  to view this content.

Just in case I decide to give up for the day and go home, what's happening is your code is successfully editing the cells but not protecting the sheet once it's finished. Either, the method you're using to detect if the sheet should be protected or not is returning an incorrect value and your code to lock it all again isn't triggering.....or your code to protect the sheet is having an error.

Please Login or Register  to view this content.

looks fine to me. Which suggests the code to protect the sheet once your macro has finished unlocking cells isn't triggering. What's interesting is the switch 'useinterrfaceonly=true' should remove the need to unlock the sheet before making changes.

I'm not sure sorry this is why I ask for help. Thanks if you have to leave.

Step through your code (click in the margin on the first line to set a break point - press F8 to step though line by line). What value is the 'shtprotected = Sheet1.ProtectContents' returning?

If it's returning anything but 'True' then we've found the problem.

it keeps cycling through the top part of the ClerkUnlock code

Ok, just admitted defeat and downloaded your attachment. The shtprotected part is definitely returning 'False' as the value, so your protect code will never trigger. That's the problem. You'll need to come up with another method of deciding whether or not a sheet needs to be protected.

However, as I mentioned earlier, the 'UserInterfaceOnly' switch you're using when you protect a sheet negates the need to unprotect that sheet again, VBA code will still be allowed to make changes despite the sheet being protected. The only caveat to using it is that Excel 'forgets' the switch every time you close the workbook, so when you load it again tomorrow none of your protected sheets will have the userinterfaceonly switch set.....until you unprotect them and re-protect them again with the switch.

The usual method I use to get around this is in the workbook_open event I put code to unprotect and reprotect every sheet.....when the workbook opens (clue's in the name of the event). Once you've got userinterfaceonly setup consistently there'll no longer be any need to unprotect a sheet when you want to unlock specific cells.

And on that note I'm giving up for the day. Good luck and if you're still having trouble tomorrow I'll jump in and re-write what you've got there.

I appreciate the extensive help you are providing but it is a bit advanced for my knowledge thus far.

Thanks BellyGas I will keep trying. Enjoy your evening.

I came up with a possible solution:

Please Login or Register  to view this content.

Not sure what the FormLock and FormUnlock do but I made them comments and the sheet remains protected during the Lock and Unlock.