Locking a sheet...

J

Jay Gustafson

Hello,

Is there some kind of code that I code use, that if the sheet is locked/protected then cell A1 will say, "This sheet is protected", if it's not then I want it to say, "This sheet is not protected".

I'd appreciate any help with this. I have no experience in coding other than copying & pasting it in.

Jay
 
K

K Dales

I wrote a formula that can check for the protection status
of a worksheet (where xlRange is any cell or range on the
sheet):

Public Function Protected(xlRange) As Byte

Protected = xlRange.Parent.ProtectContents * -4 _
+ xlRange.Parent.ProtectDrawingObjects * -2 _
+ xlRange.Parent.ProtectScenarios * -1

End Function

Returned value is non-zero if any protection option is
active; 4 for contents, 2 for objects, 1 for scenarios,
and the sum of the individual values for any combination
of these. So you could use this formula, say in cell A1:

=IF(Protected(A2)>0,"This Sheet is Protected","This Sheet
is Not Protected")

BUT: changing the protection status of the sheet is not
enough to trigger a recalculation of this formula. So
this will work, but you may need to come up with a
creative way to trigger a recalculation of the cell. Have
not yet found a good solution - anyone out there have any
ideas?
-----Original Message-----
Hello,

Is there some kind of code that I code use, that if the
Click to expand...
sheet is locked/protected then cell A1 will say, "This
sheet is protected", if it's not then I want it to
say, "This sheet is not protected".
I'd appreciate any help with this. I have no experience
Click to expand...
in coding other than copying & pasting it in.
 
Top