Dynamically selecting ListFillRange for ComboBox based on two previous ComboBoxes' values?

Question

I currently have three combo boxes (comboBox2, comboBox3, comboBox4). ComboBox2.ListFillRange and ComboBox3.ListFillRange are currently linked to named ranges. Depending on what is chosen in ComboBox2 and ComboBox3 the ListFillRange will be different for ComboBox4, i.e it looks at a different name range.

       Private Sub ComboBox3_Change()

       Application.EnableEvents = True

        If ComboBox2.Value = "Internal" And ComboBox3.Value = "Breach" Then
            ComboBox4.ListFillRange = "=Internal_Breach"
       ElseIf ComboBox2.Value = "Internal" And ComboBox3.Value = "Error" Then
            ComboBox4.ListFillRange = "=Internal_Error"
       ElseIf ComboBox2.Value = "External" And ComboBox3.Value = "Breach" Then
            ComboBox4.ListFillRange = "=External_Breach"
       ElseIf ComboBox2.Value = "External" And ComboBox3.Value = "Error" Then
            ComboBox4.ListFillRange = "=External_Error"
       Else: ComboBox2.Value = "External" And ComboBox3.Value = "Error"
            ComboBox4.ListFillRange = "=External_Error"
      End If

      End Sub

When I select "Internal" for ComboBox2 and "Breach" for ComboBox3 I get the required ListFillRange for ComboBox4. But if I then select "External" for ComboBox2 and leave ComboBox3 as "Breach" I don't get the expected ListFillRange in ComboBox4 for the "External"-"breach" combination, I have to reselect "Breach" to get this. Anyone know how I can get it to update automatically when I reselect one and leave the other?

user3598756 user3598756 · Accepted Answer · 2016-05-19T17:48:00

it's because you only have ComboBox3_Change() event handler which only fires if ComboBox3 is changed.

you must ass a ComboBox2_Change() event handler that has to do the same work

so you could add a sub to be called by either event handlers

moreover you'd have problems with:

Else: ComboBox2.Value = "External" And ComboBox3.Value = "Error"
            ComboBox4.ListFillRange = "=External_Error"
End If

since it'd result in:

Else
      ComboBox2.Value = "External" And ComboBox3.Value = "Error"
      ComboBox4.ListFillRange = "=External_Error"
End If

and thus in an error at the line:

ComboBox2.Value = "External" And ComboBox3.Value = "Error"

for all what above your code in the worksheet code pane could be:

Option Explicit

Private Sub ComboBox2_Change()
    Application.EnableEvents = True
    Call CheckComboBoxes
End Sub

Private Sub ComboBox3_Change()
    Application.EnableEvents = True
    Call CheckComboBoxes
End Sub

Sub CheckComboBoxes()
    If ComboBox2.Value = "Internal" And ComboBox3.Value = "Breach" Then
        ComboBox4.ListFillRange = "=Internal_Breach"
    ElseIf ComboBox2.Value = "Internal" And ComboBox3.Value = "Error" Then
        ComboBox4.ListFillRange = "=Internal_Error"
    ElseIf ComboBox2.Value = "External" And ComboBox3.Value = "Breach" Then
        ComboBox4.ListFillRange = "=External_Breach"
    ElseIf ComboBox2.Value = "External" And ComboBox3.Value = "Error" Then
        ComboBox4.ListFillRange = "=External_Error"
    ElseIf ComboBox2.Value = "External" And ComboBox3.Value = "Error" Then
        ComboBox4.ListFillRange = "=External_Error"
    End If
End Sub

which could be shortened down to:

Option Explicit

Private Sub ComboBox2_Change()
    Application.EnableEvents = True
    Call CheckComboBoxes
End Sub

Private Sub ComboBox3_Change()
    Application.EnableEvents = True
    Call CheckComboBoxes
End Sub

Sub CheckComboBoxes()
    ComboBox4.ListFillRange = ComboBox2.Value & "_" & ComboBox3.Value
End Sub

Dynamically selecting ListFillRange for ComboBox based on two previous ComboBoxes' values?

1 Answers