Salut Laura,
Salut les as,
une chose que j'aurais implémenté : des listes de validation excluant les professions déjà listées.
Condition : que la BDD en 'DATA' soit triée...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim iRow%, sItem$, sData$
'
'Construction de la liste de validation
If Not Intersect(Target, Columns(1)) Is Nothing And Target.Row = Range("B" & Rows.Count).End(xlUp).Row + 1 Then
Cells.Validation.Delete
With Worksheets("DATA")
If .[A3] <> "" Then _
.Range("A1").CurrentRegion.Sort _
key1:=.[A2], order1:=xlAscending, _
key2:=.[B2], order2:=xlAscending, _
key3:=.[C2], order3:=xlAscending, _
Orientation:=xlTopToBottom, Header:=xlYes
If .[A2] <> "" Then
For iRow = 2 To .Range("A" & Rows.Count).End(xlUp).Row
sData = .Range("A" & iRow).Value
If WorksheetFunction.CountIf(Columns(1), sData) = 0 Then sItem = sItem & IIf(sItem = "", "", ",") & sData
iRow = .Columns(1).Find(what:=sData, lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlPrevious).Row
Next
End If
End With
If sItem = "" Then
MsgBox "! Toutes les professions sont déjà listées !", vbInformation + vbOKOnly, "Info"
Else
Target.Validation.Add Type:=xlValidateList, Formula1:=sItem
End If
End If
'
End Sub
A+