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