Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking a sheet...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking a sheet...
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
locking a sheet | Excel Discussion (Misc queries) | |||
Locking A sheet | Excel Discussion (Misc queries) | |||
Locking Data Sheet | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
Locking a sheet?? | Excel Discussion (Misc queries) |