Het werken met tabbladen in Excel is eenvoudig: je kunt ze een kleur geven (via rechtsklikken), verplaatsen (door ze met de muis “vast te pakken”), verbergen/zichtbaar maken (via rechtsklikken) etc.
Maar wat te doen, als je werkmap uit tientallen werkbladen bestaat? Dan kan het nogal wat muisklikken kosten om één van bovenstaande handelingen te verrichten.
Daarom in dit artikel enkele VBA-routines, die ons werk flink kunnen vereenvoudigen.
Overzicht van tabbladen (1)
We beginnen met het maken van een overzicht van alle tabbladen, die in een werkmap voorkomen (zie Voorbeeldbestand).
Niet alleen de naam van het tabblad plaatsen we daarin, maar ook of het tabblad verborgen is of niet, de tabkleur en de positie er van.
NB1 de kleur-code -4142 geeft aan, dat het tabblad geen kleur heeft meegekregen.
NB2 het is een goede gewoonte om tabbladen met dezelfde functie eenzelfde kleur te geven; er ontstaat dan niet zo’n kermis als in mijn voorbeeld 😉
In het tabblad Param van het Voorbeeldbestand heeft de cel met de tekst BladNamen de naam BladStart gekregen; binnen de VBA-routine zullen we daar veelvuldig naar refereren (let op de rechte haken rond een naam).
De VBA-routine BladReset genereert een nieuw overzicht:
Deze routine staat in Module1 van het VBAproject BladenVBA.xlsm. Via Alt-F11 komt u in de Visual Basic editor (dit kan natuurlijk ook via de menukeuzes Ontwikkelaars/Programmacode/Visual Basic).
Een module toevoegen doet u in de editor via de menukeuzes Invoegen/Module.
Uitleg van enkele belangrijke onderdelen van bovenstaande routine:
- na de naam van de subroutine worden 3 variabelen gedeclareerd met het commando Dim. Deze variabelen hebben we dadelijk nodig.
NB String betekent dat de variabele een tekst zal bevatten, Integer dat het een ‘gewoon’ getal is. 
om later enkele tabbladen op een aparte manier te kunnen behandelen zijn die in een Excel-tabel met de naam tblNiet opgenomen.
In vorige versies van Excel werden tabellen Lijsten genoemd; in de VBA-syntax is nog steeds sprake van een ListObject.
Wanneer we in VBA de verschillende cellen uit de tabel zonder meer in een variabele zouden plaatsen, dan zou dat in de vorm van een 2-dimensionale array zijn; via de functie Application.Transpose wordt de ingelezen kolom Niet verbergen omgezet in een 1-dimensionale array.
Met de functie Join worden de array-elementen in één tekst-variabele geplaatst met (in dit geval) als scheiding een ; (punt-komma).
NB1 vanwege lay-out-technische redenen is de totale opdracht verdeeld over 2 regels; dit door middel van een spatie en een underscore (_).
NB2 we gebruiken DataBodyRange in plaats van Range omdat we de kop van de kolom niet mee willen nemen.- voordat we een nieuw overzicht genereren moeten we een eventueel reeds bestaand overzicht verwijderen:
[BladStart].Offset(1, 0).Resize([aantBladen], 4).Clear
Vanuit de cel met de naam BladStart verschuiven (Offset) we 1 regel naar beneden. Deze selectie breiden we uit (Resize) met het aantal rijen in het bestaande overzicht; dat ligt in cel F2 van het tabblad Param vast. Deze cel heeft de naam aantBladen. De selectie moet 4 kolommen breed zijn. De inhoud van deze selectie wordt gewist (Clear). - De For-Next-lus loopt dan alle tabbladen langs. De teller i loopt van 1 tot en met het aantal werkbladen.
Eerst wordt de naam van het werkblad in rij i geplaatst (met behulp van Offset).
Standaard is een blad niet verborgen; er wordt wel gecontroleerd of dit toch het geval is. De status (wel of niet verborgen) wordt 1 kolom naar rechts in rij i vastgelegd.
Met behulp van de functie InStr wordt gecontroleerd op welke positie de sheet-naam in de uitzonderingslijst voorkomt; komt die niet voor dan is het resultaat gelijk aan 0. In dat geval wordt de huidige tabkleur weggeschreven; komt de naam wel voor in de uitzonderingslijst dan plaatsen we de code voor de kleur rood in het overzicht.
Om de routine makkelijk te kunnen uitvoeren is deze aan een knop gekoppeld:
kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen- klik dan op het eerste symbool (de Knop)
- wijs met de muis de gewenste positie van de nieuwe knop aan
- koppel in het vervolgscherm een macro/subroutine aan de knop
- wijzig de tekst van de knop
Overzicht van tabbladen (2)
Overzichten in Excel zijn handiger als ze in tabel-formaat staan (zie menig ander artikel van G-Info). Of dat ook geldt wanneer we via VBA daarmee aan de slag gaan, laat ik aan u zelf over.
Om eenzelfde overzicht als hierboven in tabel-vorm te maken, moeten we eerst de kop van de tabel aanmaken. De tabel op het tabblad Param van het Voorbeeldbestand heeft de naam tblKeus gekregen.
De bijbehorende subroutine BladReset2 lijkt veel op de voorgaande. De volgende afwijkingen behoeven een toelichting:
- het aantal regels in het bestaande overzicht laten we niet in een cel bepalen, maar we berekenen die in VBA zelf via ListRows.Count
- als er regels zijn (dus aantBladOud niet gelijk aan nul) dan worden de rijen uit de DataBodyRange verwijderd (de tabel wordt dus kleiner)
- informatie aan het overzicht toevoegen gaat nu niet via Offset, maar door een variabele nwRij te ‘setten’; deze is van het type ListRow
Tabbladen aanpassen (1)
De overzichten zoals hiervoor aangemaakt kunnen we ook andersom gebruiken: we veranderen de Verberg-status, de kleur of positie van een tabblad en laten VBA deze wijziging voor het betreffende tabblad uitvoeren.
De volgende opmerkingen:
- als het aantal bladen in de werkmap (aantBlad) niet gelijk is aan het aantal rijen in het overzicht (de cel met de naam aantBladen) dan verschijnt er een melding op het scherm en stopt de routine (Exit Sub)
LET OP de routine controleert alleen het aantal; als de namen niet overeenkomen zal de routine daarop stuk lopen. - ieder tabblad wordt eerst zichtbaar gemaakt.
Dan, als het geen uitzondering is, en in de 2e kolom van het overzicht staat JA dan wordt het betreffende tabblad verborgen.
NB de inhoud van de 2e kolom wordt omgezet naar hoofdletters (UCase, uppercase), dus ook ja, Ja of jA zullen een verberging tot gevolg hebben
Ook deze routine is aan een knop gekoppeld.
LET OP de volgorde veranderen van 1 of 2 tabbladen gaat op deze manier (meestal) zonder problemen. Bij heel veel wijzigingen kan het zijn, dat de volgorde niet meteen goed is. Herhaal de procedure dan nogmaals en ….
Tabbladen aanpassen (2)
Ook via de tabel tblKeus kunnen de aanpassingen doorgevoerd worden:
- om de routine leesbaar te houden hebben we gebruik gemaakt van de VBA-optie With-End With
With Worksheets(“Param”).ListObjects(“tblKeus”)
Als in de regels tussen With en End With een optie begint met een . (punt) dan weet VBA dat dit betrekking heeft op het ListObject tblKeus - binnen de For-Next-lus wordt nog een keer With gebruikt. Alle .Cells-verwijzingen hebben daardoor betrekking op de Range in rij i (van tblKeus).
Kleur tabbladen
Met de toewijzing .Tab.Colorindex kunnen slechts 56 kleuren gebruikt worden (nou ja eigenlijk 57; de code -4142 zorgt er voor dat het tabblad geen kleur krijgt).
In het tabblad Param van het Voorbeeldbestand staat een overzicht van codes en bijbehorende kleuren.