[發問] 在vba userform 設3個 combox3 如何3層篩選

user999

在vba userform 設3個 combox3
資料來源 EXCEL SHEET1 分類:A欄 大項 B欄 小項C欄 細項  D欄 所要的值
這超個1層要如何寫,請教各位先進,謝謝!

user999

回復 1# user999 再
再麻煩您看看 我還沒權限 下載

oobird

userform中：

1. Private Sub ComboBox1_Change()
   
2. With ComboBox2
   
3. If InStr(d(ComboBox1.Value), ",") = 0 Then
   
4. .Clear
   
5.         .AddItem d(ComboBox1.Value)
   
6.         Else
   
7. .List = Split(d(ComboBox1.Value), ",")
   
8. End If
   
9. End With
   
10. End Sub
    
11. 
    
12.          
    
13. Private Sub ComboBox2_Change()
    
14. With ComboBox3
    
15. If InStr(d2(ComboBox1.Value & ComboBox2.Value), ",") = 0 Then
    
16. .Clear
    
17.         .AddItem d2(ComboBox1.Value & ComboBox2.Value)
    
18.         Else
    
19. .List = Split(d2(ComboBox1.Value & ComboBox2.Value), ",")
    
20. End If
    
21. End With
    
22. 
    
23. End Sub
    
24. 
    
25. Private Sub ComboBox3_Change()
    
26. With Sheet1
    
27. rng = .UsedRange
    
28. For i = 2 To UBound(rng)
    
29. If rng(i, 1) & rng(i, 2) & rng(i, 3) = ComboBox1.Value & ComboBox2.Value & ComboBox3.Value Then
    
30. x = x + rng(i, 4)
    
31. End If
    
32. Next
    
33. TextBox1.Value = x
    
34. End With
    
35. End Sub
    
36. 
    
37. Private Sub UserForm_Initialize()
    
38. Set d = CreateObject("Scripting.Dictionary")
    
39. Set d2 = CreateObject("Scripting.Dictionary")
    
40. With Sheet1
    
41. rng = .UsedRange
    
42. For i = 2 To UBound(rng)
    
43. If d(rng(i, 1)) = "" Then
    
44. d(rng(i, 1)) = rng(i, 2)
    
45. Else
    
46. If InStr(d(rng(i, 1)), rng(i, 2)) = 0 Then
    
47. d(rng(i, 1)) = d(rng(i, 1)) & "," & rng(i, 2)
    
48. End If
    
49. End If
    
50. If d2(rng(i, 1) & rng(i, 2)) = "" Then
    
51. d2(rng(i, 1) & rng(i, 2)) = rng(i, 3)
    
52. Else
    
53. If InStr(d2(rng(i, 1) & rng(i, 2)), rng(i, 3)) = 0 Then
    
54. d2(rng(i, 1) & rng(i, 2)) = d2(rng(i, 1) & rng(i, 2)) & "," & rng(i, 3)
    
55. End If
    
56. End If
    
57. Next
    
58. ComboBox1.List = d.keys
    
59. End With
    
60. End Sub

複製代碼

Module中

1. Public d As Object
   
2. Public d2 As Object

複製代碼

GBKEE

回復 2# user999
UserForm 的程式碼

1. Dim D As Object
   
2. Private Sub UserForm_Initialize()
   
3.     Dim Dx(1 To 3) As Object, i As Integer, ii As Integer
   
4.     Set D = CreateObject("SCRIPTING.DICTIONARY")
   
5.     For ii = 1 To UBound(Dx)
   
6.         Set Dx(ii) = CreateObject("SCRIPTING.DICTIONARY")
   
7.     Next
   
8.     i = 2
   
9.     Do While Sheet1.Cells(i, 1) <> ""
   
10.            For ii = 1 To UBound(Dx)
    
11.                 Dx(ii)(Cells(i, ii).Value) = ""
    
12.             Next
    
13.             D(Cells(i, 1) & Cells(i, 2) & Cells(i, 3)) = Cells(i, "d")
    
14.             i = i + 1
    
15.     Loop
    
16.     For ii = 1 To UBound(Dx)
    
17.         Controls("ComboBox" & ii).List = Dx(ii).keys
    
18.     Next
    
19. End Sub
    
20. Private Sub ComboBox1_Change()
    
21.     平均
    
22. End Sub
    
23. Private Sub ComboBox2_Change()
    
24.     平均
    
25. End Sub
    
26. Private Sub ComboBox3_Change()
    
27.     平均
    
28. End Sub
    
29. Private Sub 平均()
    
30.     If D.exists(ComboBox1 & ComboBox2 & ComboBox3) Then
    
31.         TextBox1 = D(ComboBox1 & ComboBox2 & ComboBox3)
    
32.     Else
    
33.         TextBox1 = ""
    
34.     End If
    
35. End Sub

複製代碼

user999

回復 3# oobird


    太感謝您們了!我該好好學習,以您們為榜樣,熱心專業,謝謝!

user999

回復 4# GBKEE


     太感謝您們了!我該好好學習,以您們為榜樣,熱心專業,謝謝!