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 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| Option Explicit
Dim DernLigne As Long
Private Sub UserForm_Initialize()
Dim i As Long
' On Error Resume Next
DernLigne = Range("B" & Rows.Count).End(xlUp).Row
ComboBoxPrinci.Clear
For i = 3 To DernLigne
ComboBoxPrinci.AddItem Range("B" & i).Value
Next i
End Sub
Private Sub ComboBoxPrinci_Change()
Dim i As Long
Me.ComboBox2.Clear
Me.ComboBox3.Clear
Me.ComboBox4.Clear
Dim dico2 As Scripting.Dictionary
Set dico2 = CreateObject("Scripting.Dictionary")
Dim dico3 As Scripting.Dictionary
Set dico3 = CreateObject("Scripting.Dictionary")
Dim dico4 As Scripting.Dictionary
Set dico4 = CreateObject("Scripting.dictionary")
With Feuil1
For i = 3 To DernLigne
If .Cells(i, 2).Text = Me.ComboBoxPrinci.Value Then
Select Case True
Case .Cells(i, 4).Value <> ""
If Not dico2.Exists(.Cells(i, 4).Value) Then
dico2.Add .Cells(i, 4).Value, .Cells(i, 4).Value
Me.ComboBox2.AddItem .Cells(i, 4).Value
End If
Case .Cells(i, 5).Value <> "" And .Cells(i, 6).Value <> ""
If Not dico3.Exists(.Cells(i, 5).Value) Then
dico3.Add .Cells(i, 5).Value, .Cells(i, 5).Value
Me.ComboBox3.AddItem .Cells(i, 5).Value
End If
If Not dico4.Exists(.Cells(i, 6).Value) Then
dico4.Add .Cells(i, 6).Value, .Cells(i, 6).Value
Me.ComboBox4.AddItem .Cells(i, 6).Value
End If
End Select
End If
Next i
End With
dico4.RemoveAll
Set dico4 = Nothing
dico3.RemoveAll
Set dico3 = Nothing
dico2.RemoveAll
Set dico2 = Nothing
End Sub |
Partager