Private Sub Worksheet_Change(ByVal Target As Range)
Dim n
If Not Intersect(Target(1, 1), Range("e1")) Is Nothing Then
If Len(Trim(Target(1, 1))) > 0 Then
On Error Resume Next
n = Application.WorksheetFunction.Match(Target(1, 1), Columns("a:a"), 0)
On Error GoTo 0
If Not IsEmpty(n) Then
MsgBox "le nom: " & Target(1, 1) & vbLf & _
"figure déjà dans la colonne A !", vbCritical
Else
Cells(Rows.Count, "a").End(xlUp).Offset(1) = _
Application.WorksheetFunction.Proper(Target(1, 1))
MsgBox "le nom: " & Application.WorksheetFunction.Proper(Target(1, 1)) _
& vbLf & "a été rajoputé en fin de colonne A", vbInformation
End If
End If
End If
End Sub