=DECALER(BDD!$A$2;;;NBVAL(BDD!$A:$A)-1;5)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mondico, c, temp
If Target.Column <= 5 And Target.Count = 1 Then
Set mondico = CreateObject("Scripting.Dictionary")
Select Case Target.Column
Case 1
Target.Offset(, 1) = ""
Target.Offset(, 2) = ""
Target.Offset(, 3) = ""
Target.Offset(, 4) = ""
For Each c In Application.Index([MaBD], , 1)
If Not mondico.Exists(c.Value) Then mondico.Add c.Value, c.Value
Next c
Case 2
Target.Offset(, 1) = ""
Target.Offset(, 2) = ""
Target.Offset(, 3) = ""
For Each c In Application.Index([MaBD], , 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 3
Target.Offset(, 1) = ""
Target.Offset(, 2) = ""
For Each c In Application.Index([MaBD], , 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 4
Target.Offset(, 1) = ""
For Each c In Application.Index([MaBD], , 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 5
For Each c In Application.Index([MaBD], , 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
If mondico.Count > 0 Then
If mondico.Count = 1 Then
Target = mondico.keys
Else
For Each c In mondico.items: temp = temp & c & ",": Next c
Target.Validation.Delete
Target.Validation.Add xlValidateList, Formula1:=Left(temp, Len(temp) - 1)
End If
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mondico, c, temp
If Target.Column <= 5 And Target.Count = 1 Then
Set mondico = CreateObject("Scripting.Dictionary")
Select Case Target.Column
Case 1
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([MaBD], , 1)
If Not mondico.Exists(c.Value) Then mondico.Add c.Value, c.Value
Next c
Case 2
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([MaBD], , 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 3
Target.Offset(, 1) = "": Target.Offset(, 1).Validation.Delete
Target.Offset(, 2) = "": Target.Offset(, 2).Validation.Delete
For Each c In Application.Index([MaBD], , 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 4
Target.Offset(, 1) = "": Target.Offset(, 1).Validation.Delete
For Each c In Application.Index([MaBD], , 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 5
For Each c In Application.Index([MaBD], , 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
If mondico.Count > 0 Then
Target.Validation.Delete
If mondico.Count = 1 Then
Target = mondico.keys
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
Bonsoir Art94,Bonjour, je relance cet échange car je me retrouve en difficulté... Merci déjà pour ces solutions, j'ai testé celle de David84, en l'adaptant pour une liste en cascade avec 3 niveaux. Le hic, c'est que dans la quatrième colonne de ma feuille, j'ai besoin de faire la même chose que dans la troisième, mais en reportant les résultats issus des données entrées dans les colonnes 1 et 2... En résumé : cascade sur les trois premières colonnes, puis cascade sur les colonnes 1, 2 et 4...
Est-ce quelqu'un pourrait m'aider ? Merci beaucoup