I'm trying to indicate in cell E1 whether protection for my worksheet is on or off.
I tried this in CF
but don't find it responds after protecting / unprotecting the worksheet.
=CELL("protect",E1)=0
I wondered is some event VBA could provide some simple text and a change of colour in E1 to show worksheet protection status.
Can someone suggest some VBA to satisfy this?
Grateful for any advice.
Using Excel , Word and Access 2003 - For the whole of 2024
Like
If Sheets("sheet1").ProtectContents Then 'protected Else ' not protected End If
Unfortunately, this does not do what you think/thought it does. It is checking if the cell is locked or not. It is NOT checking if the sheet is protected."protect"
The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.
Note: This value is not supported in Excel for the web, Excel Mobile, and Excel Starter.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks guys. For clarity , I'm not trying to protect / unprotect the worksheet , only to indicate in a given cell whether it's on or off.
I'm not sure it's possible , but wondered if someone knows a way. The cell might change colour as the protection changes.
Jindon - thanks for your code , but I'm not sure how to apply your suggestion. Perhaps you could clarify.
Please re-read post #3. The formula does not check sheet protection, it checks if a cell is locked. I know you are not trying to protect/unprotect the sheet.
Thanks TMS. No, the formula doesn't the job. I couldn't get it working with CF..
CDandVinyl,
If you want.
1) To a standard code module.
2) To Sheet code moduleFunction IsSheetProtected(r As Range) As Boolean Application.Volatile IsSheetProtected = r.Parent.ProtectContents End Function
3) Select A1 and CFPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Me.Calculate End Sub
Formula
=IssheetProtected(A1)
4) When you protect sheet, you need to allow both [Select locked Cells] & [Select unlocked Cells]
5) Each time you Lock/Unlock the sheet, you need to select any cell to recalculate the CF formula.
Thanks jindon. This works fine. I'm impressed!
BTW With the CF option i can use colour but can't toggle green 'Protection on' , red 'Protection Off' text in E1. Could this be achieved with VBA in the Sheet code module?
2 ways
1)
Simple way is to color the cell in Green.
So that the cell is always green when not protected and turns to red when protected via CF.
2) If you want to control in CF then add another one
formula
=NOT(IsSheetProtected(A1))
and format the cell in Green
Many Thanks - problem solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks