[發問] 在vba userform 設3個 combox3 如何3層篩選

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2011-10-28 07:54 | 只看該作者

[版主管理留言]

在vba userform 設3個 combox3
資料來源 EXCEL SHEET1 分類:A欄大項 B欄小項C欄細項 D欄所要的值
這超個1層要如何寫,請教各位先進,謝謝!

max

2^#

發表於 2011-10-28 14:37 | 只看該作者

回復 1# user999 再
再麻煩您看看我還沒權限下載

testa.rar (11.02 KB)

如何3層篩選

max

　頭銜: 街友一族

論壇貢獻勳章

3^#

發表於 2011-10-28 15:35 | 只看該作者

userform中：

Private Sub ComboBox1_Change()
With ComboBox2
If InStr(d(ComboBox1.Value), ",") = 0 Then
.Clear
.AddItem d(ComboBox1.Value)
Else
.List = Split(d(ComboBox1.Value), ",")
End If
End With
End Sub
Private Sub ComboBox2_Change()
With ComboBox3
If InStr(d2(ComboBox1.Value & ComboBox2.Value), ",") = 0 Then
.Clear
.AddItem d2(ComboBox1.Value & ComboBox2.Value)
Else
.List = Split(d2(ComboBox1.Value & ComboBox2.Value), ",")
End If
End With
End Sub
Private Sub ComboBox3_Change()
With Sheet1
rng = .UsedRange
For i = 2 To UBound(rng)
If rng(i, 1) & rng(i, 2) & rng(i, 3) = ComboBox1.Value & ComboBox2.Value & ComboBox3.Value Then
x = x + rng(i, 4)
End If
Next
TextBox1.Value = x
End With
End Sub
Private Sub UserForm_Initialize()
Set d = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
With Sheet1
rng = .UsedRange
For i = 2 To UBound(rng)
If d(rng(i, 1)) = "" Then
d(rng(i, 1)) = rng(i, 2)
Else
If InStr(d(rng(i, 1)), rng(i, 2)) = 0 Then
d(rng(i, 1)) = d(rng(i, 1)) & "," & rng(i, 2)
End If
End If
If d2(rng(i, 1) & rng(i, 2)) = "" Then
d2(rng(i, 1) & rng(i, 2)) = rng(i, 3)
Else
If InStr(d2(rng(i, 1) & rng(i, 2)), rng(i, 3)) = 0 Then
d2(rng(i, 1) & rng(i, 2)) = d2(rng(i, 1) & rng(i, 2)) & "," & rng(i, 3)
End If
End If
Next
ComboBox1.List = d.keys
End With
End Sub