MAJ données

Option Explicit
[COLOR="RoyalBlue"][B]Public MaJ As Boolean
Public LgNom As Integer[/B][/COLOR]

Private Sub ListBoxContacts_Change()
    'si aucun contact n'est sélectionné dans la liste
    'Les boutons MAJ, OK et Suppression ne sont pas accessibles
    If ListBoxContacts.ListIndex = -1 Then
        CommandMAJ.Enabled = False
        CommandButtonOK.Enabled = False
        CommandButton_Supprime.Enabled = False
    'sinon ils sont accessibles
    Else
        CommandMAJ.Enabled = True
        CommandButtonOK.Enabled = True
        CommandButton_Supprime.Enabled = True
        [COLOR="RoyalBlue"][B]LgNom = ListBoxContacts.ListIndex + 2[/B][/COLOR]
    End If
End Sub

Private Sub CommandButton1_Click()
[COLOR="RoyalBlue"][B]Select Case MaJ
  Case True
   With Feuil2
    .Cells(LgNom, 1) = Societe.Value
    .Cells(LgNom, 2) = Nom.Value
    .Cells(LgNom, 3) = Prenom.Value
    .Cells(LgNom, 4) = Adresse.Value
    .Cells(LgNom, 5) = CP.Value
    .Cells(LgNom, 6) = Ville.Value
    .Cells(LgNom, 7) = Tel.Value
    .Cells(LgNom, 8) = Fax.Value
    .Cells(LgNom, 9) = Portable.Value
    .Cells(LgNom, 10) = Mail.Value
   End With
  Case False[/B][/COLOR]
    Sheets("Fournisseurs").Select
    Range("A2:J2").Select
    Selection.Insert Shift:=xlDown
    Selection.Interior.ColorIndex = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
.......
End Sub

Private Sub Pays_Change()
Dim val1 As Long
Dim col As String
Dim cellule As Range
If Pays.ListIndex = -1 Then Exit Sub
val1 = Pays.ListIndex + 2
With Sheets("Bases")
       col = Chr(val1 + 64)
       [COLOR="RoyalBlue"][B]DerniereCode = .Range(col & "2").End(xlDown).Address[/B][/COLOR]
       Code.RowSource = "Bases!B" & col & "2:" & DerniereCode
       Code.ListIndex = -1
       Code.Visible = True
End With
End Sub

Private Sub CommandButton1_Click()
Select Case MaJ
  Case True
   With Feuil2
    .Cells(LgNom[COLOR="Blue"][B] + 1[/B][/COLOR], 1) = Societe.Value
    .Cells(LgNom[COLOR="Blue"][B] + 1[/B][/COLOR], 2) = Nom.Value
    .Cells(LgNom[COLOR="Blue"][B] + 1[/B][/COLOR], 3) = Prenom.Value
    .Cells(LgNom[COLOR="Blue"][B] + 1[/B][/COLOR], 4) = Adresse.Value
    ...
    ...
    .Cells(LgNom + 1, 10) = Mail.Value
    .Cells(LgNom[COLOR="Blue"][B] + 1[/B][/COLOR], 11) = Pays.Value
    .Cells(LgNom[COLOR="Blue"][B] + 1[/B][/COLOR], 12) = Code.Value
    Unload Entree_contact
    Unload FichFournisseur
    Sheets("Menu").Select
   End With
   ...