Private Sub txtnom_AfterUpdate()
Dim var As Variant
Dim i&
'---
If WorksheetFunction.CountIf(Sheets("Clients").Range("c:c"), Me.txtnom.Value) = 0 Then
MsgBox " Ce client n'existe pas.veuillez resaisir un nouveau nom", vbInformation + vbOKOnly, "Client non trouvé"
Exit Sub
End If
With Me
var = Sheets("Clients").Range("Tableau1")
For i& = LBound(var, 1) To UBound(var, 1)
If var(i&, 3) = .txtnom Then
.txtprénom = var(i&, 4)
.txtadresse = var(i&, 5)
.txtcp = var(i&, 6)
.txtville = var(i&, 7)
'etc...
Exit For
End If
Next i&
End With
End Sub