Activesheet protection

R

Rob

Excel 2000
Hi,

I have a macro that I want to run only if the worksheet is protected, I've
tried code like: If ActiveSheet.Protection = False Then GoTo ResetMsg

ResteMgs:
MsgBox "Can't run macro!"
Exit Sub

The above doesn't work but I can't figure it out, any pointers most welcome.

Rob
 
B

Bob Phillips

Hi Rob,

There are 3 protection properties. You can cater for all with

With ActiveSheet
If .ProtectContents = False Or _
.ProtectDrawingObjects = False Or _
.ProtectionMode = False Then
GoTo resetmsg
End If
End With

but you will probably only need ProtectContents.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Steve Garman

Try using:

If ActiveSheet.ProtectionMode = False Then GoTo ResetMsg

'Also, you need to spell "ResetMsg" consistently
 
P

Patrick Molloy

TIP: assign a variable as worksheet then set the variable to the
sheet...that way Intellisense works and you'll get the enumerated values
too...

Sub Test()

Dim ws As Worksheet
Set ws = ActiveSheet
If ws.ProtectionMode = False Then
MsgBox "No Protection"
ElseIf ws.Protection.AllowDeletingRows = True Then
MsgBox "Delete rows off"
End If

End Sub
 
R

Rob

Thanks to you all, great solutions which I experiment with and go for the
most suited.

Thanks, Rob
 
T

Tom Ogilvy

I am not sure why anyone is suggesting ProtectionMode as the single property
to check. This is only true if the UserInterfaceOnly property of the
protect method has been used and this must be set using code. (at least
this is the way it works in xl2000 and xl97)

Bob Phillips is the closest, but there is one more property to check and I
would see the sense of the check being slightly different

With Worksheets("sheet1")
If .ProtectContents = False and _
.ProtectDrawingObjects = False and _
.ProtectScenarios = False and _
.ProtectionMode = False Then
GoTo resetmsg
End If
End With


exit sub
resetmsg :
msgbox "Sheet isn't protected"

I would suspect, however, that your macro might only be interested in the
Protectcontents property, so that might be all you need to check.
 
R

Rob

Tom,

Thanks for the bit on ProtectionMode, it was the one element that seemed to
not work quite like the others. Also, AND is what I needed.

Rob
 
D

Dave Peterson

I used xl2002:

I protected a worksheet with userinterfaceonly :=true. I unprotected the sheet
and .protectionmode still returned True.

I don't think I've ever worried about resetting that userinterfaceonly when I've
unprotected a sheet.

Option Explicit
Sub testme()
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
.Protect userinterfaceonly:=True
MsgBox .ProtectionMode
.Unprotect
MsgBox .ProtectionMode
End With
End Sub

Unless the OP is very careful, it might not be useful to include in that in the
If statement.
 
T

Tom Ogilvy

It doesn't set itself. But, I agree that there is no need to check
..ProtectionMode.
 
Top