Private Sub ComboBox1_Change()
Dim i&, j As Byte
If ComboBox1.ListIndex = -1 Then 'RAZ
For j = 1 To 9
Controls("TextBox" & j) = ""
Next
Exit Sub
End If
With [A3].CurrentRegion
i = Application.Match(ComboBox1, .Columns(1), 0)
For j = 1 To 9
Controls("TextBox" & j) = .Cells(i, j).Text
Next
End With
End Sub
Private Sub CommandButton1_Click() 'VALIDER
Dim i&, j As Byte, x$
If ComboBox1.ListIndex = -1 Then _
ComboBox1 = "": ComboBox1.SetFocus: Exit Sub
With [A3].CurrentRegion
i = Application.Match(ComboBox1, .Columns(1), 0)
For j = 1 To 9
x = Controls("TextBox" & j)
If IsDate(x) Then
.Cells(i, j) = CDate(x)
Else
.Cells(i, j) = UCase(x) 'majuscules
End If
Next
End With
x = UCase(TextBox1) 'mémorise
UserForm_Initialize
ComboBox1 = x
End Sub
Private Sub CommandButton2_Click() 'QUITTER
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim t, i&, n&, a$()
ComboBox1.Clear
With [A3].CurrentRegion.Resize(, 1)
If .Count = 1 Then Exit Sub
If .Count = 2 Then ComboBox1.AddItem .Cells(2): Exit Sub
t = .Offset(1).Resize(.Count - 1) 'matrice, plus rapide
End With
For i = 1 To UBound(t)
If t(i, 1) <> "" Then
n = n + 1
ReDim Preserve a(1 To n)
a(n) = t(i, 1)
End If
Next
tri a, 1, UBound(a)
ComboBox1.List = a
End Sub
Sub tri(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
Do While a(g) < ref: g = g + 1: Loop
Do While ref < a(d): d = d - 1: Loop
If g <= d Then
temp = a(g): a(g) = a(d): a(d) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub
