Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'liste des feuilles où chercher les doublons
Const LesFeuilles = "feuil1,feuil2,feuil3,feuil4,feuil5"
Dim NomFeuil, res, n&, i&, s$, doublon As Boolean, nom$
If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target = "" Then Exit Sub
On Error Resume Next
For Each NomFeuil In Split(LesFeuilles, ",")
With Worksheets(Trim(NomFeuil))
If LCase(NomFeuil) <> LCase(Target.Parent.Name) Then
Set res = .Columns(1).Find(what:=Target.Value, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not res Is Nothing Then
nom = .Name
doublon = True: Exit For
End If
Else
If Application.CountIf(.Columns(1), Target.Value) > 1 Then
nom = .Name
doublon = True: Exit For
End If
End If
End With
Next NomFeuil
If doublon Then
s = "La valeur saisie < " & Target.Value & " > se retrouve aussi "
s = s & vbLf & vbLf & "sur la feuille : " & nom
MsgBox s, vbCritical
Target.ClearContents
End If
End Sub