how to check if a sheet is protected

[syaronc]

Hi,
I have a workbook with data I need to hide from some users (money...) and I am trying to create a macro that when I exit the workbook some columns while "Hide" and the sheet will be password protected. I have managed to do all but I don't know how to check if the sheet is protected, because if I try to run my macro when the sheet is protected I get an error massage
Please help...

 

[Smitty]

Welcome to the Board!

Why not just Unprotect it,do your thing and reprotect it?

<font face=Tahoma>    ActiveSheet.Unprotect Password:="password"
    <SPAN style="color:#007F00">'   Do your thing here</SPAN>
    ActiveSheet.Protect Password:="password"
</FONT>

Hope that helps,

Smitty

 

[rotarinn]

Hi,
you could also protect the sheet so that it is only protected in the user interface but still allows your macros or code to work with it.

     ActiveSheet.Protect Password:="myPass", UserInterfaceOnly:=True

 

[DRJ]

I think Smitty and Rotarinn have you covered for what you want, but here is the answer to your actual question.

MsgBox Sheets("Sheet1").ProtectContents

or

x = Sheets("Sheet1").ProtectContents

This will return True or False for if the sheet is protected or not.

 

[Tom Urtis]

This further to your actual question:

Sub Test1()
If ActiveSheet.ProtectContents = True Then
MsgBox "Protected"
Else
MsgBox "Not protected"
End If
End Sub


or this function and MsgBox from Goblin:

Public Function IsSheetProtected(Optional cell As Range, _
Optional volatile As Boolean = True) As String
Dim sh As Worksheet
Application.volatile volatile 'Updated on every recalc if true
If cell Is Nothing Then
Set sh = ActiveSheet
Else
Set sh = cell.Parent
End If
IsSheetProtected = "Unprotected"
If sh.ProtectContents = True Then
sh.Unprotect
If sh.ProtectContents = True Then
IsSheetProtected = "Password protected"
Exit Function
End If
sh.Protect
IsSheetProtected = "Protected"
End If
End Function

Sub Test2()
MsgBox IsSheetProtected
End Sub

 

[DRJ]

Just nitpicking Tom because I can

Sub Test1() 

If ActiveSheet.ProtectContents Then 
    MsgBox "Protected" 
    Else 
    MsgBox "Not protected" 
End If 

End Sub

 

[Tom Urtis]

So where's the nitpicking part? You lost me.

 

[DRJ]

I just changed

If ActiveSheet.ProtectContents = True Then

To

If ActiveSheet.ProtectContents Then

Since the = True is redundant

 

[Tom Urtis]

Well then you are doing the opposite of nitpicking, which is cutting a corner. It's a better practice to go the distance and not cut corners by assuming that a default is going to evaluate to True in all versions (including future versions) in all cases. As new versions of Office apps are released, MS also releases a new version of the object model, which you can see for yourself in the Available References pane. In the context of other macros and code going on among different versions using your macros, why take the chance and not specify something to be True even if it "should be". It adds no overhead weight to the file and is easier to rule out when a bug does occur. Your call, but it's an unnecessary risk in my opinion.

 

[syaronc]

Thanks all,
Your help is very appreciated!!!! it worked!!

Yaron.