Macros to protect and unprotect sheets (Without a password)

Hello All,

I have the following code after recording two macros which I hoped would protect a series of sheets and then unprotect the same sheets however it doesn't work with a run time error 9 being displayed. I'm still learning about Macros and would appreciate help with this.

Please Login or Register  to view this content.

I should add that the workbook contains 14 tabs named as above in the code and I created the macro using the record Macro option.

Any help would be be as ever appreciated.

Thanks & best regards,

David

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Correct this and I'm sure someone will be along to help.

Regards

Originally Posted by David_S_Walker

Couldn't see a # character but I have removed the font colour if that is what you meant

On this occasion I have changed your post for you. When you add code in the future please highlight the code in the message box and then click the little # sign you see along the top of the message box. When editing a previous post do the same but choose advanced edit first.

Regards

David,

The macro recorder is excellent for capturing code when learning, but as you've found it is very wasteful since it always includes every parameter that's available and explicitly defines them to be False, which is generally what you want in any case and therefore this is redundant.

It also uses the .Select and .Activate instructions which are very rarely needed and only slow the code down since VBA has to keep jumping backwards and forwards from VBA to the Excel App and back again.

A far more efficient way is as follows

Please Login or Register  to view this content.

Regards

Can anyone help further with this as the protect and unprotect works fine but when I protect I only want users to select unlocked cells. What extra code would be needed in addition to that which Richard supplied.

I have played around a bit but am getting nowhere. All the 14 tabs are correctly set with cells ticked or unticked as locked/unlocked

Thanks for any help on this

Best regards,

David