Option Compare Text 'la casse est ignorée
Private Sub ComboBox1_GotFocus()
Dim r As Range
With ComboBox1
.Clear
.Value = ""
Set r = Range("A2:A" & Rows.Count)
If Application.CountA(r) = 0 Then Exit Sub 'sécurité
For Each r In r.SpecialCells(xlCellTypeConstants)
.AddItem r & IIf(r(1, 3) = "", "", " " & r(1, 3))
Next
If .ListCount = 1 Then Exit Sub
Application.ScreenUpdating = False
With [D1].Resize(.ListCount) 'colonne auxiliaire
.Value = ComboBox1.List
.Sort .Cells, xlAscending, Header:=xlNo 'tri alphabétique
ComboBox1.List = .Value
.ClearContents
End With
End With
End Sub
Private Sub ComboBox1_Change()
Dim r As Range, x As String
[H7] = "" 'RAZ
Set r = Range("A2:A" & Rows.Count)
If ComboBox1.ListIndex = -1 Or Application.CountA(r) = 0 Then ComboBox1 = "": Exit Sub
x = ComboBox1
For Each r In r.SpecialCells(xlCellTypeConstants)
If r & IIf(r(1, 3) = "", "", " " & r(1, 3)) = x Then [H7] = r(1, 2): Exit For
Next
End Sub