如何在自定表單中建立二階的下拉式選單

hbjang

各位高手,
請問如何在自定表單中建立二階的下拉式選單?
假設自定表單中,ComboBox1的資料是A與B
我希望如果ComboBox1選擇A則在ComboBox2會顯示A1,A2,A3的選項
如果ComboBox1選擇B則在ComboBox2會顯示B1,B2,B3的選項
謝謝

register313

回復 1# hbjang

1. Private Sub UserForm_Initialize()
   
2. Ar = Array("A", "B")
   
3. For i = 0 To UBound(Ar)
   
4.     ComboBox1.AddItem Ar(i)
   
5. Next i
   
6. For i = 1 To 2
   
7.     Controls("ComboBox" & i) = ""
   
8. Next i
   
9. End Sub
   
10. 
    
11. Private Sub ComboBox1_Change()
    
12. If ComboBox1 <> "" Then
    
13.   Select Case ComboBox1
    
14.     Case "A"
    
15.       Br = Array("A1", "A2", "A3")
    
16.     Case "B"
    
17.       Br = Array("B1", "B2", "B3")
    
18.   End Select
    
19.   With Controls("ComboBox2")
    
20.     .Clear
    
21.     .Value = ""
    
22.   End With
    
23.   For i = 0 To UBound(Br)
    
24.     ComboBox2.AddItem Br(i)
    
25. Next i
    
26. End If
    
27. End Sub

複製代碼

Hsieh

回復 1# hbjang

1. Private Sub ComboBox1_Change()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. d(1) = Array("甲", "乙", "丙", "丁", "戊", "己", "庚", "辛", "壬", "癸")
   
4. d(2) = Array("金", "木", "水", "火", "土")
   
5. With ComboBox2
   
6. .Clear
   
7. s = CInt(ComboBox1)
   
8. .List = d(s)
   
9. End With
   
10. End Sub
    
11. 
    
12. Private Sub UserForm_Initialize()
    
13. With ComboBox1
    
14. .List = Array(1, 2)
    
15. End With
    
16. End Sub

複製代碼

下載 (265.13 KB)

2012-5-2 22:03

hbjang

感謝各位大大,問題已經解決
我稍微修改了一下程式,如下:
Private Sub UserForm_Activate()
Dim i%
i = Worksheets("Database").Range("A65536").End(xlUp).Row
UserForm1.ComboBox1.List = Worksheets("Database").Range("A3:A" & i).Value
Worksheets("Sheet1").Select
End Sub

Private Sub ComboBox1_Change()
If ComboBox1 <> "" Then
With Controls("ComboBox2")
    .Clear
    .Value = ""
End With

Select Case ComboBox1
Case "櫻花牌"
   j = Worksheets("Database").Range("B65536").End(xlUp).Row
   UserForm1.ComboBox2.List = Worksheets("Database").Range("B3:B" & j).Value
Case "林內牌"
   k = Worksheets("Database").Range("C65536").End(xlUp).Row
   UserForm1.ComboBox2.List = Worksheets("Database").Range("C3:C" & k).Value
End Select
End If
End Sub