Kleine ergernissen kunnen soms uitgroeien tot grote.

Iedereen, die regelmatig een draaitabel maakt, kan dat waarschijnlijk beamen: de draaitabel is snel gemaakt, maar dan moeten er nog diverse handelingen uitgevoerd worden om de lay-out zodanig aan te passen, dat de draaitabel er uitziet, zoals je graag wilt.
Veel van die instellingen zitten niet bij elkaar in de menu-structuur, dus is het zoeken, muizen en klikken geblazen.

Eén van die instellingen is bijvoorbeeld de rapportindeling: iemand bij Microsoft heeft bedacht,  dat de standaard-instelling daarvoor Compact moet zijn en deze standaard is niet te wijzigen.
Deze week bleek uit een vraag van een oud-collega, dat ik niet de enige ben die liever met de ‘klassieke’ indeling werkt.

In dit artikel zal ik laten zien, dat het mogelijk is om alle aanpassingen, die je vaak maakt, op één formulier te plaatsen, zodat het lastige zoeken naar de diverse menu-opties achterwege kan blijven.

Hieronder zal ik uitleggen hoe zo’n  formulier wordt opgebouwd; de gegevens, die ik als voorbeeld gebruik, de daarbij behorende draaitabel en een reeds gebouwd (uitgebreider) formulier vind je in het Voorbeeldbestand.

Formulier maken

Een formulier voor Excel wordt gemaakt in de VBA-editor.
In de editor kom je, wanneer je in de menutab Ontwikkelaars in het blok Programmacode de optie Visual Basic kiest.
Maar veel makkelijker is natuurlijk de toetscombinatie Alt-F11.

NB Is de menutab Ontwikkelaars niet zichtbaar, pas dan de Menubalk aan bij Bestand/Opties.

In de VBA-editor maken we nu als volgt een formulier met enkele voorbeeld-opties:

1. zorg in het Project-overzicht dat een Excel-map is geselecteerd door er op te klikken (heb je geen Excel-map open, ga dan terug naar Excel (Alt-F11), klik Ctrl-N en ga weer naar VBA)
2. kies in de menu-balk Invoegen en daarbinnen de optie UserForm
3. er opent zich een leeg raster (het nieuwe formulier) waarop we met behulp van de Werkset allerlei besturingselementen kunnen plaatsen
4. Links onder het Project-overzicht zie je het Eigenschappen-venster van het actieve element; nu staan daar de eigenschappen van het formulier.
   Voorlopig zijn alleen de eigenschap Name, waarmee we het formulier binnen VBA kunnen benaderen,  en de Caption (de titel bovenaan het formulier) relevant.
   De overige eigenschappen zijn vooral bedoeld om de lay-out van het formulier te beïnvloeden.
   Bij Name voeren we in UF_DT2 en bij Caption de tekst Draaitabel opmaken.
5. Nu gaan we 2 opties toevoegen waarmee we kunnen bepalen of er wel of niet rij- en kolomtotalen moeten worden weergegeven in de draaitabel:
   * klik in het grijze raster
   * sleep vanuit de Werkset het selectievakje  naar het raster op de plaats, waar de optie moet komen en herhaal dat een 2e keer (de eerste krijgt automatisch de naam Checkbox1 en de tweede Checkbox2)
   * de namen laten we zo, maar de Caption veranderen we via de Eigenschappen: de eerste krijgt wordt Rij-totalen, de tweede Kolom-totalen
6. Voor de Rapportindeling willen we drie opties aanbieden. In tegenstelling tot de opties van het vorige punt sluiten deze elkaar uit: een draaitabel kan niet tegelijkertijd in de Compact- en in de Klassieke weergave staan, terwijl we natuurlijk wel de rij- en kolom-totalen los van elkaar willen kunnen aan- en uitzetten.
   Hiervoor gebruiken we dan ook geen Selectievakjes maar Keuzerondjes:
   * sleep drie keer een keuzerondje  naar het formulier
   * verander de Caption daarvan in Compact, respectievelijk Overzicht en Classic
   * de namen van de rondjes laten we gehandhaafd (die blijven dus OptionButton 1, 2 en 3)
7. Test het formulier even:
   * druk op F5
   * klik op de diverse opties: de Selectievakjes kunnen los van elkaar aan- en uitgezet worden, terwijl er bij de Keuzerondjes altijd maar één actief is
   * het formulier doet verder nog niets; klik rechtsboven op het kruisje om te sluiten
8. Dan gaan we 2 opdrachtknoppen toevoegen:
   * sleep 2 keer een Opdrachtknop vanuit de Werkset naar het raster. Heb je te weinig plaats: maak het raster groter door de randen te verslepen of de reeds geplaatste elementen te verplaatsen.
   * de eerste opdrachtknop geven we de volgende eigenschappen:  de naam wordt ok en ook de caption wijzigen we daarin; de tweede krijgt de naam cancel en als caption Annuleren
9. Klik met de rechter muisknop op de 2e opdrachtknop en kies de optie Programmacode weergeven. Alles wat  tussen de 2 regels staat (nu nog niets!) zal worden uitgevoerd wanneer we op de Annuleer-knop klikken. Tik in de 2e (lege) regel in: unload me
   Dit is voor VBA het commando om het formulier (me) uit het geheugen te verwijderen en dus te sluiten.
   NB omdat VBA de 2 woorden herkent zullen er (na ‘Enteren’) automatisch hoofdletters tevoorschijn komen.
10. Test het formulier opnieuw:
    * dubbelklik in het projectoverzicht op UF_DT2
    * druk op F5.
    * wat doet de ok-knop? En de annuleer-knop?

NB De programmacode, die nodig is wanneer er op de OK-knop wordt geklikt, is wel wat uitgebreider. Door in het Voorbeeldbestand in de VBA-editor rechts op de ok-knop te klikken kan de programma-code bekeken worden.

LET OP heb je nog meer keuzerondjes nodig, maar moeten die niet afhankelijk zijn van de keuze van de overige rondjes, zorg dan dat ieder blok bij elkaar horende keuzerondjes in een zogenaamd Groepsvak (ook een element van de Werkset) wordt geplaatst.
In het Voorbeeldbestand zijn op die manier de instellingen voor de Rapport-indeling en voor de Veld-opmaak afzonderlijk in  te stellen.

Formulier activeren

Nu moeten we nog een mogelijkheid maken om het formulier in Excel op te roepen:

1. we gaan weer naar VBA (Alt-F11)
2. klik in de projectverkenner op de Excel-map, waarin het formulier UF_DT2 staat
3. kies in de het VBA-menu Invoegen en daarbinnen de optie Module
4. tik dan in:
   sub Start()
   UF_DT2.show
   end sub

NB2 de regel UF_DT2.Show is voldoende om het formulier te tonen. In het Voorbeeldbestand is die routine uitgebreid met programmacode, die er voor zorgt dat de keuzerondjes en selectievakjes overeenkomen met de instellingen van de draaitabel op dat moment.

NB3 heb je de macro/routine vaak nodig, dan is het handig om daar een toetscombinatie aan te koppelen:

1. ga naar de macro’s (Alt-F8)
2. klik op de macro Start
3. klik op de knop Opties
4. tik een letter in (eventueel samen met Shift) waarmee de macro opgestart moet worden (bijvoorbeeld q, want de sneltoets Ctrl-q wordt door Windows niet standaard gebruikt)
5. klik Ok en dan Annuleren (dus niet Uitvoeren!)
6. probeer de toetscombinatie uit.

Formulier altijd klaar voor gebruik

In het voorbeeld hiervoor en in het Voorbeeldbestand zijn het formulier en de diverse routines in een Excel-werkmap opgenomen en kunnen dus ook alleen gebruikt worden, wanneer deze werkmap is geopend.

Wil je altijd de beschikking hebben over formulieren of routines dan moeten die worden opgenomen in de persoonlijke, onzichtbare werkmap Personal.xlsb.
Hoe kom je aan die werkmap? Het meest eenvoudige is op de volgende manier:

1. kies in Excel in de menutab Ontwikkelaars in het blok Programmacode de optie Macro opnemen
2. Zorg dat in het middenvak de optie Persoonlijke macrowerkmap is geselecteerd
3. klik OK
4. klik dan met de muis op een willekeurige cel en stop de opname
5. ga naar de VBA-editor (Alt-F11) en verwijder in de Module1 van Personal.xlsb de zojuist opgenomen macro.

Formulier en routine kopiëren naar de persoonlijke werkmap:

1. open het Voorbeeldbestand (als dat nog niet gebeurd is)
2. ga naar de VBA-editor (Alt-F11)
3. sleep het formulier UF-DT vanuit PT.xlsm naar Personal.xlsb
4. idem met de Module1
5. sluit Excel en bevestig de vraag over het opslaan van de persoonlijke map.