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 |
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 |
All times are GMT +1. The time now is 08:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com