XL97 (is that what you have ?) is particularly prone to unhandled errors in
UDF's causing all other code to simply stop. XL2k very much less so but
similarly under certain situations. Not sure about later versions, hopefully
not.

Normally calculation manual prevents the UDF's from firing and all's well.
But, I've experienced UDF's triggering even with calculation manual and even
in workbooks totally unconected that with the main code (xl97).
Theoretically this should not occur, and just at the moment not sure how to
recreate the problem. But it can occur!

Obviously the best solution is to ensure UDF's are fully error handled.

Regards,
Peter T

"Don Wiss" wrote in message
...
On 26 Feb 2005, Bob Phillips wrote:

Surely, the point of error trapping is so that it does fail silently. If
you
don't want that, take error trapping off and get a runtime error.

You misunderstood. When I take the error trapping off I do not get a
runtime error. It then silently fails, meaning the code simply stops
running. And EnableEvents=False.

As to the problem, something else must be happening. I have recreated the
code and some data, and it works every time for me.

What else could be happening? The code does have user functions. As you
see
I left it on auto calculate. I just tried it with setting Calculation to
Manual. I removed the error trapping. It is still failing on the line
Target.Offset(0, 1).Value = "Excluded" (or any of the ones like it). NO
runtime error message. I'm left with EnableEvents=False and
Calculation=Manual. Watching it fail on the line the title bar does
quickly
flash a few times, so something is happening. But what? For now I'm am
just
going to have to tell the user they can't have this feature.


"Don Wiss" wrote in message
.. .
One of the things I dislike is when Excel VBA silently crashes and
doesn't
return a debug message box. You can step through and find the bad line,
but
that doesn't help you determine why the line fails.

In this routine, without the On Error line, it will silently fail on
any of
the Target.Offset(0, 1).Value = lines. With the error trapping at least
it
does know that there was an error, so my EnableEvents is restored.

Target.Offset(0, 1).Address is a Data Validation drop down list. The
values
I'm trying to assign to the cells are valid entries. (Target.Address is
also a Data Validation drop down list.)

This is not the first time I've had problems with changes in Data
Validation drop down lists cells changing other Data Validation drop
down
lists. I'm using XL2002.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo ErrorSection

If Not Intersect(Range("TermsLinked"), Target) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "Not Covered" Then
Target.Offset(0, 1).Value = "Follow Form"
ElseIf Target.Value = "Covered" Then
Target.Offset(0, 1).Value = "Excluded"
End If
ElseIf Target.Address = Range("TermsWC").Cells(10, 1).Address Then
Application.EnableEvents = False
If Target.Value = "No Exposure" Then
Target.Offset(0, 1).Value = "No Exposure"
ElseIf Target.Value = "Exposure" Then
Target.Offset(0, 1).Value = "Excluded"
End If
End If

ErrorSection:
Application.EnableEvents = True

End Sub

Don <donwiss at panix.com.