rooster, Gantt overzicht, planningoverzicht

[snb]

In https://www.helpmij.nl/forum/threads/rijen-koppelen-in-excel.973616/
beantwoordde een aantal helpers (o.a. Warme Bakkertje, Emields en ik) de vraag over een verlofrooster.
Een aantal helpers borduurde voort op het bestand dat TS bijvoegde.
Omdat de vraag om roosterplanningen en -overzichten wel eens vaker voorkomt leek het me verstandig daaraan een aparte draad te wijden.

In het onderstaande voorstel heb ik geprobeerd de vraag in Excel zo eenvoudig mogelijk te beantwoorden.
Daarbij wordt de gebruiker zoveel mogelijk geholpen fouten te voorkomen.
Ook heb ik geprobeerd de ingebouwde mogelijkheden van Excel optimaal te benutten (formules als benoemde gebieden, dynamische validatielijsten, draaitabel en slicers)
Voor zover ik kan beoordelen kan mijn opzet in alle Excelversies gebruikt worden.
De inzet van VBA is tot het uiterste beperkt.

Graag nodig ik leden/bezoekers van dit forum uit hun visie op mijn aanpak te geven en suggesties voor verbeteringen te doen.

De toelichting op mijn voorzet in de bijlage:

Het bestand bevat drie werkbladen: overzicht, data en lijst.

Werkblad 'overzicht'.

Tabel T_IDName

Kolom A en B bevatten de namen en ID's van werknemers.
Zet een nieuw ID in kolom A en de overeenkomstige regels in H:AL zullen zichtbaar worden.
Zet in kolom B de naam van de medewerker
Zorg dat de nieuwe ID deel uitmaakt van de tabel T_IDName
De formules in kolom C:G worden automatisch meegenomen naar de nieuwe tabelrij.

In C2:G2 staan 5 codes voor verschillende soorten verlof

In C3:G4 staat hoeveel werkdagen (exclusief weekend en feestdagen) van welk soort verlof een medewerker heeft in het jaar van cel J1 .

Deze tabel bevat 4 benoemde gebieden:
TID_IDs: de ID's in kolom A
TID_namen: de Namen in kolom B
TID_typen: de verlofsoorten in C2:G2
TID_tal: de formule voor de berekening in de kolommen C:G


Kalender

De kalender staat in I2:AL2
De eerste datum van de kalender staat in J1 en wordt weergegeven als maand jaar.
De kalender omvat 1 maand vanaf de dag in J1.
In I1 staat het weeknummer van de dag in J1; boven iedere maandag van een nieuwe week wordt het weeknummer geplaatst.

Met een klik in M1 toon je de vorige maand
Met een klik in N1 toon je de vorige week
Met een klik in O1 toon je de volgende week
Met een klik in P1 toon je de volgende maand

Daarmee bevatten I2:AL2 een in principe eeuwigdurende kalender.

De kalender bevat 7 regels voor voorwaardelijke opmaak.
De formules voor 6 regels zijn vastgelegd als een 'benoemd gebied':
weekeinden en feestdagen: cf_grijs
verlof z: cf_rood
verlof v: cf_groen
verlof a: cf_lila
verlof o: cf_blauw
verlof s: cf_geel
geen verlof zwart


Werkblad data

Tabel T_verlof

Voer iedere verloftoekenning in als een nieuw record in de tabel.
Begin met de ID van de medewerker aan de hand van gegevensvalidatie van de ID: het benoemde gebied TID_IDs
De bijbehorende naam komt automatisch in kolom B

Voer geen verlofperiodes in met een begin in een ander jaar dan het einde.
Splits zo'n verlof in een periode tot het eind van een jaar en een verlofperiode vanaf het begin van het volgende jaar.

Selecteer de begindatum uit de validatielijst die begint met vandaag en 365 dagen later eindigt: benoemd gebied L_begin.
Selecteer de einddatum uit de validatielijst: benoemd gebied L_end
Iedere einddatum is daardoor automatisch later dan de begindatum
Iedere einddatum overschrijdt daardoor nooit het einde van het jaar waarin de begindatum valt.

De kolom dagen rekent het aantal werkdagen uit exclusief weekenden en feestdagen (benoemd gebied L_feest).

Draaitabel PT_verlof

De draaitabel is opgemaakt als 'Tabular Form' zonder subtotalen.
Het veld 'Begin' is gegroepeerd op jaar.
Via de filter in de koppen kunnen allerlei selekties in de gegevens plaatsvinden.

Die filtering kan ook met slicers, waarvan 2 voorbeelden zijn toegevoegd.

Werkblad lijst

Tabel T_feest

De tabel bevat de feestdagen in het huidige jaar en de komende 2 jaren.
De tabel blijft daardoor altijd actueel.
Achter de vaste feestdagen is ruimte gelaten voor extra vaste feestdagen.
Alle berekende feestdagen op basis van de berekening van pasen staan achter de vaste feestdagen.
De data in kolom B vormen het benoemde gebied L_feest.

Kolom D

Bevat alle data vanaf vandaag tot 1 jaar later: benoemd gebied L_begin.
Dit is de validatielijst voor tabel T_verlof, veld 'begin' in werkblad 'data'.

Kolom F

Bevat alle data van de laatst ingevoerde begindatum van enig verlof in Tabel T_verlof tot het einde van het jaar van de begindatum van het verlof: benoemd gebied L_end
Dit is de validatielijst voor tabel T_verlof, veld 'eind' in werkblad 'data'.

 

[AHulpje]

"De inzet van VBA is tot het uiterste beperkt." en daar is geen woord aan gelogen!

Maar misschien dit nog even aanpassen/verwijderen:

Private Sub Workbook_Open()
    ThisWorkbook.SaveCopyAs "K:\" & ThisWorkbook.Name

    ThisWorkbook.SaveCopyAs Replace(ThisWorkbook.FullName, ".", "_res.")
End Sub

 

[snb]

@AHulpje

Dankjewel, daar heb je helemaal gelijk in. Een restant van de testperiode.
Ik ga het verwijderen.

 

[AD1957]

@snb,
Aangezien er toch nog met een weinig VBA wordt gewerkt.
Zou het dan niet gebruiksvriendelijker zijn om tabel T_verlof in te vullen dmv. een userform.
Userform openen op blad overzicht. ( nog niet volledig getest)
Userform nog een beetje aanpassen zodat er ook eenvoudig werknemers kunnen worden toegevoegd.

p.s. de kalender in het userform is er volgens mij eentje van jouzelf.
Van mijn kant alleen maar lof voor hoe je het hebt aangepakt, zou mij niet lukken.

 

[emields]

Gelukkig is hij die het zo kan oplossen, dit gaat mijn petje ver te boven.

 

[snb]

Zoals toegezegd aan @AHulpje een bestand zonder 'autoopen'macro.

Ook heb ik in werkblad 'data' de macro in worksheet_change met 1 regel kunnen reduceren.
In plaats daarvan heeft cel F1 in werkblad 'lijsten' nu een formule gekregen.

@AD1957
Ik ga je bestand bekijken en kom met een reaktie.

 

[AD1957]

In mijn bestand is Tbl_medewerkers niet correct, de gegevensvalidatie en formules
heb ik per abuis overgenomen.

 

[snb]

@AD1957

1. je kunt voor de invoer in het userform de gegevenstabel uit werkblad 'overzicht' gebruiken. een extra tabel is overbodig en leidt (zoals al blijkt) tot verwarring. Gebruik de reeds gedefinieerde benoemde gebieden:
dit is voldoende in Initialize:
Cb_naam.List = [T_IDName].Value
Cb_type.Column = [TID_typen].Value
nieuw

2. selekteer een medewerker op basis van de unieke identificatie, voornamen kunnen dubbel voorkomen.

3. velden die de gebruiker niet hoeft in te vullen, kun je beter niet als tekstvak opnemen (naam, begin, eind). De code achter het keuzevak of de kalender verzorgt dat. gebruik daarvoor 'labels', die eruitzien als tekstvakken.

4. Met een correcte tabvolgorde van userformelementen heb je geen 'setfocus'nodig.

5. de kalender kan veel eenvoudiger (ca 5 regels VBA) met een klassemodule.

6. de situatie van een keuze in de kalender als zowel begin als einde reeds zijn ingevuld moet je nog even afvangen.

 

[AD1957]

1. duidelijk, maar hoe ga je een eventuele nieuwe medewerker toevoegen ?
Ik zie daar nu geen mogelijkheid voor.
3. nooit aan gedacht, ik zal in de toekomst proberen hiermee rekening te houden.
die tekstboxen zitten er bij mij ingebakken.
4. duidelijk
5. De code is volgens mij nog een oude van jou, die met 5 regels heb ik ook al eens zien voorbijkomen.
Misschien kun/wil je deze kalender nog eens plaatsen op het forum.

Vraagje/suggestie:
Als iemand ziek is op een feestdag geld deze dag als ziek, maar dat zie ik niet gebeuren

 

[snb]

@AD1957

Nieuwe medewerkers voer je gewoon in in de tabel in werkblad 'overzicht'

Ter inspiratie