Bonjour,
Une piste mais positionnes tes ComboBox dans sens logique c'est à dire de haut en bas (ComboBox1 haut dessus des autres puis le 2 et le 3) :
Private Sub UserForm_Initialize()
Dim Dico As Object
Dim Plage As Range
Dim Cel As Range
'remplissage du 1er ComboBox
'la plage est en colonne A de la feuille "Feuil2" à partire de A2
With Worksheets("Feuil2"): Set Plage = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)): End With
Set Dico = CreateObject("Scripting.Dictionary")
For Each Cel In Plage: Dico(Cel.Value) = "": Next Cel
Tri Dico
ComboBox1.List = Dico.Keys
End Sub
Private Sub ComboBox1_Click()
Dim Dico As Object
Dim Plage As Range
Dim Cel As Range
Dim Adr As String
'la plage de recherche en colonne A de la feuille "Feuil2" à partire de A2
With Worksheets("Feuil2"): Set Plage = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)): End With
Set Dico = CreateObject("Scripting.Dictionary")
'effectue la recherche du mot sélectionné et récupère la valeur située dans la colonne B
Set Cel = Plage.Find(ComboBox1.Text, , xlValues, xlWhole)
Adr = Cel.Address
Do
Dico(Cel.Offset(, 1).Value) = ""
Set Cel = Plage.FindNext(Cel)
Loop While Adr <> Cel.Address
Tri Dico
ComboBox2.Clear
ComboBox2.List = Dico.Keys
End Sub
Private Sub ComboBox2_Click()
Dim Dico As Object
Dim Plage As Range
Dim Cel As Range
Dim Adr As String
'de même que pour ComboBox2, récup des valeurs dans la colonne C
With Worksheets("Feuil2"): Set Plage = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp)): End With
Set Dico = CreateObject("Scripting.Dictionary")
Set Cel = Plage.Find(ComboBox2.Text, , xlValues, xlWhole)
Adr = Cel.Address
Do
Dico(Cel.Offset(, 1).Value) = ""
Set Cel = Plage.FindNext(Cel)
Loop While Adr <> Cel.Address
Tri Dico
ComboBox3.Clear
ComboBox3.List = Dico.Keys
End Sub
Sub Tri(Dico As Object)
Dim Tbl()
Dim Cle
Dim I As Integer, J As Integer
Dim Tempo
For Each Cle In Dico.Keys
ReDim Preserve Tbl(0 To I): Tbl(I) = Cle: I = I + 1
Next Cle
For I = 0 To UBound(Tbl): For J = I + 1 To UBound(Tbl) '- 1
If Tbl(I) > Tbl(J) Then
Tempo = Tbl(J): Tbl(J) = Tbl(I): Tbl(I) = Tempo
End If
Next J, I
Dico.RemoveAll
For I = 0 To UBound(Tbl): Dico(Tbl(I)) = "": Next I
End Sub