Private Sub UserForm_Initialize()
Dim Plage As Range
'sur la feuille active en colonne A à partir de A2
With ActiveSheet: Set Plage = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)): End With
'charge le ComboBox
ComboBox1.RowSource = Plage.Address
End Sub
Private Sub ComboBox1_Change()
Dim Plage As Range
Dim Cel As Range
Dim Txt As Control
Dim I As Integer
'toujours sur la feuille active
With ActiveSheet: Set Plage = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)): End With
'recherche le nom
Set Cel = Plage.Find(ComboBox1.Text, , xlValues, xlWhole)
'et inscrit les valeurs dans les TextBox
For I = 2 To 10
Me.Controls("Txb" & I).Text = Cel.Offset(, I - 1).Value
Next I
End Sub