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

 

[stefankemp]

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

If the sheet is password protected and you run the Test2 procedure, the user will be prompted for the password to unprotect the sheet. Is there any way to avoid this?

 

[baldmosher]

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.

I agree. Also easier to follow your own trail when you're learning VBA if everything in your code is specified very specifically

If the sheet is password protected and you run the Test2 procedure, the user will be prompted for the password to unprotect the sheet. Is there any way to avoid this?

EDIT: this was my original answer below but I've just proof-read the code and logically it won't do what you need. It needs a bit of a re-think.

You'd have to pass the sheet password as an additional variable in the function, as follows:

Public Function IsSheetProtected(Optional cell As Range, _
Optional volatile As Boolean = True[b], Optional pw As String[/b]) 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 [b]Password:=pw[/b]
If sh.ProtectContents = True Then
IsSheetProtected = "Password protected"
Exit Function
End If
sh.Protect [b]Password:=pw[/b]
IsSheetProtected = "Protected"
End If
End Function

EDIT: maybe a combination of Application.DisplayAlerts = False and On Error might solve it....

 

[pcc]

I think an important point has been overlooked here, for the original poster...

Excel's security is not good (in fact is's pretty poor).
Even with passwords and hidden sheets etc, anyone with a modest knowledge of how Excel works will be able to get at the 'protected' data.

My advice is: if you don't want people to see certain data, then don't put it in the workbook in the first place.

 

[baldmosher]

I think an important point has been overlooked here, for the original poster...

Excel's security is not good (in fact is's pretty poor).
Even with passwords and hidden sheets etc, anyone with a modest knowledge of how Excel works will be able to get at the 'protected' data.

My advice is: if you don't want people to see certain data, then don't put it in the workbook in the first place.

Important indeed but if the OP password protects his VBA Project won't that make it "uncrackable"?

I stand to be corrected as I've only just started thinking outside the Project box, as it were, and I'm not a hacker

 

[pcc]

Possibly, I'm not sure.
I am still using Excel 2000 and it's a doddle to get at hidden and protected data if you know how.
Maybe later versions are more secure.
I still would not put data in if it was not meant to be seen by others...
you have been warned

 

[baldmosher]

If you password protect the Project it makes all the VBA code inaccessible (unless there's a back door I'm not aware of)

You can then very easily write code to make specific sheets permanently invisible to anyone without access to the Project, e.g. Sheets.Visible = xlVeryHidden which removes it from the list of sheets in Tools > Sheets > Unhide

I have one wb that demands a master password before unhiding all sheets.

This is all from 2003 by the way, may be different in 2000.

 

[pcc]

OK if you think it's secure then you go with it.
I find it a trivial task to get at any data in any sheet even if the sheets are xlveryhidden, protected, the workbook structure is protected, the vb is locked down etc etc. However, forum rules prevent me from divulging how to do this. Those that know, know, those that don't, don't know. Let's leave it at that.
I still maintain that hidden data is not hidden from anyone who is determined enough.
Please note that I'm not trying to be a smartarse here, or 'get one over' on anyone! I'm just trying to be helpful in pointing out to the original poster that Excel is not secure..
Cheers

 

[pcc]

Just checked on Excel 2007, and it's the same story - I can get at the data.

Perhaps an MVP would like to comment, although I'm sure Excel's poor security has been mentioned many times in the past.
Regards

 

[baldmosher]

So there is a back door and I stand corrected

I must admit I don't put data in a workbook that shouldn't be seen by the recipient. Not worth my efforts concealing it, for the very reasons you gave. Easier to control via external data source.