1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
| Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim C As Range, Plage As Range, Tabl(), Ctr As Integer, Txt As String
Dim X As Range, I As Integer, Tabl2(), Sem As Variant
Dim HeurDeb As Date, HeurFin As Date
Sem = Array("LUNDI", "MARDI", "MERCREDI", "JEUDI", "VENDREDI", "SAMEDI", "DIMANCHE")
ReDim Tabl2(0)
Ctr = -1
If Not IsNumeric(Application.Match(Sh.Name, Sem, 0)) Then Exit Sub
Application.EnableEvents = False
With Sh
ReDim Tabl(Application.CountA(.Range("B27:R28")) - 1)
For Each C In .Range("B27:R28")
If C.Value <> "" Then
Ctr = Ctr + 1
Tabl(Ctr) = C.Value
End If
Next C
Set Plage = Union(.Range("B6", .Cells(5, 2).End(xlDown)), .Range("H6", .Cells(5, 8).End(xlDown)), _
.Range("N6", .Cells(5, 14).End(xlDown))).Offset(, 4)
If Intersect(Plage, Target) Is Nothing Then Exit Sub
HeurDeb = Target.Offset(, -3)
HeurFin = Target.Offset(, -2)
For Each C In Plage
Txt = ""
Erase Tabl2
ReDim Tabl2(0)
Ctr = -1
For Each X In Plage
If (HeurDeb >= X.Offset(, -3) And HeurDeb < X.Offset(, -2)) Or _
(HeurFin >= X.Offset(, -3) And HeurFin < X.Offset(, -2)) And X.Value <> "" Then
Ctr = Ctr + 1
ReDim Preserve Tabl2(Ctr)
Tabl2(Ctr) = X.Value
End If
Next X
If Tabl2(0) = "" Then
For I = 0 To UBound(Tabl)
Txt = Txt & "," & Tabl(I)
Next I
Else
For I = 0 To UBound(Tabl)
If Not IsNumeric(Application.Match(Tabl(I), Tabl2, 0)) Then
Txt = Txt & "," & Tabl(I)
End If
Next I
End If
Txt = Right(Txt, Len(Txt) - 1)
C.Validation.Delete
C.Validation.Add xlValidateList, , , Txt
Next C
End With
Application.EnableEvents = True
End Sub |
Partager