1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| Dim f
Private Sub UserForm_Initialize()
Set f = Sheets("BDD")
Set d1 = CreateObject("Scripting.Dictionary")
For Each c In f.Range("A2:A" & f.[A65000].End(xlUp).Row)
If c <> "" Then d1(c.Value) = ""
Next c
Me.ComboBox1.List = d1.keys
End Sub
Private Sub ComboBox1_click()
Set d1 = CreateObject("Scripting.Dictionary")
For Each c In f.Range("B2:B" & f.[A65000].End(xlUp).Row)
tmp = c.Offset(0, -1): If tmp = "" Then tmp = c.Offset(0, -1).End(xlUp)
If tmp = Me.ComboBox1 Then If c <> "" Then d1(c.Value) = ""
Next c
If d1.Count > 0 Then Me.ComboBox2.List = d1.keys
End Sub
Private Sub ComboBox2_click()
Set d1 = CreateObject("Scripting.Dictionary")
For Each c In f.Range("C2:C" & f.[A65000].End(xlUp).Row)
tmp = c.Offset(0, -2): If tmp = "" Then tmp = c.Offset(0, -2).End(xlUp)
tmp2 = c.Offset(0, -1): If tmp2 = "" Then tmp2 = c.Offset(0, -1).End(xlUp)
If c <> "" Then
If tmp = Me.ComboBox1 And tmp2 = Me.ComboBox2 Then d1(c.Value) = ""
End If
Next c
If d1.Count > 0 Then Me.ComboBox3.List = d1.keys
End Sub
Private Sub ComboBox3_click()
Set d1 = CreateObject("Scripting.Dictionary")
For Each c In f.Range("D2:D" & f.[A65000].End(xlUp).Row)
If c <> "" Then
tmp = c.Offset(0, -3): If tmp = "" Then tmp = c.Offset(0, -3).End(xlUp)
tmp2 = c.Offset(0, -2): If tmp2 = "" Then tmp2 = c.Offset(0, -2).End(xlUp)
tmp3 = c.Offset(0, -1): If tmp3 = "" Then tmp3 = c.Offset(0, -1).End(xlUp)
If tmp = Me.ComboBox1 And tmp2 = Me.ComboBox2 And tmp3 = Me.ComboBox3 Then d1(c.Value) = ""
End If
Next c
If d1.Count > 0 Then Me.ComboBox4.List = d1.keys
End Sub |
Partager