Private Sub Worksheet_Change(ByVal Target As Range)
With Application: .EnableEvents = False: .Calculation = xlCalculationManual: End With
Select Case Target.Address
Case "$B$2":
If IsError(Me.[B1].Value) Then
With Feuil2.[nom]: .Rows(.Rows.Count + 1).Value = Target.Value: End With
Me.[B3:B5].Value = "?": Me.[B3].Select
MsgBox "Veuillez saisir les autres informations.", vbInformation, "Nouveau nom entré"
Else
If Not Me.[B3].HasFormula Then Me.[B3].Formula = "=INDEX(adresse,$B$1)"
If Not Me.[B4].HasFormula Then Me.[B4].Formula = "=INDEX(code,$B$1)"
If Not Me.[B5].HasFormula Then Me.[B5].Formula = "=INDEX(commune,$B$1)"
End If
Case "$B$3": Feuil2.[adresse].Rows(Me.[B1].Value) = Target.Value: Target.Formula = "=INDEX(adresse,$B$1)"
Case "$B$4": Feuil2.[code].Rows(Me.[B1].Value) = Target.Value: Target.Formula = "=INDEX(code,$B$1)"
Case "$B$5": Feuil2.[commune].Rows(Me.[B1].Value) = Target.Value: Target.Formula = "=INDEX(commune,$B$1)"
End Select
With Application: .EnableEvents = True: .Calculation = xlCalculationAutomatic: End With
End Sub