Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mondico, c, temp, BD As Range
' Créer la Base de Données en fonction de sa taille
With Sheets("BDD")
Set BD = .[A2].Resize(.[A2].CurrentRegion.Rows.Count - 1, _
.[A2].CurrentRegion.Columns.Count)
End With
' Vérifier qu'on se trouve dans la zone souhaitée
If Target.Column >= Columns("FJ").Column _
And Target.Column <= Columns("J").Column _
And Target.Count = 1 Then
' Créer une instance de dictionnaire
Set mondico = CreateObject("Scripting.Dictionary")
' Selon la colonne sélectionnée
Select Case Target.Column
' On crée la liste déroulante voulu
Case Columns("F").Column
Target.Offset(, 1) = "": Target.Offset(, 1).Validation.Delete
Target.Offset(, 2) = "": Target.Offset(, 2).Validation.Delete
Target.Offset(, 3) = "": Target.Offset(, 3).Validation.Delete
Target.Offset(, 4) = "": Target.Offset(, 4).Validation.Delete
For Each c In Application.Index(BD, , 1)
If Not mondico.Exists(c.Value) Then mondico.Add c.Value, c.Value
Next c
Case Columns("G").Column
Target.Offset(, 1) = "": Target.Offset(, 1).Validation.Delete
Target.Offset(, 2) = "": Target.Offset(, 2).Validation.Delete
Target.Offset(, 3) = "": Target.Offset(, 3).Validation.Delete
For Each c In Application.Index(BD, , 2)
If Not mondico.Exists(c.Value) And c.Offset(0, -1) = Target.Offset(0, -1) Then
mondico.Add c.Value, c.Value
End If
Next c
Case Columns("H").Column
Target.Offset(, 1) = "": Target.Offset(, 1).Validation.Delete
Target.Offset(, 2) = "": Target.Offset(, 2).Validation.Delete
For Each c In Application.Index(BD, , 3)
If Not mondico.Exists(c.Value) And c.Offset(0, -1) = Target.Offset(0, -1) And _
c.Offset(0, -2) = Target.Offset(0, -2) Then
mondico.Add c.Value, c.Value
End If
Next c
Case Columns("I").Column
Target.Offset(, 1) = "": Target.Offset(, 1).Validation.Delete
For Each c In Application.Index(BD, , 4)
If Not mondico.Exists(c.Value) And c.Offset(0, -1) = Target.Offset(0, -1) And _
c.Offset(0, -2) = Target.Offset(0, -2) And c.Offset(0, -3) = Target.Offset(0, -3) Then
mondico.Add c.Value, c.Value
End If
Next c
Case Columns("J").Column
For Each c In Application.Index(BD, , 5)
If Not mondico.Exists(c.Value) And _
c.Offset(0, -1) = Target.Offset(0, -1) And _
c.Offset(0, -2) = Target.Offset(0, -2) And c.Offset(0, -3) = Target.Offset(0, -3) And _
c.Offset(0, -4) = Target.Offset(0, -4) Then
mondico.Add c.Value, c.Value
End If
Next c
End Select
' Si le dictionnaire n'est pas vide
If mondico.Count > 0 Then
' On crée la validation de donnée
Target.Validation.Delete
If mondico.Count = 1 Then
Target = mondico.keys
If Target.Column < Columns("J").Column Then Target.Offset(0, 1).Select
Else
For Each c In mondico.items: temp = temp & c & ",": Next c
Target.Validation.Add xlValidateList, Formula1:=Left(temp, Len(temp) - 1)
End If
End If
End If
End Sub
