Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range, c As Range, d As Object
With Sheets("Paramètres")
Set Rng = .Range("J2:J" & .Range("J" & Rows.Count).End(xlUp).Row)
End With
If Target.Address = "$D$4" Then
Set d = CreateObject("Scripting.Dictionary")
For Each c In Rng: d(c.Value) = "": Next c
Target.Validation.Delete
Target.Validation.Add xlValidateList, Formula1:=Join(d.keys, ",")
End If
If Target.Address = "$B$13" Then
Set d = CreateObject("Scripting.Dictionary")
For Each c In Rng
' c est en col J on veut atteindre la société en col D, donc on se déplace à gauche de la colJ (soit -6)
If c.Value = Range("$D$4").Value Then d(c.Offset(0, -6).Value) = ""
Next c
If d.Count = 0 Then
MsgBox "Veuillez sélectionner Nom du RG!", vbokonky + vbCritical, "ECHEC....."
Exit Sub
Else
Target.Validation.Delete
Target.Validation.Add xlValidateList, Formula1:=Join(d.keys, ",")
End If
End If
End Sub