Cell to indicate protected sheet

[Jim]

Is there any way that a cell can be set up to indicate if a sheet is
protected or unprotected?

Thanks

 

[Ken Johnson]

Is there any way that a cell can be set up to indicate if a sheet is
protected or unprotected?

Thanks

Do you mean something like this?
A1 would have to be locked.

Public Sub CheckProtection()
On Error GoTo PROTECTED
Range("A1").Value = "Not Protected"
Exit Sub
PROTECTED: ActiveSheet.Unprotect: Range("A1").Value = "Protected"
ActiveSheet.Protect
End Sub

Ken Johnson

 

[SteveM]

Is there any way that a cell can be set up to indicate if a sheet is
protected or unprotected?

Thanks

Jim,

See ProtectContents and ProtectionMode in the Worksheets Object
Browser.

I think this should do it:

Public Function ProtectStatus() As String
Dim pStatus As Boolean

pStatus = ActiveSheet.Protect
If pStatus = True Then
ProtectStatus = "Protected"
Else
ProtectStatus = "Unprotected"
End If

End Function

Just select the cell you want to use as the indicator and then this
function from the User Defined list.

SteveM

 

[Dave Peterson]

If you're looking for a visual indicator, you may want to look at the toolbars.
Lots of the formatting icons are disabled when the sheet is protected.

 

[Don Wiss]

I think this should do it:

Public Function ProtectStatus() As String
Dim pStatus As Boolean

pStatus = ActiveSheet.Protect
If pStatus = True Then
ProtectStatus = "Protected"
Else
ProtectStatus = "Unprotected"
End If

End Function

Why all the unnecessary code? Why not?

Public Function ProtectStatus() As String

If ActiveSheet.Protect Then
ProtectStatus = "Protected"
Else
ProtectStatus = "Unprotected"
End If

End Function

And you never need code like "pStatus = True" as pStatus is already a
boolean.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[SteveM]

Why all the unnecessary code? Why not?

Public Function ProtectStatus() As String

If ActiveSheet.Protect Then
ProtectStatus = "Protected"
Else
ProtectStatus = "Unprotected"
End If

End Function

And you never need code like "pStatus = True" as pStatus is already a
boolean.

Don <www.donwiss.com> (e-mail link at home page bottom).

I suppose I could say that:

I wanted the logic to be clear to the OP. Or;
I just like the way "True" looks on a page. Or;
We haven't gotten that far yet in the Programming with Prussian
Efficiency course I'm taking. Or even;
I'm just a dope.

There may others. Take your pick.

SteveM