VBA 用户窗体多个依赖动态组合框

Question

>基本上，我正在尝试在UserForm上创建多个依赖的动态组合框，因为它从LookupList工作表中提取值，如下所示

查找列表工作表

它应该如何工作：

ComboBox1 would list the company
ComboBox2 is dependent on ComboBox1
ComboBox3 is dependent on ComboBox2

例：

ComboBox1: Company = Mercedes
ComboBox2: Mercedes Model = A Class
ComboBox3: A Class Model = AMG

我已经尝试了下面的代码，但卡在了ComboBox2上

Private Sub UserForm_Initialize()
'dynamically call ComboBox1 when form initialize
With Worksheets("LookupList")
ComboBox1.List = .Range("A2:A" & .Range("a" & .Rows.Count).End(xlUp).Row).Value
End With
End Sub

---

Private Sub ComboBox1_Change()
Dim index As Integer
'list ComboBox1 and look for dependency
index = ComboBox1.ListIndex
ComboBox2.Clear
ComboBox3.Clear
'call values in ComboBox2
Select Case index
Case Is = 0
'Calls Mercedes Car Model contents dynamically
With Worksheets("LookupList")
ComboBox2.List = .Range("C2:C" & .Range("c" & Rows.Count).End(xlUp).Row).Value
End With
Case Is = 1
'Calls BMW Car Model contents dynamically
With Worksheets("LookupList")
ComboBox2.List = .Range("G2:G" & .Range("g" & Rows.Count).End(xlUp).Row).Value
End With
End Select
End Sub

---

Private Sub ComboBox2_Change()
Dim index As Integer
Dim verify_data As Long
index = ComboBox2.ListIndex
verify_data = ComboBox2.ListIndex
'Calls values in ComboBox3
Select Case index
If verify_data = 0 Then
'If Mercedes A Class Model is selected from ComboBox2, list A Class model types
Case Is = 0
With Workseets("LookupList")
ComboBox3.List = .Range("D2:D" & .Range("d" & Rows.Count).End(xlUp).Row).Value
End With
End If
End Select
End Sub

Answer 1

（

代表问题作者发布）。

我设法解决了我自己的问题，如下面的代码所示。如果有人有更短的方法，请随时分享。

  Private Sub ComboBox2_Change()
  Dim index As Integer
  'list ComboBox2 and look for dependency
   index = ComboBox2.ListIndex
   ComboBox3.Clear
   If Me.ComboBox2.Value = "A Class" Then
   With Worksheets("LookupLists")
   ComboBox3.List = .Range("D2:D" & .Range("d" & 
   Rows.Count).End(xlUp).Row).Value
   End With
   End If
   If Me.ComboBox2.Value = "B Class" Then
    With Worksheets("LookupLists")
    ComboBox3.List = .Range("E2:E" & .Range("e" & 
    Rows.Count).End(xlUp).Row).Value
    End With
    End If
    End Sub

Answer 2

缩短代码的一种方法是使用更好的语法重构它，并将每个查找列表分配给一个命名范围。

Private Sub ComboBox2_Change()
  Dim index As Integer
  'list ComboBox2 and look for dependency
   index = ComboBox2.ListIndex
   ComboBox3.Clear
  Dim whichName as String
  Select Case index
      Case "A Class": whichName = "aClass" 'assumed named range scoped to worksheet
      Case "B Class": whichName = "bClass" 'assumed named range scoped to worksheet
  End Select
  ComboBox3.List = Worksheets("LookupLists").Range(whichName).Value
End Sub

Answer 3

尝试

Private Sub UserForm_Initialize()
    Dim Ws As Worksheet
    Set Ws = Worksheets("LookupList")
    With Ws
        ComboBox1.List = .Range("A2:A" & .Range("a" & Rows.Count).End(xlUp).Row).Value
    End With
End Sub
Private Sub ComboBox1_Change()
    Dim Ws As Worksheet
    Dim rngT As Range, rngHead As Range
    Set Ws = Worksheets("LookupList")
    Set rngHead = Ws.Range("a1", "i1")

    ComboBox2.Clear
    ComboBox3.Clear
    Set rngT = rngHead.Find(ComboBox1.Value, LookIn:=xlValues, Lookat:=xlPart)
    If Not rngT Is Nothing Then
        ComboBox2.List = Ws.Range(rngT.Offset(1, 0), rngT.End(xlDown)).Value
    End If

End Sub
Private Sub ComboBox2_Change()
    Dim Ws As Worksheet
    Dim rngT As Range, rngHead As Range
    Set Ws = Worksheets("LookupList")
    Set rngHead = Ws.Range("a1", "i1")
    ComboBox3.Clear
    If ComboBox2.Value <> "" Then
        Set rngT = rngHead.Find(ComboBox2.Value, LookIn:=xlValues, Lookat:=xlPart)
        If Not rngT Is Nothing Then
            ComboBox3.List = Ws.Range(rngT.Offset(1, 0), rngT.End(xlDown)).Value
        End If
    End If
End Sub