Having cell show if worksheet is protected or unprotected

[bamaisgreat]

As im sure you can tell in the code below that when I select cell E1 in sheet 2 it calls to run a macro to toggle protect and unprotect of sheet 2. I was wandering if there could be something added to the LockUnlock() code so cell E1 would actually show the text "PROTECTED" OR UNPROTECTED" depending on the worksheets status.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Address = "$E$1" Then
        Call LockUnlock
    End If
End Sub

Sub LockUnlock()
If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect ""
Else
ActiveSheet.Protect ""
End If
End Sub

 

[Joe4]

Should be pretty straightforward:

Sub LockUnlock()
    If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect ""
        Range("E1").Value = "UNPROTECTED"
    Else
        Range("E1").Value = "PROTECTED"
        ActiveSheet.Protect ""
    End If
End Sub

 

[bamaisgreat]

Thanks Joe worked like a charm. Could there be something added to make the cell blue and font white when protected and cell green and font black when Unprotected?

 

[Joe4]

Thanks Joe worked like a charm. Could there be something added to make the cell blue and font white when protected and cell green and font black when Unprotected?

Sure. But I will let you do that part yourself. It will be a great learning/teaching exercise.

For real simple snippets of code (like changing colors, etc), simply turn on your Macro Recorder and record yourself manually making the changes you want to see.
Then stop the Macro Recorder and view the code you just recorded. It will contain the commands you need to put in your code. So just copy and insert those parts over into your code.
The Macro Recorder is a great tool for this sort of thing.

One caveat: The Macro Recorder is very literal, and records every cell selection. So if you were recording yourself setting cell A4 to "Dog", you would have a section of code that looks something like this:

    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Dog"

In VBA code, it usually isn't necessary to select a range first in order to work with it. So whenever you have one line of code that sends in "Select" and the next begins with "Selection" or "ActiveCell", you can usually combine them together like this:

    Range("A4").FormulaR1C1 = "Dog"

 

[bamaisgreat]

Awesome. Thanks for the help and tip.

 

[Joe4]

You are welcome!