[µo°Ý] ¦bvba userform ³]3­Ó combox3 ¦p¦ó3¼h¿z¿ï

user999

¦bvba userform ³]3­Ó combox3
¸ê®Æ¨Ó·½ EXCEL SHEET1 ¤ÀÃþ:AÄæ ¤j¶µ BÄæ ¤p¶µCÄæ ²Ó¶µ  DÄæ ©Ò­nªº­È
³o¶W­Ó1¼h­n¦p¦ó¼g,½Ð±Ð¦U¦ì¥ý¶i,ÁÂÁÂ!

user999

¦^´_ 1# user999 ¦A
¦A³Â·Ð±z¬Ý¬Ý §ÚÁÙ¨SÅv­­ ¤U¸ü

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

GBKEE

¦^´_ 2# user999
UserForm ªºµ{¦¡½X

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

½Æ»s¥N½X

user999

¦^´_ 3# oobird


    ¤Ó·PÁ±z­Ì¤F!§Ú¸Ó¦n¦n¾Ç²ß,¥H±z­Ì¬°º]¼Ë,¼ö¤ß±M·~,ÁÂÁÂ!

user999

¦^´_ 4# GBKEE


     ¤Ó·PÁ±z­Ì¤F!§Ú¸Ó¦n¦n¾Ç²ß,¥H±z­Ì¬°º]¼Ë,¼ö¤ß±M·~,ÁÂÁÂ!