Code VBA

Option Explicit

Dim Ws As Worksheet
'pour le formulaire
Private Sub UserForm_Initialize()
Dim J As Long
    Dim I As Integer

    ComboBox2.ColumnCount = 1 'pour la liste déroulante civilité
    ComboBox2.List() = Array("", "Mr", "Melle", "dme")
    Set Ws = Sheets("clients") 'correspond au nom de l'onglet
    With Me.ComboBox1
        For J = 2 To Ws.Range("A" & Rows.Count).End(xlUp).Row
            .AddItem Range("A" & J)
        Next J
    End With
    For I = 1 To 7
        Me.Controls("textbox" & I).Visible = True
    Next I
End Sub
'pour la liste déroulante
 Private Sub combox1_change()
    Dim Ligne As Long
    Dim I As Integer
    
    If Me.ComboBox1.ListIndex = -1 Then Exit Sub
    Ligne = Me.ComboBox1.ListIndex + 2
    combox2 = Ws.Cells(Ligne, "B")
    For I = 1 To 7
        Me.Controls("textBox" & I) = Ws.Cells(Ligne, I + 2)
    Next I
End Sub
      
'Bouton nouveau contact
Private Sub commandButton1_click()
    Dim L As Integer
    If MsgBox("confirmez vous ce nouveau contact?", vbYesNo, "demande de confirmation d'ajout") = vbYes Then
          L = Sheets("clients").Range("a65536").End(xlUp).Row + 1 'pour placer l'enregistrement à la premiere ligne
          Range("A" & L).Value = ComboBox1
          Range("B" & L).Value = ComboBox2
          Range("C" & L).Value = TextBox1
          Range("D" & L).Value = TextBox2
          Range("E" & L).Value = TextBox3
          Range("F" & L).Value = TextBox4
          Range("G" & L).Value = TextBox5
          Range("H" & L).Value = TextBox6
          Range("I" & L).Value = TextBox7
      End If
End Sub
'pour le bouton modifier
Private Sub commandButoon2_click()
    Dim Ligne As Long
    Dim I As Integer
    
    If MsgBox("confirmez vous la modification?", vbYesNo, "demande de confirmation de modification") = vbYes Then
       If Me.ComboBox1.ListIndex = -1 Then Exit Sub
       Ligne = Me.ComboBox1.ListIndex + 2
       Ws.Cells(Ligne, "B") = combox2
       For I = 1 To 7
            If Me.Controls("textbox" & I).Visible = True Then
                Ws.Cells(Ligne, I + 2) = Me.Controls("textbox" & I)
            End If
     Next I
    End If
End Sub

'pour le bouton quitter
Private Sub commandeButton3_click()
   Unload Me
End Sub