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

Thanks

On Feb 17, 9:54 pm, Jim <sylv1...@yahoo.com> wrote:
> 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

On Feb 17, 5:54 am, Jim <sylv1...@yahoo.com> wrote:
> 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

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.

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

--

Dave Peterson

On Sun, 17 Feb 2008 05:54:23 -0800 (PST), SteveM <(E-Mail Removed)>
wrote:

>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).

On Feb 17, 10:17 am, Don Wiss <donwiss@no_spam.com> wrote:
> On Sun, 17 Feb 2008 05:54:23 -0800 (PST), SteveM <sbm...@vzavenue.net>
> wrote:
>
> >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).

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