oobird

userform¤¤¡G

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

½Æ»s¥N½X

Module¤¤

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

½Æ»s¥N½X