Combobox avec toutes les options

Option Explicit
Private Tablo As Variant

Private Sub UserForm_Initialize()
Dim i As Integer
Dim ColCombo As New Collection

With Feuil2
Tablo = .Range('A2:K' & .Range('A35000').End(xlUp).Row)
End With

      For i = 1 To UBound(Tablo, 1)
          On Error Resume Next
              ColCombo.Add CStr(Tablo(i, 1)), CStr(Tablo(i, 1))
          On Error GoTo 0
      Next i

    For i = 1 To ColCombo.Count
        ComboBox1.AddItem ColCombo.Item(i)
    Next i
End Sub
Private Sub ComboBox1_Click()
Dim i As Integer
Dim ColCombo As New Collection
ComboBox2.Clear

    For i = 1 To UBound(Tablo, 1)
        If ComboBox1 = Tablo(i, 1) Then
            On Error Resume Next
                ColCombo.Add CStr(Tablo(i, 2)), CStr(Tablo(i, 2))
            On Error GoTo 0
        End If
    Next i
   
    For i = 1 To ColCombo.Count
        ComboBox2.AddItem ColCombo.Item(i)
Next i
End Sub
Private Sub ComboBox2_Change()
Dim i As Integer
Dim ColCombo As New Collection
ComboBox3.Clear

For i = 1 To UBound(Tablo, 1)
  If ComboBox1 & ComboBox2 = Tablo(i, 1) & Tablo(i, 2) Then
        On Error Resume Next
            ColCombo.Add CStr(Tablo(i, 3)), CStr(Tablo(i, 3))
        On Error GoTo 0
  End If
Next i

For i = 1 To ColCombo.Count
    ComboBox3.AddItem ColCombo.Item(i)
Next i
End Sub

Cliquez pour agrandir...