Solved: Display Protection Status

[sbrown]

Is there any way to check to see if a spreadsheet is protected and return a particular value to a cell? For instance, if the sheet is protected, cell A1 would equal "Protected", else it would equal "Unprotected"?

Thanks in advance!
Steve

[mvidas]

Hi Steve,

You could create your own function for this, something like:[vba]Function IsProtected(Optional ByVal SheetName As String) As Boolean
Application.Volatile
Dim WS As Worksheet
On Error Resume Next
Set WS = Sheets(SheetName)
On Error GoTo 0
If WS Is Nothing Then Set WS = Application.Caller.Worksheet
IsProtected = (WS.ProtectContents Or WS.ProtectDrawingObjects Or _
WS.ProtectScenarios Or WS.ProtectionMode)
End Function[/vba]
To determine the current sheet's protection, you would just use
=ISPROTECTED()
or
=IF(ISPROTECTED(),"Protected","Unprotected")

If you want to determine another sheet's protection status, include that sheet's name in the formula, like
=ISPROTECTED("Sheet2")

It will default to the current sheet if no sheet name is specified, or an invalid sheet name is specified.

If the sheet being tested is unprotected and you then protect it, you will need to hit F9 to calculate it to update the return value.

Matt

[sbrown]

PERFECT!

I then wrote a macro to turn the protection on / off which includes a recalculate. Just what the doctor ordered!

THANKS!

[mvidas]

Glad to help!
I decided to make it a little more robust, you can now send this a worksheet object or a worksheet name, in case you wanted to use it from a macro as well. So you can either call it as described above or from vba as IsProtected(ActiveSheet) or IsProtected("Sheet3"), etc:[vba]Function IsProtected(Optional ByVal TheSheet As Variant) As Boolean
Application.Volatile
Dim WS As Worksheet
On Error Resume Next
If TypeName(TheSheet) = "Worksheet" Then 'if worksheet object was passed
Set WS = TheSheet
Else 'in case string was passed
Set WS = Sheets(CStr(TheSheet))
End If
On Error GoTo 0
If WS Is Nothing Then
If TypeName(Application.Caller) = "Range" Then
Set WS = Application.Caller.Worksheet
Else
Set WS = ActiveSheet
End If
End If
IsProtected = (WS.ProtectContents Or WS.ProtectDrawingObjects Or _
WS.ProtectScenarios Or WS.ProtectionMode)
End Function[/vba]Matt

[ALe]

Hey Matt, what's caller?

[mvidas]

I could describe it myself, but the help does it so nicely:

Caller Property

Returns information about how Visual Basic was called (for more information, see the Remarks section).
Syntax
expression.Caller(Index)
expression Required. An expression that returns an Application object.
Index Optional Variant. An index to the array. This argument is used only when the property returns an array (for more information, see the Remarks section).
Remarks
This property returns information about how Visual Basic was called, as shown in the following table.
Caller -
Return value

A custom function entered in a single cell -
A Range object specifying that cell

A custom function that is part of an array formula in a range of cells -
A Range object specifying that range of cells

An Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro -
The name of the document as text

A macro set by either the OnDoubleClick or OnEntry property -
The name of the chart object identifier or cell reference (if applicable) to which the macro applies

The Macro dialog box (Tools menu), or any caller not described above -
The #REF! error value


I have it the way I do to determine if called by UDF (in which case set it to that worksheet if nothing else set), or the activesheet if called any other way.
Matt

[ALe]

thank you. My question has been related to the fact that I've just changed my PC and VBA guide is not installed.


thank you!

[mvidas]

Not a problem! I'm just happy my help is working again (wasn't for a couple months)