Opgelost Dynamische Kalender

[Georgyboy]

Vrolijk kerst en fijn eindejaar voor ieder!

Probeer een Dynamische kalender te maken te beginnen met de maand Januari.
In Cel A3 staat het jaar.
De dynamische jaren staan van 2023 tot 2027.
1/01/2024 begint correct in cel C6, Foor de andere jaren loopt het fout.
Wat doe ik mis?
Is dit mogelijk Om de code ook te maken voor een volledige jaarkalender?
Alvast bedankt!

 

[snb]

De ultieme dynamische kalender

 

[Georgyboy]

Dankjewel!
Handige Kalender.
Mag ik vragen wat er fout is in onderstaande code, bij wijze van leren uit mijn fouten?

Sub VulKalenderMetDagen1611()
Dim Jaar As Integer
Dim Maand As Integer
Dim StartDatum As Date
Dim Cel As Range
Dim i As Integer
Dim Formule As String
Dim EersteDag As Integer
Dim HuidigeDatum As Date

Jaar = Range("A3").Value
Maand = 1 ' Januari

' Bereken de eerste dag van de maand
EersteDag = Weekday(DateSerial(Jaar, Maand, 1), vbMonday)

' Vul de dagen van de week in
Range("C5").Value = "Ma"
Range("D5").Value = "Di"
Range("E5").Value = "Wo"
Range("F5").Value = "Do"
Range("G5").Value = "Vr"
Range("H5").Value = "Za"
Range("I5").Value = "Zo"

' Vul de kalender met dynamische formules
For i = 0 To 41 ' 6 weken, 7 dagen per week
' Bereken de celpositie voor de huidige datum
Set Cel = Range("C6").Offset(Int((i + EersteDag - 1) / 7), (i + EersteDag - 1) Mod 7)
HuidigeDatum = DateSerial(Jaar, Maand, 1) + i - (EersteDag - 1)
If Month(HuidigeDatum) = Maand Then
Formule = "=IF(AND(YEAR(DATE($A$3," & Maand & "," & Day(HuidigeDatum) & "))=$A$3,MONTH(DATE($A$3," & Maand & "," & Day(HuidigeDatum) & "))=" & Maand & "),DATE($A$3," & Maand & "," & Day(HuidigeDatum) & "),"""")"
Cel.Formula = Formule
Else
Cel.Value = ""
End If
Next i
End Sub

Alvast bedankt,
Groeten Georgyboy

 

[snb]

Je hebt hiervoor geen VBA nodig. Hiervoor is Excel zelf handiger.
En je hebt daarvoor al veel formules in het werkblad staan.
De dagaanduiding in iedere cel lijkt me overbodig als je de dagaanduiding in de kop hebt staan

 

[Georgyboy]

Dankjewel snb,
Zou Februari wat extra aanpassing vergen?
Voor Februari heb ik deze =DATUM($A$3;2;1)-WEEKDAG(DATUM($A$3;2;1);2)+1
maar begint met 26/01
bij wijzigen laatste getal +1 naar +4 of anders is het ook niet juist.
Dank voor de aanzet!

 

[snb]

Als de maandag in kolom C moet komen komt in kolom C altijd een maandag te staan.:

Kijk eens in je agenda op welke datum de maandag is in de week waarin1 februari valt.

 

[cow18]

andere formule, maar in essentie hetzelfde, kiest de 1e maandag voor de 2e van de maand en de bestaande VO verbergt die dagen die niet in je maand vallen

 

[HSV]

Leuke materie met de nieuwste formulefuncties.

 

[Georgyboy]

Dank voor de tips
Heb proberen jullie tips te gebruiken maar maak fouten voor de volgende maanden in het jaar.
In tabblad jaarkalender loopt dit vrij goed echter de feestdagen dienen aangepast als het kan (is niet dynamisch bij jaarwisseling) ?
nieuwjaar valt altijd op 1/01 en kerstmis ook altijd op 25/12 zo ook verjaardagen.
Verlof is dan weer variabel per jaar.

@Cow 18
Kan je formule ook worden aangepast voor de volgende maanden?

@HSV
Heb je geweldige formule willen toepassen maar lukt me (nog) niet. vergt wat extra kennis voor me dit alles te begrijpen.

@snb
Dank voor je aanzet.

Hopelijk is het Excel bestand duidelijker?

Alvast bedankt!

 

[HSV]

Kijk maar eens of dit wat is (geen Vba gebruikt).

Eenvoudiger formules.
De eerste drie maanden voor je gedaan.
Consistentie aangebracht wat betreft de maand aanduiding zoals B3, J3 en R3 enz.
Voorwaardelijke opmaak gebruikt voor de dagen die niet vallen in die bewuste maand.

 

[Georgyboy]

Dankjewel HSV, denk dat het niet eenvoudiger kan?

Heb de rest aangevuld en zie nu de feestdagen (rode blokjes) overlappen in meerdere maanden.
Niet erg, maar kunnen we dat via de formule aanpassen?

 

[cow18]

mijn voorstel met feestdagen, verlofdagen en verjaardagen zonder VBA in TestCow
EDit : nieuw bestand met ook VO voor het weekend
Edit : nog een 2e update ivm 6e week in een maand

 

[HSV]

Kijk maar welke het beste bij je past.

 

[Georgyboy]

@Cow 18
Wauw, d´as straf!
Versteld van alle mogelijk oplossingen en mogelijkheden om tot zo iets te komen.
Dank @snb, HSV, Cow18.

Het was een uitdaging met zoekwerk om zoiets via VBA te doen en mijn kennis te verruimen.
Zonder VBA heb ik het geluk van jullie dit te mogen leren. De uitdaging blijft voor een stuk om het verschil te zien met of zonder vba, maar dat is nog niet voor mij. Wel via Copilot een code verworven die werkt voor januari.

PS Helpmij is nog steeds véél beter! heb al véél hulp gekregen en mogen leren

Sub VulDatumsIn()
Dim jaar As Integer
Dim maand As Integer
Dim startCel As Range
Dim datum As Date
Dim i As Integer

jaar = Range("B1").Value
maand = Range("B3").Value

' Bepaal de startcel op basis van de maand
Select Case maand
Case 1: Set startCel = Range("B5")
Case 2: Set startCel = Range("J5")
Case 3: Set startCel = Range("R5")
Case 4: Set startCel = Range("B14")
Case 5: Set startCel = Range("J14")
Case 6: Set startCel = Range("R14")
Case 7: Set startCel = Range("B23")
Case 8: Set startCel = Range("J23")
Case 9: Set startCel = Range("R23")
Case 10: Set startCel = Range("B32")
Case 11: Set startCel = Range("J32")
Case 12: Set startCel = Range("R32")
End Select

datum = DateSerial(jaar, maand, 1)

For i = 0 To 41 ' Maximaal 6 weken (42 dagen)
startCel.Offset(i \ 7, i Mod 7).Value = datum + i
Next i
End Sub

Voor de andere maanden zal deze vast blijven : "maandNaam = Range("B3").Value"

Sub VulDatumsIn2()
Dim jaar As Integer
Dim maand As Integer
Dim startCel As Range
Dim datum As Date
Dim i As Integer
Dim maandNaam As String

jaar = Range("$B$1").Value
maandNaam = Range("B3").Value

' Bepaal de cel voor de maand op basis van de maandnaam
Select Case maandNaam
Case "Januari": maand = 1
Case "Februari": maand = 2
Case "Maart": maand = 3
Case "April": maand = 4
Case "Mei": maand = 5
Case "Juni": maand = 6
Case "Juli": maand = 7
Case "Augustus": maand = 8
Case "September": maand = 9
Case "Oktober": maand = 10
Case "November": maand = 11
Case "December": maand = 12
End Select

' Bepaal de startcel op basis van de maand
Select Case maand
Case 1: Set startCel = Range("B5")
Case 2: Set startCel = Range("J5")
Case 3: Set startCel = Range("R5")
Case 4: Set startCel = Range("B14")
Case 5: Set startCel = Range("J14")
Case 6: Set startCel = Range("R14")
Case 7: Set startCel = Range("B23")
Case 8: Set startCel = Range("J23")
Case 9: Set startCel = Range("R23")
Case 10: Set startCel = Range("B32")
Case 11: Set startCel = Range("J32")
Case 12: Set startCel = Range("R32")
End Select

datum = DateSerial(jaar, maand, 1)

For i = 0 To 41 ' Maximaal 6 weken (42 dagen)
startCel.Offset(i \ 7, i Mod 7).Value = datum + i
Next i
End Sub

Sub VulDatumsIn3()
Dim jaar As Integer
Dim maand As Integer
Dim startCel As Range
Dim datum As Date
Dim i As Integer
Dim maandNaam As String

jaar = Range("$B$1").Value
maandNaam = Range("B3").Value

' Bepaal de cel voor de maand op basis van de maandnaam
Select Case maandNaam
Case "Januari": maand = 1: Set startCel = Range("B5")
Case "Februari": maand = 2: Set startCel = Range("J5")
Case "Maart": maand = 3: Set startCel = Range("R5")
Case "April": maand = 4: Set startCel = Range("B14")
Case "Mei": maand = 5: Set startCel = Range("J14")
Case "Juni": maand = 6: Set startCel = Range("R14")
Case "Juli": maand = 7: Set startCel = Range("B23")
Case "Augustus": maand = 8: Set startCel = Range("J23")
Case "September": maand = 9: Set startCel = Range("R23")
Case "Oktober": maand = 10: Set startCel = Range("B32")
Case "November": maand = 11: Set startCel = Range("J32")
Case "December": maand = 12: Set startCel = Range("R32")
End Select

datum = DateSerial(jaar, maand, 1)

' Controleer of startCel correct is ingesteld
If startCel Is Nothing Then
MsgBox "Startcel is niet ingesteld. Controleer de maandnaam in cel B3.", vbCritical
Exit Sub
End If

For i = 0 To 41 ' Maximaal 6 weken (42 dagen)
startCel.Offset(i \ 7, i Mod 7).Value = datum + i
Next i
End Sub

 

[cow18]

hallo HSV, ook mooi
@Georgyboy, anders zo ?

Sub M_Maanden()
     Dim Jaar, Maand, Maandag as long, i, j, c

     Jaar = Range("Jaar_Cow18").Value

     For i = 6 To 33 Step 9                  '1e rij van 1e, 4e, 7e en 10e maand
          For j = 1 To 3                     '1e kolom van 1e, 2e en 3e maand
               Maand = Maand + 1             'maandnr
               Set c = Sheets("Test Cow18").Cells(i, Mid("CKS", j, 1))     'linkerbovenhoek maand
               Maandag = WorksheetFunction.WorkDay_Intl(DateSerial(Jaar, Maand, 2), -1, "0111111")     'de maandag voor de 2e van die maand
               With c.Offset(-2)             '2 rijen erboven
                    .Value = WorksheetFunction.Proper(WorksheetFunction.Text(Maandag + 7, "[$-NL-NL]Mmmm"))     'maand voluit schrijven
                    .Resize(, 7).HorizontalAlignment = xlCenterAcrossSelection     'centreren
               End With
               'om "tijdelijk" de formule van januari te behouden, wordt januari voorlopig overgeslagen
               If Maand > 1 Then c.Resize(6, 7) = WorksheetFunction.Sequence(6, 7, Maandag)     'die maandag + de 41 daaropvolgende dagen in 6 rijen * 7 kolommen
          Next
     Next
End Sub

 

[Georgyboy]

Goedemorgen,

Knap gedaan @ Cow18
Zie dat het begint met de maand Februari IPV Januari.
Versteld hoe kort deze code is TOV Copilot.
Waarschijnlijk een kleine aanpassing te beginnen met Januari?

 

[cow18]

hello Georgyboy,
even de macro er op naslaan, die vette tekst schrappen ....

             'om "tijdelijk" de formule van januari te behouden, wordt januari voorlopig overgeslagen
               If Maand > 1 Then c.Resize(6, 7) = WorksheetFunction.Sequence(6, 7, Maandag)     'die maandag + de 41 daaropvolgende dagen in 6 rijen * 7 kolommen

Het antwoord van Copilot wordt hoogst waarschijnlijk beinvloed door de vraagstelling. Sequence (of reeks) bestaat nog maar sinds excel2021, dus had je dat er eventueel bijgezegd en dat hij/zij het kort moest houden ... .

 

[Georgyboy]

Hello Cow18,

Inderdaad opgelost daarmee!
weer 2 dingen kunnen bijleren van je

 

[snb]

In de Bijlage:

I dynamische maandkalender met Excelmiddelen
- 1 dynamische maandkalender in gebied A1:H8
- 1 spinknop in H1
- de waarde van de spinknop is gekoppeld aan cel A1
- alle weken beginnen met de maandag (conform het ISO-systeem)
- de enige berekening vindt plaats in cel A3
- alle overige cellen in kolom A:J zijn met Excelformules afgeleid van de waarde in cel A3
- in kolom H wordt het ISO-weeknummer berekend en getoond
- vanwege de vaste struktuur van de weekweergave zijn weekdagen lichtgrijs opgemaakt en weekenddagen iets donkerder

- de dagen die geen deel uitmaken van de maand worden via voorwaardelijke opmaak niet getoond
- in kolom AC staat een dynamische reeks van 'feest'dagen
- met voorwaardelijke opmaak worden 'feest'dagen in gebied A3:J8 oranje gemarkeerd

Samenvattend: de dynamische maandkalender in gebied A1:H8 wordt volledig met Excelmiddelen aangestuurd.

II dynamische maandkalender met vaste dagposities

- gebied A10:H16 bevat een dynamische maandkalender waarin de eerste dag van een maand altijd in cel A12 terecht komt.
- de kalender is met Excelformules gebaseerd op de waarde in cel A1
- de aanduiding van de weekdagen per kolom is daardoor ook dynamisch (zie de formules in rij 11)
- het verschil in opmaak tussen weekdagen en weekenddagen kan alleen met voorwaardelijke opmaak gerealiseerd worden.
- een ISO-weekaanduiding kan niet ingevoerd worden omdat 1 regel in 2 verschillende weken kan vallen.

III dynamische jaarkalender met Excelformules

- gebied A18:Z52 bevat een dynamische jaarkalender
- de kalender is met Excelformules gebaseerd op de waarde in cel A1
- de achtergrondopmaak is statisch, want gebaseerd op het ISO-systeem
- dagen die niet in de overeenkomstige maand vallen worden via voorwaardelijke opmaak niet getoond
- de weergave van 'feest'dagen gebeurt met voorwaardelijke opmaak
- de weeknummers staan in de kolommen H, Q en Z

IV dynamische jaarkalender met VBA

- het gebied A60:Z94 bevat een dynamische jaarkalender
- de berekening is gebaseerd op de waarde in cel A1
- de macro M_snb wordt geactiveerd via de worksheet_calculate gebeurtenis
- de macro maakt alle berekeningen in een Array en schrijft deze tenslotte naar gebied A60:Z94
- alleen voor het weeknummer is gebruik gemaakt van een Excelformule
- de markering van 'feest'dagen gaat ook in zo'n geval het best met voorwaardelijke opmaak

 

[Georgyboy]

Wat begint het jaar goed!
Dankjewel snb voor de vele extra mogelijkheden.

In de topic weer verwend geweest met vele mogelijke oplossingen.