Results 1 to 2 of 2

Thread: Worksheet change detection for macro execution, skips one macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Worksheet change detection for macro execution, skips one macro

    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:

    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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Worksheet change detection for macro execution, skips one macro

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

    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 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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •