VBA code silently crashing. Problem with Data Validation drop down lists.

[Don Wiss]

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>.

[Bob Phillips]

Don,

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.

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Don Wiss" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> 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>.

[Don Wiss]

On 26 Feb 2005, Bob Phillips <[email protected]> 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" <donwiss@no_spam.com> wrote in message
>news:[email protected]...
>> 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>.
>

[Bob Phillips]

Don,

You could send me the workbook so I can see it happen. I just might see
something that you miss as you are too close.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Don Wiss" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> On 26 Feb 2005, Bob Phillips <[email protected]> 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" <donwiss@no_spam.com> wrote in message
> >news:[email protected]...
> >> 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>.
> >
>

[Peter T]

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" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> On 26 Feb 2005, Bob Phillips <[email protected]> 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" <donwiss@no_spam.com> wrote in message
> >news:[email protected]...
> >> 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>.
> >
>

[Don Wiss]

On Sat, 26 Feb 2005 13:34:33 -0000, "Peter T" <peter_t@discussions> wrote:

>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.

And I noted in my first post I'm using XL2002. In 97 changes to Data
Validation lists did not trigger a change event. You could capture them
unreliably by having a cell dependent on the value and using a calculate
event.

>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.

There is only two UDFs in spreadsheet cells. (Both appear multiple times.)
Both have error trapping.

And I have no other workbooks open.

I have a similar problem in another workbook. The thing both have in common
is both make sql calls to an Access database using DAO 3.6. (And the
database is always closed after the call.) In the other it was calling the
database after the change event. Here the problematic sheet has no
dependents or precedents with any other sheet. It calls no database. A
macro, run by a button push edits and reads from this sheet. It is
completely independent from the rest of the workbook. I tried using a new
workbook with just this one sheet and by itself it works fine.

>Regards,
>Peter T
>
>"Don Wiss" <donwiss@no_spam.com> wrote in message
>news:[email protected]...
>> On 26 Feb 2005, Bob Phillips <[email protected]> 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" <donwiss@no_spam.com> wrote in message
>> >news:[email protected]...
>> >> 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>.
>> >
>>
>

[Peter T]

The symptoms you originally described so closely matched my frustrating
experiences of running code in XL97 with (user's) faulty UDF's - it blinded
me to the fact you clearly stated you are using XL2002 - sorry.

> I have a similar problem in another workbook. The thing both have in
> common is both make sql calls to an Access database using DAO 3.6.

I didn't think later versions suffer problem I mentioned. Yet, what you have
since described is interesting. Maybe when a UDF plays away from home, a
similar scenario can occur. Might depend of what those "calls" are.

Regards,
Peter T

"Don Wiss" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> On Sat, 26 Feb 2005 13:34:33 -0000, "Peter T" <peter_t@discussions> wrote:
>
> >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.
>
> And I noted in my first post I'm using XL2002. In 97 changes to Data
> Validation lists did not trigger a change event. You could capture them
> unreliably by having a cell dependent on the value and using a calculate
> event.
>
> >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.
>
> There is only two UDFs in spreadsheet cells. (Both appear multiple times.)
> Both have error trapping.
>
> And I have no other workbooks open.
>
> I have a similar problem in another workbook. The thing both have in
common
> is both make sql calls to an Access database using DAO 3.6. (And the
> database is always closed after the call.) In the other it was calling the
> database after the change event. Here the problematic sheet has no
> dependents or precedents with any other sheet. It calls no database. A
> macro, run by a button push edits and reads from this sheet. It is
> completely independent from the rest of the workbook. I tried using a new
> workbook with just this one sheet and by itself it works fine.
>
> >Regards,
> >Peter T
> >
> >"Don Wiss" <donwiss@no_spam.com> wrote in message
> >news:[email protected]...
> >> On 26 Feb 2005, Bob Phillips <[email protected]> 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" <donwiss@no_spam.com> wrote in message
> >> >news:[email protected]...
> >> >> 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>.
> >> >
> >>
> >
>

[CHatmaker]

This is posted for others who come across this thread looking for a solution to the - Validation "Gotcha"

When using VBA to create validation, we can put values in the validation object that we shouldn't - or - leave out an important piece.

Setup: Using VBA, add VALIDATION.TYPE to 3 (xlValidateList) to a cell and either neglect to add a value to FORMULA1 or add a value that is not a comma separated list or a reference to a single column range. Save and close the workbook. When the workbook opens and the worksheet with the improper validation is displayed the workbook will crash. I have replicated this on a few different versions of XL and diffent PCs.

Counter Measure: Assuming you can get the workbook to open, goto VBA and either delete the offending validation:

Please Login or Register  to view this content.

-or- correct it:

Please Login or Register  to view this content.