两个ComboBox与一个TextBox联动

tpaiwy

两个ComboBox与一个TextBox联动，求助大神修改一下代码

tpaiwy

1. Dim d1 As New Dictionary
   
2. Dim d2 As New Dictionary
   
3. Private Sub ComboBox1_Click()
   
4. ComboBox2.Clear
   
5. TextBox1.Clear
   
6. ComboBox2.List = d1(ComboBox1.Text).Items
   
7. End Sub
   
8. Private Sub ComboBox2_Click()
   
9. TextBox1 = ""
   
10. TextBox1.List = d2(ComboBox1.Text & ComboBox2.Text).Items
    
11. End Sub
    
12. Private Sub UserForm_Initialize()
    
13. arr = Sheet1.Range("a3:c" & Sheet1.[a65536].End(3).Row)
    
14. For i = 1 To UBound(arr)
    
15. a = arr(i, 1) & "": b = arr(i, 2) & "": c = arr(i, 3) & ""
    
16. x = arr(i, 1) & arr(i, 2)
    
17. If d1.Exists(a) = False Then Set d1(a) = New Dictionary
    
18.    d1(a)(b) = b
    
19. If d2.Exists(x) = False Then Set d2(x) = New Dictionary
    
20.    d2(x)(c) = c
    
21. Next
    
22. ComboBox1.List = d1.Keys
    
23. End Sub
    
24. 
    

复制代码

山菊花

供参考。

sqrall900

Public d As Object
Public ListArr1

Private Sub ComboBox1_Change()
    Dim i As Long
    Dim d0 As Object
   
    Set d0 = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(ListArr1)
        If ListArr1(i, 1) = ComboBox1.Text Then d0(ListArr1(i, 2)) = ""
    Next
    TextBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox2.List = d0.Keys()
End Sub

Private Sub ComboBox2_Change()
    Dim i As Long
   
    For i = 1 To UBound(ListArr1)
        If ListArr1(i, 1) = ComboBox1.Text And ListArr1(i, 2) = ComboBox2.Text Then TextBox1.Text = ListArr1(i, 3): Exit For
    Next
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim Sh As Worksheet
   
    Set d = CreateObject("Scripting.Dictionary")
    Set Sh = ThisWorkbook.Sheets("数据")
    Sr = 2
    Sc = 1
    Er = Sh.Cells(Sh.Rows.Count, Sc).End(3).Row
    Ec = 3
    ListArr1 = Sh.Range(Sh.Cells(Sr, Sc), Sh.Cells(Er, Ec))
    For i = 1 To UBound(ListArr1)
        d(ListArr1(i, 1)) = ""
    Next
    ComboBox1.List = d.Keys()
End Sub

tpaiwy

山菊花 发表于 2018-10-4 15:37
供参考。

万分感谢版主指导

tpaiwy

sqrall900 发表于 2018-10-4 15:51
Public d As Object
Public ListArr1

谢谢大神的指导和帮助

tpaiwy

山菊花 发表于 2018-10-4 15:37
供参考。

版主大神，还有个需要求教的问题，我想做个多行录入，现只会单行，请指导一下