MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protecting & Unprotecting worksheet

Posted by kh on January 07, 2001 11:21 PM


I've got a simple macro that protects a few sets the worksheet protection on and off using the

sub protecton()

On Error Resume Next
ActiveSheet.Unprotect password:="pass"

end sub

I will call this in another procedure if the worksheet is protected to make some amendments to certain hidden cells. However the problem is that the protect and unprotect function is not always working that is I have notice that sometimes when unprotecting the worksheet the worksheet is still in protected mode. Can anyone help.


Posted by Dave Hawley on January 08, 2001 2:28 AM


Hi kh

There is nothing wrong with your code providing the sheet you want to unprotect is the Activesheet when it is Called. This is probaly where the problem lies.
A very good habit to get into when using VBA in Excel is to ALWAYS refer to the Worksheets using their CodeName. This way you will always be refering the sheet you want, regardless of it's Tab Name or Position. you can see your sheets code name in the project explorer (Ctrl+R) and are always like: Sheet1, Sheet2 etc. The other great thing about using this method is that by placing a .(period) after it, Excel will list all properties and Even objects you have on the Sheet.

Hope this helps, if not just yell


  • OzGrid Business Applications