Private Sub Worksheet_Activate()
Dim plg As Range, cell As Range
Dim col As New Collection, i As Integer, j As Integer, k As Integer
'vide la combobox
ComboBox1.Clear
'défini la plage de la feuille 1
Set plg = Sheets("Feuil1").Range("A1:A" & Sheets("Feuil1").Range("A65536").End(xlUp).Row)
'récupère les valeurs sans doublons
For Each cell In plg
If cell <> "" Then
On Error Resume Next
col.Add cell, CStr(cell)
On Error GoTo 0
End If
Next cell
'Tri par odre alpha de la collection
For i = 1 To col.Count
For j = 1 To col.Count
If col.Item(i) < col.Item(j) Then
temp = col.Item(i)
col.Item(i) = col.Item(j)
col.Item(j) = temp
End If
Next j
Next i
' pour chaque élément de la collection triée, remplissage de la combobox
For k = 1 To col.Count
ComboBox1.AddItem col.Item(k)
Next k
'valeur par défaut de la combobox
ComboBox1.Value = 3
End Sub