msgbox on error

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" <Ciara@discussions.microsoft.com> a écrit dans le message de news:
2516C6D6-ABC8-4FE2-8DD7-DCA7D65FED1F@microsoft.com...
>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