1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
|
Option Compare Text
Dim f, ligneEnreg, Tblclé(), tblCLIENTS()
Private Sub UserForm_Initialize()
Set f = Sheets("CLIENTS")
Tblclé = Range("A2:B" & [A65000].End(xlUp).Row).Value ' Nom+Prénom
tblCLIENTS = Range("A2:G" & [A65000].End(xlUp).Row).Value ' CLIENTS
Call Tri(Tblclé, LBound(Tblclé), UBound(Tblclé))
Me.ComboBox1.List = Tblclé
End Sub
Private Sub ComboBox1_click()
'on récupère tous les champs
For i = 1 To UBound(Tblclé)
If tblCLIENTS(i, 1) = ComboBox1.Column(0) And tblCLIENTS(i, 2) = ComboBox1.Column(1) Then
ligneEnreg = i
Me.Controls("ComboBox2") = tblCLIENTS(ligneEnreg, 1)
For k = 1 To 9
Me.Controls("TextBox" & k) = tblCLIENTS(ligneEnreg, k + 1)
Me.Controls("ComboBox5") = tblCLIENTS(ligneEnreg, 11)
Me.Controls("ComboBox3") = tblCLIENTS(ligneEnreg, 12)
Me.Controls("TextBox11") = tblCLIENTS(ligneEnreg, 13)
Me.Controls("ComboBox4") = tblCLIENTS(ligneEnreg, 14)
Me.Controls("TextBox12") = tblCLIENTS(ligneEnreg, 15)
End If
Next i
End Sub
Sub Tri(a(), gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2, 1) & a((gauc + droi) \ 2, 2)
g = gauc: d = droi
Do
Do While a(g, 1) & a(g, 2) < ref: g = g + 1: Loop
Do While ref < a(d, 1) & a(d, 2): d = d - 1: Loop
If g <= d Then
For k = LBound(a, 2) To UBound(a, 2)
temp = a(g, k): a(g, k) = a(d, k): a(d, k) = temp
Next k
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 |
Partager