根据前面两个ListFillRange的值为ComboBox动态选择ComboBoxes？

Question

我目前有三个组合框(comboBox2、comboBox3、comboBox4)。ComboBox2.ListFillRange和ComboBox3.ListFillRange当前链接到命名范围。根据在ComboBox2和ComboBox3中选择的内容，ComboBox4的ListFillRange将有所不同，即它查看不同的名称范围。

       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

当我为ComboBox2选择“内部”，为ComboBox3选择"Breach“时，我得到了ComboBox4所需的ListFillRange。但是，如果我为ComboBox2选择"External“，并将ComboBox3设置为"Breach”，那么对于"External"-"breach“组合，我在ComboBox4中得不到预期的ListFillRange，我必须重新选择"Breach”才能得到这个结果。有人知道当我重新选择一个并离开另一个时，如何让它自动更新吗？

Answer 1

这是因为您只有ComboBox3_Change()事件处理程序，该事件处理程序仅在ComboBox3更改时触发。

您必须执行必须执行相同工作的ComboBox2_Change()事件处理程序

因此，您可以添加一个要由任一事件处理程序调用sub

此外，您还会遇到以下问题：

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

因为它会导致：

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

因此在这一行的错误中：

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

工作表代码窗格中的上述代码可能是：

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

它可以缩短为：

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