請問連動式下拉式選單該如何製作，如圖檔例如;當ComBoBox1選擇A1時，則ComBoBox2下拉選單則會變成工作表2 A2欄位下方人員名稱小明1,小明2....，當我選擇B1時ComBoBox2下拉選單又會變成大明1,大明2....以此類推，請問是否有人可以幫小弟解答..謝謝!!
是有特殊用途嗎？
試試
Private Sub ComboBox1_Change()
Dim a As Range
Dim m, l As Integer
ComboBox2.Clear
m = Application.Match(ComboBox1.Text, Range("Sheet1!A1:D1"), 0)
l = Application.CountA(Columns(m))
For Each a In Range(Cells(2, m), Cells(l, m))
ComboBox2.AddItem a
Next
End Sub
Private Sub UserForm_Initialize()
Dim a As Range
For Each a In Range("Sheet1!A1:D1")
ComboBox1.AddItem a
Next
End Sub
Private Sub ComboBox1_Change()
Dim Lastrow As Integer
If ComboBox1.ListIndex > -1 Then
ComboBox2.Clear
Lastrow = Sheets("工作表1").Columns(ComboBox1.ListIndex + 1).End(xlDown).Row - 1
ComboBox2.List = Sheets("工作表1").Range("a2").Resize(Lastrow, 1).Offset(, ComboBox1.ListIndex).Value
End If
End Sub
Private Sub UserForm_Initialize()
ComboBox1.List = Application.Transpose(Sheets("工作表1").Range("a1:d1").Value)
End Sub
Private Sub ComboBox1_Change()
Dim Lastrow As Integer
If ComboBox1.ListIndex > -1 Then
ComboBox2.Clear
Lastrow = Sheets("工作表1").Columns(ComboBox1.ListIndex + 1).End(xlDown).Row - 1
ComboBox2.List = Sheets("工作表1").Range("a2").Resize(Lastrow, 1).Offset(, ComboBox1.ListIndex).Value
End If
End Sub
風城小方 worte:
如何修改才會加大範圍...(恕刪)
Private Sub ComboBox1_Change()
Dim Lastrow As Integer
If ComboBox1.ListIndex > -1 Then
Lastrow = Sheets("工作表1").Columns(ComboBox1.ListIndex + 3).End(xlDown).Row - 1
'出現資料的第一個欄位號碼
ComboBox2.List = Sheets("工作表1").Range("c2").Resize(Lastrow, 1).Offset(, ComboBox1.ListIndex).Value
'出現資料的第一個不含標題的儲存格名稱
ComboBox2.ListIndex = 0
End If
End Sub
Private Sub UserForm_Initialize()
ComboBox1.List = Application.Transpose(Sheets("工作表1").Range("c1:f1").Value)
'欄位範圍
ComboBox1.ListIndex = 0
End Sub
牛小傑 wrote:
想請教牛小傑，
我跟著步驟輸入但有點卡關，因為會抓到不同工作表的資料，
因此想請問在"For Each a In Range(Cells(2, m), Cells(l, m))"要怎麼輸入限定某個工作表呢?
我把我改寫跟需要請您幫忙的部分標記在下面，
謝謝。
Dim a As Range
Dim m, l As Integer
CBCsecond.Clear
m = Application.Match(CBCfirst.Text, Sheets("會計表單").Range("A1:I1"), 0)
l = Application.CountA(Columns(m))
For Each a In Range(Cells(2, m), Cells(l, m))
CBCsecond.AddItem a
Next
End Sub
Private Sub UserForm_Activate()
Dim a As Range
For Each a In Sheets("會計表單").Range("A1:I1")
CBCfirst.AddItem a
Next
End Sub
Zi Jie Yu wrote:
(恕刪)
太感謝您的回應了!!
不過應該是我整個程式碼有還有寫錯的地方，測試之後發現問題包括
1.選取"100-收入"時，偵錯程式碼為For Each a In .Range(.Cells(2, m), .Cells(l, m))
2.選取CBCfirst下拉式選單其他選項時，CBCsecond下拉式選單無法完整跑出下面的資料。
我將我完整的程式碼及工作表單列在下面，
Private Sub CBCfirst_Change()
Dim a As Range
Dim m, l As Integer
CBCsecond.Clear
m = Application.Match(CBCfirst.Text, Sheets("會計表單").Range("A1:I1"), 0)
l = Application.CountA(Columns(m))
With Sheets("會計表單")
For Each a In .Range(.Cells(2, m), .Cells(l, m))
CBCsecond.AddItem a
Next
End With
End Sub
Private Sub UserForm_Activate()
Dim a As Range
For Each a In Sheets("會計表單").Range("A1:I1")
CBCfirst.AddItem a
Next
End Sub
非常感謝您的幫忙!!
內文搜尋
從 APP 打開
X