Stopping VBA Debug pop up boxes?

Hi all, I have a workbook that has quite a lot of automation in it and all works fine n dandy however, if the user causes an error (lets say Runtime Error) then of course the Debug box pops up, but i have protected my code if the user chooses Debug then none of the automation in my workbok works rendering it usless!

Is there any way of bypassing this and allowing my code to act normally?, is the solution perhaps to put On Error Resume Next in every block of code? (hope not!)

i have stopped the user right clicking (i would like to stop keyboard shortcuts too!) but they could still drag a cell which will cause an error or select more than one cell at a time which causes an error....................How can i prevent these things causing a problem with the code i use?

Regards,
Simon

You certainly should not put On Error Resume Next in each module, that will
just ignore errors. You need to trap errors yourself and handle them, by
putting a an On Error Goto label at the head of the procedures. I typically
add it to the initiaiting procedure to trap unexpected errors, and then just
throw out as much detail as I can, and quit.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Simon Lloyd" <Simon.Lloyd.2cfhmr_1155388206.4623@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.2cfhmr_1155388206.4623@excelforum-nospam.com...
>
> Hi all, I have a workbook that has quite a lot of automation in it and
> all works fine n dandy however, if the user causes an error (lets say
> Runtime Error) then of course the Debug box pops up, but i have
> protected my code if the user chooses Debug then none of the automation
> in my workbok works rendering it usless!
>
> Is there any way of bypassing this and allowing my code to act
> normally?, is the solution perhaps to put On Error Resume Next in every
> block of code? (hope not!)
>
> i have stopped the user right clicking (i would like to stop keyboard
> shortcuts too!) but they could still drag a cell which will cause an
> error or select more than one cell at a time which causes an
> error....................How can i prevent these things causing a
> problem with the code i use?
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=571027
>

Thanks for the reply Bob, if i used On Error Goto 0 would that prevent runtime Errors occuring or VBA error boxes poping up? would it also prevent the code from being stopped ?(as is the case now), other than using the ill fated On error Resume Next, i have no experience of error handling, i know i have been visiting this forum for a couple of years and picked up so much from all the help here but, you only learn or ask questions on things that are directly important at the time................ok call it laziness! but i still need guidance.

Regards,
Simon

No! If you read On Error Goto 0 in the help, you will see it says that it
.... Disables any enabled error handler in the current procedure. That means
that errors will break, causing the Debug popup, which is exactly what you
don't want.

You need something like

Option Explicit

Public sProcedure As Sring

Sub Main()

On Error Goto errhandler

sProcedure = "Main"

'some code

Call Macro1

'some more code

Call Macro2

'final code

Exit Sub

errHandler:
MsgBox "Error in procedure: " & sProcedure
MsgBox "Error " & err.Number & " - " & err.Description
'and any other info you think might help

End Sub

Sub Macro1()

sProcedure = "Macro1"

'code

End Sub

Sub Macro2()

sProcedure = "Macro2"

'code

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Simon Lloyd" <Simon.Lloyd.2cfn6t_1155395411.7823@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.2cfn6t_1155395411.7823@excelforum-nospam.com...
>
> Thanks for the reply Bob, if i used On Error Goto 0 would that prevent
> runtime Errors occuring or VBA error boxes poping up? would it also
> prevent the code from being stopped ?(as is the case now), other than
> using the ill fated On error Resume Next, i have no experience of error
> handling, i know i have been visiting this forum for a couple of years
> and picked up so much from all the help here but, you only learn or ask
> questions on things that are directly important at the
> time................ok call it laziness! but i still need guidance.
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=571027
>

Simon,

Re: "...but i still need guidance."
Review "On Error Statement" in the vba help file.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware