Worksheet_Change events possibly conflicting with each other

[Limone]

I've got a Worksheet_Change event that can get triggered in multiple occasions depending on what column the end user is working with.

My problem is that when the Line3 part of my code gets triggered and a cell in the ContractTable[City] column autofills after a value being manually entered in in the cell right at its left (ContractTable[Address]), then the Line2 part of my code stops working, meaning that the ContractTable[Address] column won't get filled when something is entered in a cell within the ContractTable[Department] column.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any ideas why?

Also, when Line2 does work and Address gets filled by the event, City doesn't: isn't a value within Address being changed either manually or by worksheet_change supposed to trigger the event again (Line3) and allow City to be filled too?

Hope I've made myself clear enough.

I've left out the part of my code that doesn't give me problems.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rng As Range, c As Range, myMatch As Variant


If Not Intersect(Target, Range("ContractTable[Position]")) Is Nothing Then GoTo Line1 Else GoTo Line2


Line1: (...)


Line2: If Not Intersect(Target, Range("ContractTable[Department]")) Is Nothing Then
        Set rng = Intersect(Target, Range("ContractTable[Department]"))
[INDENT]Else GoTo Line3[/INDENT]
            For Each c In rng
                If Len(c.Value) = 0 Then
                    c.Offset(0, 1).Value = ""
                    Else
                        myMatch = Application.Match(c.Value, Worksheets("#DeparmentAddresses").Range("AddressTable[Department]"), 0)
                    If Not IsError(myMatch) Then
                        c.Offset(0, 1).Value = Application.Index(Worksheets("#DepartmentAddresses").Range("AddressTable[Address]"), myMatch)
                    End If
                End If
            Next
[TABLE="width: 245"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Line3:  If Not Intersect(Target, Range("ContractTable[Address]")) Is Nothing Then[/TD]
[/TR]
[TR]
[TD]        Set rng = Intersect(Target, Range("ContractTable[Address]"))[/TD]
[/TR]
[TR]
[TD]            For Each c In rng[/TD]
[/TR]
[TR]
[TD]                If Len(c.Value) = 0 Then[/TD]
[/TR]
[TR]
[TD]                    c.Offset(0, 1).Value = ""[/TD]
[/TR]
[TR]
[TD]                    Else[/TD]
[/TR]
[TR]
[TD]                        myMatch = Application.Match(Split(c.Value, " ")(UBound(Split(c.Value))), Worksheets("#CityAbbreviations").Range("CityTable[Abbreviation]"), 0)[/TD]
[/TR]
[TR]
[TD]                    If Not IsError(myMatch) Then[/TD]
[/TR]
[TR]
[TD]                        c.Offset(0, 1).Value = Application.Index(Worksheets("#CityAbbreviations").Range("CityTable[City]"), myMatch)[/TD]
[/TR]
[TR]
[TD]                    End If[/TD]
[/TR]
[TR]
[TD]                End If[/TD]
[/TR]
[TR]
[TD]            Next[/TD]
[/TR]
[TR]
[TD]        End If[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Two suggestions to start:

1. Replace all your GoTo's with If .. Then .. Else

Why?

'You have, for example:
Line2:    If Not Intersect(Target, Range("ContractTable[Department]")) Is Nothing Then
        Set rng = Intersect(Target, Range("ContractTable[Department]"))
    Else: GoTo Line3
        'You'll never get here!
        For Each c In rng
            If Len(c.Value) = 0 Then
            '....

'I assume what you want is:
If Not Intersect(Target, Range("ContractTable[Department]")) Is Nothing Then
    Set rng = Intersect(Target, Range("ContractTable[Department]"))
    For Each c In rng
        If Len(c.Value) = 0 Then
        '....
Else
    'Do your Line3 stuff here
End If

2. Whenever you make changes to the worksheet, wrap like this:

Application.EnableEvents = False

'make changes

Application.EnableEvents = True

Otherwise each change will trigger your Worksheet_Change code

 

[Limone]

I usually don't include "application.enableevents" until I feel my code is ready because it usually gives me issues testing it.

Do you think my bad habit of directing the code to lines is what might be causing my problems?

 

[StephenCrump]

I usually don't include "application.enableevents" until I feel my code is ready because it usually gives me issues testing it.

It is possible that when you make changes to the worksheet and trigger Worksheet_Change, the code will produce unintended consequences.

I find when I'm debugging and my code errors when I have set Application.EnableEvents = False, it's easy enough to turn back on using the VBA immediate window. Or you could set up a simple Sub with keyboard shortcut to do this, plus any other housekeeping necessary.

Do you think my bad habit of directing the code to lines is what might be causing my problems?

Absolutely. You don't need to use GoTo in the code you've written. Did you see in Post #2 ? Because of the poor structure, you have a block of code that will never get executed.

 

[Limone]

Hi, sorry for the late answer. I've tried implementing your suggestion but Excel won't let me use "Else" the way you showed me. It says that I'm supposed to add "If" first, not recognizing what "If" I'm referring to. I've included a screenshot so you can take a look (the code is different than the one I originally asked about but the context is kind of the same).

https://imgur.com/a/bhDkRgH

Absolutely. You don't need to use GoTo in the code you've written. Did you see in Post #2 ? Because of the poor structure, you have a block of code that will never get executed.

Isn't it supposed not to matter if that part of the code doesn't get executed under those conditions? The code is supposed to skip to the next line if the target isn't within the referred range, or enter some values if it is. If the code runs Line3 first I don't see why it shouldn't be able to run Line2 after since different conditions are met. I don't run into the same problem with this new code, for example.

 

[StephenCrump]

You have two Else statements in a row:

If ...  then
    ..
    If ...  then
        ..
    Else
    
Else
    ..

I am guessing you want:

If ...  then
    ..
    If ...  then
        ..
    Else
        ...
[COLOR=#ff0000][B]    End If[/B][/COLOR]
Else
    ..