In addition to Claus' good advice to impliment an error handler in your
complex routines, it sounds like you need a methodology in place to
properly manage Excel events!

I use the following mechanism to ensure only 1 routine has control of
Excel's events until that routine is done with them. This ensures
another routine doesn't inadvertently toggle these setting mid-code in
the original caller, AND that events aren't inadvertently left turned
off.

Just drop this into a standard module and reuse for all your
projects...

Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID < Caller Then _
If AppMode.CallerID < "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating
.ScreenUpdating = False
AppMode.CalcMode = .Calculation
.Calculation = xlCalculationManual
AppMode.Events = .EnableEvents
.EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub 'EnableFastCode

...which requires the following 'Type' declaration to work correctly.

Type udtAppModes
Events As Boolean
CalcMode As XlCalculation
Display As Boolean
CallerID As String
End Type
Public AppMode As udtAppModes

To use the procedure I just call it from any def and pass the args as
needed...

Sub MySub()
Const sSource$ = "MySub"
On Error GoTo errExit
EnableFastCode sSource '//turn it on
'...code follows

errExit:
EnableFastCode sSource, False ''//turn it off
End Sub 'MySub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion