DefInt A-Z ' fm_CalendrierCellule avec ModClas_Calendrier
Dim BoutonJourCalendrier(1 To 42) As New ModClas_Calendrier
Private CalendrierJrsFeriesAnnee() As String
Private CalendrierDateSELECT As Date, CalAnneDEBUT, CalAnneFIN
Private Sub ButtonOk_Click()
ActiveCell = CalendrierDateSELECT: Unload Me
End Sub
Private Sub UserForm_Initialize() 'place userf au environ de la cellule
PosTop = ActiveCell.Top + (Application.Height - Application.UsableHeight) - 25
PosLeft = ActiveCell.Offset(0, 1).Left + 25
PosTopMaxi = Application.Height - Me.Height - 25
PosLeftMaxi = Application.Width - Me.Width - 25
If PosTop > PosTopMaxi Then PosTop = PosTopMaxi
If PosLeft > PosLeftMaxi Then PosLeft = PosLeftMaxi
Me.Top = PosTop: Me.Left = PosLeft
End Sub
Private Sub UserForm_Activate() 'Activate pour capter me.tag
CalAnneDEBUT = 1901: CalAnneFIN = 2199
' date d'appel sinon celle d'aujourd'hui
If IsDate(Me.Tag) Then CalendrierDateSELECT = Me.Tag Else CalendrierDateSELECT = Date
' test limite année
CalJourMIN = Day(D): CalMoisMIN = Month(D): CalAnneMIN = Year(D)
If Year(CalendrierDateSELECT) < CalAnneDEBUT Or Year(CalendrierDateSELECT) > CalAnneFIN Then
MsgBox "La Date placée dans Calendrier.Tag est invalide !?", vbCritical, "Erreur"
Unload Me: Exit Sub
End If
' init liste annee/mois
CbAnnee.Clear: For I = CalAnneDEBUT To CalAnneFIN: CbAnnee.AddItem I: Next
CbMois.Clear: For I = 1 To 12: CbMois.AddItem Choose(I, "Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre"): Next
' init date aujourd'hui
C$ = Format(Date, "dddd dd/mm/yyyy")
Mid(C$, 1, 1) = UCase(Mid(C$, 1, 1)): I = InStr(C$, " ")
LbAujourdhui.Caption = Left(C$, I - 1) & vbLf & Mid(C$, I + 1)
' Init Calendrier
CalendrierMiseAjour CalendrierDateSELECT
' Init groupe des LabelJours
Dim Ctrl As Control
For Each Ctrl In Me.CadreJours.Controls
Set BoutonJourCalendrier(Ctrl.Tag).GroupBoutonJourCalendrier = Ctrl
Next
Set Ctrl = Nothing
End Sub
' Dates
Private Sub LbAujourdhui_Click()
CalendrierMiseAjour Date
End Sub
Private Sub CbMois_Change() ' Mois .
If CbMois.Enabled = False Then Exit Sub
J = 1: M = CbMois.ListIndex + 1: If M < 1 Then M = 1
If CbAnnee.Value = CalAnneDEBUT And M <= 1 Then M = 1: J = 1: CbMois.ListIndex = M - 1
CalendrierMiseAjour J & "/" & M & "/" & CbAnnee.Value
End Sub
Private Sub CursMois_SpinDown() '<
If CbAnnee.Value = CalAnneMIN Then
If CbMois.ListIndex + 1 > CalMoisMIN Then CbMois.ListIndex = CbMois.ListIndex - 1
Else
If CbMois.ListIndex > CalMoisMIN Then
CbMois.ListIndex = CbMois.ListIndex - 1
Else
If CbAnnee.Value > CalAnneDEBUT Then
CbAnnee.Enabled = False: CbAnnee.Value = CbAnnee.Value - 1
CalendrierMiseAjour "31/12/" & CbAnnee.Value: CbAnnee.Enabled = True
End If
End If
End If
End Sub
Private Sub CursMois_SpinUp() '>
If CbMois.ListIndex < 11 Then
CbMois.ListIndex = CbMois.ListIndex + 1
Else
If CbAnnee.Value < CalAnneFIN Then
CbAnnee.Enabled = False: CbAnnee.Value = CbAnnee.Value + 1
CalendrierMiseAjour "01/01/" & CbAnnee.Value: CbAnnee.Enabled = True
End If
End If
End Sub
Private Sub CbAnnee_Change() ' Annee .
If CbAnnee.Enabled = False Then Exit Sub
J = 1: M = CbMois.ListIndex + 1: If M < 1 Then M = 1
If CbAnnee.Value = CalAnneDEBUT And M <= 1 Then M = 1: J = 1
CalendrierMiseAjour J & "/" & M & "/" & CbAnnee.Value
End Sub
Private Sub CursAnnee_SpinDown() '<
If CbAnnee.Value > CalAnneDEBUT Then CbAnnee.Value = CbAnnee.Value - 1
End Sub
Private Sub CursAnnee_SpinUp() '>
If CbAnnee.Value < CalAnneFIN Then CbAnnee.Value = CbAnnee.Value + 1
End Sub
' init calendrier
Public Sub CalendrierMiseAjour(D As Date)
Dim CaseJR As Control, DateJR As Date, DateJ1 As Date
CalendrierDateSELECT = D
MoisSelect = Month(CalendrierDateSELECT)
AnneSelect = Year(CalendrierDateSELECT)
DateJ1 = "01/" & MoisSelect & "/" & AnneSelect '1'du mois pour boucle CaseJR
J1 = Weekday(DateJ1, vbMonday) 'no du jour de semaine
If J1 = 1 Then J1 = 8 'si = lundi sauter la 1'ligne dans CadreDesJours
DateJ1 = DateJ1 - J1 'départ avant le 1'NoJour du mois(1'case jours mois précédent)
CalendrierJrsFeriesAnneeInit AnneSelect 'init jours fériés
' init listes Annee - Mois (False pour éviter répétition événement)
CbAnnee.Enabled = False: CbAnnee = AnneSelect: CbAnnee.Enabled = True
CbMois.Enabled = False: CbMois.ListIndex = MoisSelect - 1: CbMois.Enabled = True
LbNoSem1 = "": LbNoSem2 = "": LbNoSem3 = "": LbNoSem4 = "": LbNoSem5 = "": LbNoSem6 = ""
LbFerie = ""
' boucle sur les cases jours(CadreDesJours)
For Each CaseJR In CadreJours.Controls
DateJR = DateJ1 + Val(CaseJR.Tag)
NoJour = Day(DateJR): CaseJR.Caption = NoJour
NoSemISO = FCalendrierNoDeSemISO(DateJR)
Select Case Val(CaseJR.Tag)
Case 1 To 7: If LbNoSem1 = "" Then LbNoSem1 = NoSemISO
Case 8 To 14: If LbNoSem2 = "" Then LbNoSem2 = NoSemISO
Case 15 To 21: If LbNoSem3 = "" Then LbNoSem3 = NoSemISO
Case 22 To 28: If LbNoSem4 = "" Then LbNoSem4 = NoSemISO
Case 29 To 35: If LbNoSem5 = "" Then LbNoSem5 = NoSemISO
Case 36 To 42: If LbNoSem6 = "" Then LbNoSem6 = NoSemISO
End Select
'accès case jours ok si jour du mois select ok
If Month(DateJR) = MoisSelect And (AnneSelect > CalAnneDEBUT Or (AnneSelect = CalAnneDEBUT And Month(DateJR) > 1 Or Month(DateJR) = 1 And NoJour >= 1)) Then
CaseJR.SpecialEffect = fmSpecialEffectRaised: CaseJR.Enabled = True
Else
CaseJR.SpecialEffect = fmSpecialEffectEtched: CaseJR.Enabled = False
End If
If DateJR = CalendrierDateSELECT Then
CaseJR.SpecialEffect = fmSpecialEffectSunken
CaseJR.BackColor = &HFF0000 'fond bleu
CaseJR.ForeColor = &HFFFFFF 'font blanc
CaseJR.Font.Bold = True
LbFerie = CalendrierJrsFeriesAnnee(NoJour, MoisSelect)
Else
CaseJR.BackColor = &H8000000F 'fond GrisClair
CaseJR.ForeColor = &H800000 'font bleu
CaseJR.Font.Bold = False
If CaseJR.Enabled = True And CalendrierJrsFeriesAnnee(NoJour, MoisSelect) > "" Then CaseJR.BackColor = &H8080FF
End If
Next
Set CaseJR = Nothing
End Sub
' routines NoSem/Feries
Private Function FCalendrierNoDeSemISO(D As Date) 'norme ISO(Sem 4 Jrs mini)(de Renauder XLD)
T& = DateSerial(Year(D + (8 - Weekday(D)) Mod 7 - 3), 1, 1)
FCalendrierNoDeSemISO = ((D - T& - 3 + (Weekday(T&) + 1) Mod 7)) \ 7 + 1
End Function
Private Sub CalendrierJrsFeriesAnneeInit(AnneeDuCal) 'Tableau Jours Fériés(Init pour une année)
ReDim CalendrierJrsFeriesAnnee(1 To 31, 1 To 12)
'calcul dimanche de Pâques (fonction anglophone 1900-9999)
Dim DatePaque As Date, DateAscension As Date, DatePentecote As Date
'de Jean Meeus résultat idem
'A% = AnneeDuCal Mod 19: B% = AnneeDuCal \ 100: C% = AnneeDuCal Mod 100
'D% = (19 * A% + B% - (B% \ 4) - ((B% - ((B% + 8) \ 25) + 1) \ 3) + 15) Mod 30
'E% = (32 + 2 * ((B% Mod 4) + (C% \ 4)) - D% - (C% Mod 4)) Mod 7
'F% = (D% + E% - 7 * ((A% + 11 * D% + 22 * E%) \ 451) + 114)
'DatePaques = DateSerial(AnneeDuCal, F% \ 31, (F% Mod 31) + 1)
Golden% = (AnneeDuCal Mod 19) + 1: Century% = AnneeDuCal \ 100 + 1
LeapDayCorrection% = 3 * Century% \ 4 - 12
SynchWithMoon% = (8 * Century% + 5) \ 25 - 5
Sunday% = 5 * CLng(AnneeDuCal) \ 4 - LeapDayCorrection% - 10
Epact% = (11 * Golden% + 20 + SynchWithMoon% - LeapDayCorrection%) Mod 30
If Epact% < 0 Then Epact% = Epact% + 30
If Epact% = 24 Or (Epact% = 25 And Golden% > 11) Then Epact% = Epact% + 1
Jpaq% = 44 - Epact%: If Jpaq% < 21 Then Jpaq% = Jpaq% + 30
Jpaq% = Jpaq% + 7 - ((Sunday% + Jpaq%) Mod 7)
'ou remplace les 2lignes ci-dessous > DatePAQUES = DateSerial(AnneeDuCal, 3, Jpaq%)
If Jpaq% > 31 Then MPaq% = 4: Jpaq% = Jpaq% - 31 Else MPaq% = 3
DatePaque = Jpaq% & " " & MPaq% & " " & AnneeDuCal
'Date Pâques / Ascension / Pentecôte
DateAscension = DatePaque + 39: DatePentecote = DatePaque + 49
'init jours pour tableau CalendrierJrsFeriesAnnee(,)
Jasc% = Day(DateAscension): Masc% = Month(DateAscension)
Jpent% = Day(DatePentecote): Mpent% = Month(DatePentecote)
JLpaq% = Day(DatePaque + 1): MLpaq% = Month(DatePaque + 1)
JLpent% = Day(DatePentecote + 1): MLpent% = Month(DatePentecote + 1)
CalendrierJrsFeriesAnnee(1, 1) = "Nouvel AN"
CalendrierJrsFeriesAnnee(1, 5) = "Fête du Travail"
CalendrierJrsFeriesAnnee(8, 5) = "Victoire 1945"
CalendrierJrsFeriesAnnee(14, 7) = "Fête Nationale"
CalendrierJrsFeriesAnnee(15, 8) = "Assomption"
CalendrierJrsFeriesAnnee(1, 11) = "Toussaint"
CalendrierJrsFeriesAnnee(11, 11) = "Armistice 1918"
CalendrierJrsFeriesAnnee(25, 12) = "Nôel"
'deux jours fériés peuvent tomber le même jour Exp "1 Mai 2008" "Fête du Travail et Ascension"
CalendrierJrsFeriesAnnee(Jpaq%, MPaq%) = CalendrierJrsFeriesAnnee(Jpaq%, MPaq%) & " Pâque"
CalendrierJrsFeriesAnnee(JLpaq%, MLpaq%) = CalendrierJrsFeriesAnnee(JLpaq%, MLpaq%) & " Lund.Pâque"
CalendrierJrsFeriesAnnee(Jasc%, Masc%) = CalendrierJrsFeriesAnnee(Jasc%, Masc%) & " Ascension"
CalendrierJrsFeriesAnnee(Jpent%, Mpent%) = CalendrierJrsFeriesAnnee(Jpent%, Mpent%) & " Pentecôte"
CalendrierJrsFeriesAnnee(JLpent%, MLpent%) = CalendrierJrsFeriesAnnee(JLpent%, MLpent%) & " Lund.Pentecôte"
End Sub