Dim Plage As Range
Private Sub UserForm_Initialize()
Dim Cell As Range
With Sheets('Clients')
Set Plage = .Range(.Range('A2'), .Range('A500').End(xlUp))
End With
With Me.ComboBox1
For Each Cell In Plage
.AddItem Cell
Next
End With
End Sub
Private Sub ComboBox1_Change()
Dim Cell As Range
Dim CTRL As Variant
Dim Col As Byte
For Each Cell In Plage
If Cell = Me.ComboBox1 Then
For Each CTRL In Array('prenom', 'adresse', 'cp', 'ville', 'tel', 'email')
Col = Col + 1
Me.Controls(CTRL) = Cell.Offset(0, Col)
Next
End If
Next
End Sub