Worksheet change detection for macro execution, skips one macro

[SedrickBowser]

Hey there!

I have a few various modules depending on a- I believe simple- worksheet change detection for their initiation. The problem is that the worksheet_change seems to constantly detect a change in two of macro initiation functions and it never acknowledges the third.

Heres the code:

Private Sub worksheet_change(ByVal target As Range)

Dim keycells As Range
Dim keycellsEF As Range
Dim keycellsDebt As Range

Set keycells = Me.Range("E36, E46, E53, E59, E67, E77, M35, M44")
Set keycellsEF = Me.Range("O7, P7")
Set keycellsDebt = Me.Range("G23")

If Application.Intersect(keycells, Range(target.Address)) _
Is Nothing Then
Call visual_aidFunds
End If

If Application.Intersect(keycellsEF, Range(target.Address)) _
Is Nothing Then
Call visual_aidEF
End If

If Application.Intersect(keycellsDebt, Range(target.Address)) _
Is Nothing Then
Call visual_aidDebt
End If
           
End Sub

What I have done to test the "flow" of the program is to place msgboxes at the beginning of each called modules. Visual_aidEF, with the use of keycellsEF, is the only one not triggered. What is weird, though, is that visual_aidEF will trigger if it replaces visual_aidDebt or visual_aidFunds.

One further test that I have done is that I have placed msgboxes to test the target.address at various points and have also msgboxed the three different keycell variables that I have created to see if that values align in that way-which they do.

That last that I'd like to say is that I'm not sure if my strategy to use individual If statements for each module is wise, but ElseIf refused to work and I don't believe that I can use a Case Select in this instance.

I'm wiling to believe that there is a much more practical solution that would resolve this weirdness, but I haven't been able to find the answer after quite a few hours of testing researching. Your consideration is much appreciated!

-Sedrick

 

[DanteAmor]

The logic is like this: if the target intersection is in some keycells, that is, if it is not nothing, then call visual_aidFunds:

Private Sub worksheet_change(ByVal target As Range)


    Dim keycells As Range
    Dim keycellsEF As Range
    Dim keycellsDebt As Range
    
    Set keycells = Me.Range("E36, E46, E53, E59, E67, E77, M35, M44")
    Set keycellsEF = Me.Range("O7, P7")
    Set keycellsDebt = Me.Range("G23")
    
    If Not Intersect(target, keycells) Is Nothing Then
        Call visual_aidFunds
    End If
    
    If Not Intersect(target, keycellsEF) Is Nothing Then
        Call visual_aidEF
    End If
    
    If Not Intersect(target, keycellsDebt) Is Nothing Then
        Call visual_aidDebt
    End If
               
End Sub