i have protected a worksheet - if i click a command button I get

Run - time error '1004
Application defined or object defined error
(with options to debug etc....)

which is good because i don't want the command buttons to work while the
sheet is protected

however, i want my own message to come up with no options to debug/end/help.

any ideas on code for this?

Hello Ciara

On Error GoTo Err_Hdler
'your code here
Exit Sub

Err_Hdler:
Select Case Err.Number
Case Is = 1004
MsgBox "This is your message"
Case Else
Msgbox "Other message"
End Select

HTH
Cordially
Pascal

"Ciara" a écrit dans le message de news:
...
i have protected a worksheet - if i click a command button I get

Run - time error '1004
Application defined or object defined error
(with options to debug etc....)

which is good because i don't want the command buttons to work while the
sheet is protected

however, i want my own message to come up with no options to
debug/end/help.

any ideas on code for this?

it says 'error not defined' what am i leaving out?

thanks,

sorry i mean 'label' not defined

It might just be easier to check the protection status of the worksheet first:

Option Explicit
Sub testme()

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
If .ProtectContents _
Or .ProtectDrawingObjects _
Or .ProtectScenarios Then
MsgBox "Can't run on a protected sheet"
Exit Sub
End If
End With

End Sub

Ciara wrote:

i have protected a worksheet - if i click a command button I get

Run - time error '1004
Application defined or object defined error
(with options to debug etc....)

which is good because i don't want the command buttons to work while the
sheet is protected

however, i want my own message to come up with no options to debug/end/help.

any ideas on code for this?

--

Dave Peterson

Thanks Dave - that works - but once the msg box comes up and I click 'ok' i
still get the error message - can i put in a line of code that will cancel
the error message?

"Dave Peterson" wrote:

It might just be easier to check the protection status of the worksheet first:

Option Explicit
Sub testme()

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
If .ProtectContents _
Or .ProtectDrawingObjects _
Or .ProtectScenarios Then
MsgBox "Can't run on a protected sheet"
Exit Sub
End If
End With

End Sub

Ciara wrote:

i have protected a worksheet - if i click a command button I get

Run - time error '1004
Application defined or object defined error
(with options to debug etc....)

which is good because i don't want the command buttons to work while the
sheet is protected

however, i want my own message to come up with no options to debug/end/help.

any ideas on code for this?

--

Dave Peterson

I would have bet that "exit sub" would stop the procedure.

I'm betting that your code does something else, though. You may want to post
that portion of it.

Ciara wrote:

Thanks Dave - that works - but once the msg box comes up and I click 'ok' i
still get the error message - can i put in a line of code that will cancel
the error message?

"Dave Peterson" wrote:

It might just be easier to check the protection status of the worksheet first:

Option Explicit
Sub testme()

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
If .ProtectContents _
Or .ProtectDrawingObjects _
Or .ProtectScenarios Then
MsgBox "Can't run on a protected sheet"
Exit Sub
End If
End With

End Sub

Ciara wrote:

i have protected a worksheet - if i click a command button I get

Run - time error '1004
Application defined or object defined error
(with options to debug etc....)

which is good because i don't want the command buttons to work while the
sheet is protected

however, i want my own message to come up with no options to debug/end/help.

any ideas on code for this?

--

Dave Peterson


--

Dave Peterson