Stopping VBA Debug pop up boxes?

[Simon Lloyd]

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

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

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

Regards,
Simo

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Simon Lloyd]

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

Regards,
Simo

 

[Jim Cone]

Simon,

Re: "...but i still need guidance."
Review "On Error Statement" in the vba help file.

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...