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

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)

"Rob" wrote in message
...
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

Try using:

If ActiveSheet.ProtectionMode = False Then GoTo ResetMsg

'Also, you need to spell "ResetMsg" consistently

Rob wrote:
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

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

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Rob" wrote in message
...
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

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

Thanks, Rob

"Patrick Molloy" wrote in message
...
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

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Rob" wrote in message
...
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

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.


--
Regards,
Tom Ogilvy


"Rob" wrote in message
...
Thanks to you all, great solutions which I experiment with and go for the
most suited.

Thanks, Rob

"Patrick Molloy" wrote in message
...
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

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Rob" wrote in message
...
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

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
"Tom Ogilvy" wrote in message
...
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.


--
Regards,
Tom Ogilvy


"Rob" wrote in message
...
Thanks to you all, great solutions which I experiment with and go for
the
most suited.

Thanks, Rob

"Patrick Molloy" wrote in message
...
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

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Rob" wrote in message
...
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

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.





Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
Thanks to you all, great solutions which I experiment with and go for the
most suited.

Thanks, Rob

"Patrick Molloy" wrote in message
...
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

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Rob" wrote in message
...
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







--

Dave Peterson

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

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
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.





Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
Thanks to you all, great solutions which I experiment with and go for
the
most suited.

Thanks, Rob

"Patrick Molloy" wrote in message
...
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

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Rob" wrote in message
...
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







--

Dave Peterson