Private Sub Worksheet_Change(ByVal Target As Range)
Dim celnom As Range, celprenom As Range, nom$, tablo, i&, liste$
Set celnom = [E5] 'à adapter
Set celprenom = [E9] 'à adapter
If Intersect(Target, Union(celnom, celprenom)) Is Nothing Then Exit Sub
If Target.Address = celnom.Address Then Target.Select
nom = LCase(celnom) 'minuscules
If nom <> "" Then
tablo = [Tableau1] 'matrice, plus rapide
For i = 1 To UBound(tablo)
If LCase(tablo(i, 1)) = nom Then liste = liste & "," & Application.Proper(tablo(i, 2)) 'nom propre
Next
End If
liste = Mid(liste, 2)
Application.EnableEvents = False 'désactive les évènements
With celprenom
If InStr(liste, ",") Then 'au moins 2 prénoms
If ActiveCell.Address <> .Address Or .Value = "" Then
.Select
.Value = ""
.Validation.Delete
.Validation.Add xlValidateList, Formula1:=liste
CreateObject("WScript.Shell").SendKeys "%{DOWN}" 'déroule la liste
End If
Else '0 ou 1 prénom
.Validation.Delete
.Value = liste
End If
End With
Application.EnableEvents = True 'réactive les évènements
End Sub