[µo°Ý] ¦p¦óÅý¤é´ÁÄæ¦ìªº¦~¶È¹ïÀ³¸Ó¤ë(¤w¸Ñ¨M)

av8d

YM.rar (378.53 KB)

¤U¸ü¦¸¼Æ: 2

2012-7-11 12:14

¦p¦óÅýComboBox1¿ï¨ì2012ªº®É­ÔComboBox2¶ÈÅã¥Ü6¤ë7¤ë
ComboBox1¿ï¨ì2013ªº®É­ÔComboBox2¶ÈÅã¥Ü8¤ë9¤ë

¦]¬°¸ê®Æ2012¦~¥u¦³6¤ë7¤ë©Ò¥H¶ÈÅã¥Ü¸Ó¦~ªº¤ë

GBKEE

¦^´_ 1# av8d

1. Option Explicit          '«Å§iÅܼƪº²ßºD­n¾i¦¨
   
2. Dim d As Object
   
3. '°O±oDim d As Object ³o­n¸m©ó¼Ò²Õªº³»ºÝ§@¬°³o¼Ò²Õªº¨p¥ÎÅܼÆ
   
4. 'ComboBox2.List = Split(d(Val(ComboBox1.Value)), ",") ¤~·|¦³µª®×
   
5. Private Sub UserForm_Initialize()
   
6.     Dim A, AMonth As String
   
7.     Set d = CreateObject("Scripting.Dictionary")
   
8.     With ActiveSheet
   
9.         For Each A In .Range("a2", .[a2].End(xlDown))
   
10.           If d(Year(A.Value)) = "" Then
    
11.             d(Year(A.Value)) = Month(A.Value)
    
12.           Else
    
13.             AMonth = "," & d(Year(A.Value)) & ","                          '¦r¨åª«¥óªºitem «e«á¥[¤W ,
    
14.             If InStr(AMonth, "," & Month(A.Value) & ",") = 0 Then          '¦r¨åª«¥óªºitem ¤¤¤ñ¹ï¤ë¥÷µL­«´_
    
15.                 d(Year(A.Value)) = d(Year(A.Value)) & "," & Month(A.Value) '¦r¨åª«¥óªºitem ¦A¥[¤W , ¤Î¤ë¥÷
    
16.             End If         
    
17.           End If
    
18.         Next A
    
19.         ComboBox1.List = d.keys
    
20.     End With
    
21. End Sub
    
22. Private Sub ComboBox1_Change()
    
23.     If ComboBox1.ListIndex > -1 Then
    
24.         TextBox9 = ComboBox1.Value
    
25.         ComboBox2.List = Split(d(Val(ComboBox1.Value)), ",")
    
26.         '¦r¨åª«¥óªºitem ¥ÎSplit¥H","Âà´«¦¨°}¦C
    
27.     End If
    
28. End Sub

½Æ»s¥N½X

av8d

¦^´_ 2# GBKEE


    ½Ð°Ý¤j¤j~

¦pªG§Ú·s¼W¤F¤@­ÓComboBox3¡A¬°¤FÅã¥ÜDÄæ¡A¸Ó¦p¦ó´¡¤J?
­ì¥»¨S¦³ComboBox1¡AComboBox2ªº®É­Ô¬O³o¼Ë¤l
Private Sub UserForm_Initialize()
    Dim d As Object, A
    Set d = CreateObject("Scripting.Dictionary")
    With ActiveSheet
        For Each A In .Range("d2", .[d2].End(xlDown))
          d(A.Value) = ""
        Next A
        ComboBox3.List = Application.Transpose(d.keys)
    End With
End Sub


¦pªG·s¼W¤@­ÓComboBox3©MComboBox4°µ·f°t¡A¸Ó¦p¦ó´¡¤J?
­ì¥»¨S¦³ComboBox1¡AComboBox2ªº®É­Ô¬O³o¼Ë¤l
Dim d As Object
Private Sub UserForm_Initialize()
    Dim A As Range, W As String
    Set d = CreateObject("Scripting.Dictionary")
    With ActiveSheet
        For Each A In .Range("d2", .[d2].End(xlDown))
            If d(A.Value) = "" Then
                d(A.Value) = A.Offset(, 1).Value
            Else
                 W = "," & d(A.Value) & ","   '¦r¨åª«¥ó¤º®e«e«á¥[¤W ,
                If InStr(W, "," & A.Offset(, 1) & ",") = 0 Then d(A.Value) = d(A.Value) & "," & A.Offset(, 1)
                'InStr(w, "," & A.Offset(, 1) & ",") = 0 ¤ñ¹ï¦r¨åª«¥ó¤º®e¦³µLA.Offset(, 1)ªº¦r¦ê
            End If
         Next
         ComboBox3.List = d.KEYS
     End With
End Sub

av8d

¦^´_ 1# av8d


    YM2.rar (373.18 KB)

¤U¸ü¦¸¼Æ: 5

2012-7-11 16:37