Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [C5:E58]) Is Nothing Then
If Target.Count > 1 Then Exit Sub
tPrestas = Array("M", "A", "N")
tPostesG = Array("Chef de poste", "CA", "PL", "EQ/CE1", "EQ/CE2", "EQ/CE3")
lig = Application.Match(Cells(Target.Row, 1), tPostesG, 0)
If Not IsError(lig) Then
Set liste = CreateObject("scripting.dictionary")
maDate = Cells(Target.Row - lig, 1)
With Sheets("BDD")
colDate = Application.Match(CDbl(maDate), .[2:2], 0)
If IsError(colDate) Then MsgBox "Date inconnue": Exit Sub
For Each c In .Cells(3, colDate).Resize(20, 1) 'hauteur de 20 dans BDD à adapter
If c = Cells(1, Target.Column) Then
liste(.Cells(c.Row - (Application.Match(c, tPrestas, 0) - 1), 1).Value) = ""
ElseIf UCase(c) = "PRO" And Cells(4, Target.Column) = [C4].Offset(0, (c.Row - 3) Mod 3) Then
agent = IIf(.Cells(c.Row, 1) <> "", .Cells(c.Row, 1), .Cells(c.Row, 1).End(xlUp))
liste(agent) = ""
End If
Next c
For Each k In liste.keys
If Application.CountIf(Cells((Target.Row - lig) + 1, Target.Column + 5).Resize(4, 1), k) = 0 _
And Application.CountIf(Cells((Target.Row - lig) + 1, Target.Column).Resize(6, 1), k) = 0 _
Then ch = ch & k & ","
Next k
If Len(ch) > 0 Then ch = Mid(ch, 1, Len(ch) - 1)
Target.Validation.Delete
Target.Validation.Add Type:=xlValidateList, Formula1:=ch
End With
End If
End If
If Not Intersect(Target, [H5:J58]) Is Nothing Then
If Target.Count > 1 Then Exit Sub
tPrestas = Array("M", "A", "N")
tPostesA = Array("Chef de groupe", "CA", "CE/EQ", "PL")
lig = Application.Match(Cells(Target.Row, 7), tPostesA, 0)
If Not IsError(lig) Then
Set liste = CreateObject("scripting.dictionary")
maDate = Cells(Target.Row - lig, 1)
With Sheets("BDD")
colDate = Application.Match(CDbl(maDate), .[2:2], 0)
If IsError(colDate) Then MsgBox "Date inconnue": Exit Sub
For Each c In .Cells(3, colDate).Resize(20, 1) 'hauteur de 20 dans BDD à adapter
If c = Cells(1, Target.Column) Then
liste(.Cells(c.Row - (Application.Match(c, tPrestas, 0) - 1), 1).Value) = ""
ElseIf UCase(c) = "PRO" And Cells(4, Target.Column) = [C4].Offset(0, (c.Row - 3) Mod 3) Then
agent = IIf(.Cells(c.Row, 1) <> "", .Cells(c.Row, 1), .Cells(c.Row, 1).End(xlUp))
liste(agent) = ""
End If
Next c
For Each k In liste.keys
If Application.CountIf(Cells((Target.Row - lig) + 1, Target.Column - 5).Resize(6, 1), k) = 0 _
And Application.CountIf(Cells((Target.Row - lig) + 1, Target.Column).Resize(4, 1), k) = 0 _
Then ch = ch & k & ","
Next k
If Len(ch) > 0 Then ch = Mid(ch, 1, Len(ch) - 1)
Target.Validation.Delete
Target.Validation.Add Type:=xlValidateList, Formula1:=ch
End With
End If
End If
End Sub