Can't stop Combobox_Change event VBA triggering another Combobox_Change event

Question

This one has caught me - I have a form with three comboboxs in it and they are:

Umbrella_Dropdown: Umbrella_Dropdown_Change trigger doesn't change the values within the other two comboboxes, but does change the listed item within them (so it doesn't trigger a _change event in the other two (don't worry about the declarations - they're declared at global level):

Private Sub Umbrella_Dropdown_Change()

If Fund_Temination.Umbrella_Dropdown.ListIndex = -1 And Fund_Temination.Umbrella_Dropdown.Value <> "" Then
    MsgBox "Please select from drop-down", vbInformation, "Select from drop-down"
    Fund_Temination.Umbrella_Dropdown.Value = ""
    Exit Sub
End If

sString = Umbrella_Dropdown.Value
l = 2
Fund_Temination.Fund_Name_Dropdown.Clear
Fund_Temination.MCH_Dropdown.Clear

Do Until l > WorksheetFunction.Max(Sheets("List of current funds").Range("C1048576").End(xlUp).Row, Sheets("List of current funds").Range("A1048576").End(xlUp).Row)
    If sString = "" Then
        If Trim(Sheets("List of current funds").Range("C" & l).Value) <> "" And Trim(Sheets("List of current funds").Range("A" & l).Value) <> "" Then
            Fund_Temination.Fund_Name_Dropdown.AddItem Sheets("List of current funds").Range("C" & l).Value
            Fund_Temination.MCH_Dropdown.AddItem Sheets("List of current funds").Range("A" & l).Value
        End If
        Else
            If Trim(Sheets("List of current funds").Range("C" & l).Value) <> "" And Trim(Sheets("List of current funds").Range("B" & l).Value) = sString And Trim(Sheets("List of current funds").Range("A" & l).Value) <> "" Then
                Fund_Temination.Fund_Name_Dropdown.AddItem Sheets("List of current funds").Range("C" & l).Value
                Fund_Temination.MCH_Dropdown.AddItem Sheets("List of current funds").Range("A" & l).Value
            End If
    End If
    l = l + 1
Loop

sString = ""

End Sub

MCH_Dropdown: MCH_Dropdown_Change trigger does change the values of the other two comboboxes and although I've included Application.enableEvents = False immediately before the change to the values but it doesn't seem to do the trick and fires off the Umbrella_Dropdown_Change and Fund_Name_Dropdown_Change events:

Private Sub MCH_Dropdown_Change()

If Len(Fund_Temination.MCH_Dropdown.Value) = 4 Then
    If Not Fund_Temination.MCH_Dropdown.ListIndex = -1 Then
        l = 1
        Do Until l > WorksheetFunction.Max(Sheets("List of current funds").Range("C1048576").End(xlUp).Row, Sheets("List of current funds").Range("A1048576").End(xlUp).Row)
            If Sheets("List of current funds").Range("A" & l).Value = Fund_Temination.MCH_Dropdown.Value Then
                Application.EnableEvents = False
                Fund_Temination.Fund_Name_Dropdown.Value = Sheets("List of current funds").Range("C" & l).Value
                Fund_Temination.Umbrella_Dropdown.Value = Sheets("List of current funds").Range("B" & l).Value
                Application.EnableEvents = True
                GoTo Finish
            End If
            l = l + 1
        Loop
        Else
            MsgBox "The MCH entered is not listed in the dropdown" & vbNewLine & vbNewLine & "Please re-enter the correct MCH or select from the dropdown", vbCritical, "Wrong MCH code entered"
            Fund_Temination.MCH_Dropdown.Value = ""
    End If
End If

Finish:

End Sub

The same can be said for the Fund_Name_Dropdown_Change event triggering the others (and, although I've only stepped through my code, I can see that this would cause an infinite "loop" where Fund_Name_Dropdown_Change triggers MCH_Dropdown_Change which would trigger Fund_Name_Dropdown_Change and so on for the end of time..)

Anyway, just in case you want to see the code for the Fund_Name_Dropdown_Change event it's this:

Private Sub Fund_Name_Dropdown_Change()

If Not Fund_Temination.Fund_Name_Dropdown.ListIndex = -1 Then
    l = 1
    Do Until l > WorksheetFunction.Max(Sheets("List of current funds").Range("C1048576").End(xlUp).Row, Sheets("List of current funds").Range("A1048576").End(xlUp).Row)
        If Sheets("List of current funds").Range("C" & l).Value = Fund_Temination.Fund_Name_Dropdown.Value Then
            Application.EnableEvents = False
            Fund_Temination.MCH_Dropdown.Value = Sheets("List of current funds").Range("A" & l).Value
            Fund_Temination.Umbrella_Dropdown.Value = Sheets("List of current funds").Range("B" & l).Value
            Application.EnableEvents = True
            GoTo Finish
        End If
        l = l + 1
    Loop
End If

Finish:

End Sub

What can I do to turn off the triggers?! If you need any further info please let me know but I think I've listed everything.

Thanks in advance

Answer 1

Because this frustrated me so much, I have to write an answer here. If you want to continuously update the combobox, please select the MatchEntry properties of 2 - fmMatchEntryNone. This mean any change that's NOT about the combobox's text will trigger the not _Change event because it doesn't try to update the value inside of the combobox.

Answer 2

Application.EnableEvents doesn't have effect on the UserForm objects, mostly. (just hedging my statement so people don't start giving me examples)

What you need is a form scoped variable (best way is adding a custom property) to store and manipulate the form's event state.

See this example and re-work your code along this:

'/ UserForm with 2 CheckBoxes :  CheckBox1 and  CheckBox2
Private m_bEvents         As Boolean

Public Property Let EnableFormEvents(bVal As Boolean)
    m_bEvents = bVal
End Property

Public Property Get EnableFormEvents() As Boolean
    EnableFormEvents = m_bEvents
End Property

Private Sub CheckBox1_Click()

    '/ Custom Event Status

    Me.EnableFormEvents = False
        Me.CheckBox2 = Me.CheckBox1
    Me.EnableFormEvents = True

End Sub

Private Sub CheckBox2_Click()

        If Me.EnableFormEvents Then
            MsgBox "Check box clicked by user."
        Else
             MsgBox "Check box clicked by code."
        End If
End Sub