We kennen ze allemaal wel, die context-menu’s. Iedere keer als je ergens met de muis rechts-klikt krijg je een pop-up scherm met allemaal verschillende opties. Welke opties dat zijn, hangt af van wat er op dat moment op het scherm staat en/of wat je aan het doen bent. De context dus.
In Excel krijg je bijvoorbeeld een ander context-menu, wanneer je in een ‘gewone’ cel rechts-klikt of wanneer je dat doet in een draaitabel.
NB een context-menu kun je op de PC ook oproepen met behulp van de menu-toets.
Maar bij sommige spreadsheets zou het wel handig zijn om bepaalde opties, die niet standaard in het context-menu staan, wel snel tot je beschikking te hebben. Stel bijvoorbeeld, dat je iedere maand een nieuwe werkmap krijgt, die veel tekst bevat. Telkens merk je dat er spellingfouten in staan en dat er her en der veel extra spaties staan, die je weg wilt hebben. In dit geval zou het handig zijn als je via het context-menu deze zaken snel kunt regelen.
Hieronder is te zien hoe je dat met een stel VBA-programma’s klaar kunt krijgen.
Cel-context-menu
Niet alleen is het menu anders of je nu in een cel rechts-klikt of in een draaitabel, maar de context is ook anders of je in een gewone cel klikt of in een cel, die bij een Excel-tabel hoort. De context van een cel is óók anders als Excel is ingesteld op Pagina-eindevoorbeeld (kies in de menutab Beeld, in het blok Werkmapweergaven de optie Pagina-eindevoorbeeld).
Dus een cel kent drie verschillende context-menu’s; daar zullen we bij het programmeren dus rekening mee moeten houden.
NB aangezien Office voor de Mac niet helemaal gelijk is aan de Windows-versie werkt de oplossing voor een tabel-cel niet; ook bestaat Pagina-eindevoorbeeld in die versie niet.
Aanpassing cel-context-menu
In dit voorbeeld gaan we het menu dus uitbreiden met 2 opties: de spelling controleren en overbodige spaties wissen.
NB de spelling controleren is misschien wat overbodig: op een PC kan een spellingscontrole ook uitgevoerd worden met behulp van de F7-toets.
Maar het is ook maar een voorbeeld, toch?
Om aanpassingen aan de drie verschillende cel-context-menu’s flexibeler te kunnen instellen, liggen de gewenste aanpassingen in het Voorbeeldbestand vast in het tabblad Param.
In het bovenste blok kun je aangeven welk van de menu’s moet worden aangepast. Daaronder geef je het nummer op van de gewenste standaardoptie ( 2 is Spelling, 3 is Opslaan etc. Voor een volledige lijst zie het tabblad CmdIds).
Voor het verwijderen van spaties heb ik een eigen routine gemaakt met de naam SchoonSpaties (zie hieronder); deze naam geef je op in de 5e regel. Welk plaatje er in het context-menu moet komen en welke tekst wordt in de volgende regels vastgelegd.
De 7 cellen met ingestelde opties hebben allemaal een naam gekregen, die we in VBA zullen gaan gebruiken: mnuSt, mnuTbl, mnuPagEind, stOpt, nmSub, plSub respectievelijk txtSub.
De VBA-routine, die de daadwerkelijke aanpassing uitvoert, heeft de naam CelMenuAanpassen.
Deze routine/macro is gekoppeld aan de knop Context-menu aanpassen.
Hoe maak je een knop?
- klik op de menutab Ontwikkelaars (ziet u deze menutab niet? Ga naar Bestand/Opties/Lint aanpassen en zorg dat bij Hoofdtabbladen ook Ontwikkelaars is aangevinkt)
- kies in het blok Besturingselementen de optie Invoegen
- klik dan onder Formulierbesturingselementen op het eerste figuurtje
- de cursor wordt een plusje; teken daarmee de contouren van de gewenste knop
- na het tekenen krijg je de mogelijkheid om een bestaande macro aan de knop te koppelen.
Aan de tweede knop is de macro CelMenuSchonen gekoppeld; aan de derde de macro CelMenuReset.
Het verschil tussen deze twee macro’s is, dat de eerste de aanpassingen van CelMenuAanpassen terugdraait, terwijl de laatste de context-menu’s terugzet op de standaard-instellingen.
De drie routines zijn terug te vinden in de VBA-editor. Deze is op te starten via de menutab Ontwikkelaars of door op Alt-F11 te drukken.
‘Speel’ met de verschillende mogelijkheden op het tabblad Param van het Voorbeeldbestand: wijzig de instellingen en klik op de knop Context-menu aanpassen. Probeer dan het context-menu uit in de tabbladen Param, VbTbl, VbSpaties en VbSpaties (2).
Wijzig de laatste optie in: Over&bodige spaties verwijderen
(let op het &-teken)
In de Windows-versie van Excel zal in het context-menu de b worden onderstreept. Dat betekent dat je niet alleen deze optie kunt kiezen door er op te klikken maar ook door de onderstreepte letter in te tikken.
LET OP wanneer een context-menu wordt gewijzigd dan gebeurt dit voor alle geopende werkmappen van Excel. Nog sterker: pas je een menu aan, sluit je Excel en start je deze opnieuw op, dan zijn de aanpassingen nog steeds actief.
Vergeet niet dus vóór het sluiten van Excel de aanpassingen terug te draaien of te resetten!
Routine om spaties te verwijderen
De routine, die je in een context-menu wilt opnemen, moet in een module van het Voorbeeldbestand geplaatst worden. In dit geval heb ik een macro/subroutine gemaakt, die overbodige spaties verwijdert uit geselecteerde cellen (dus dubbele spatie en spaties vooraan of achteraan een tekst).
De eerste regel bevat de naam van de routine (SchoonSpaties).
De tweede en derde regel bevatten declaraties van variabelen die we in ons programma gaan gebruiken.
Het commando in de vierde regel zorgt er voor, dat als er iets mis zou gaan in regel 5, het programma gewoon verder gaat naar regel 6. Het afvangen van een mogelijke fout wordt in regel 6 weer beëindigd.
In regel 5 worden die cellen verzameld in rngAll, die in de selectie van het type Constante zijn (dus geen formules/verwijzingen etc) en dan ook nog specifiek teksten bevatten. De selectie moet al gemaakt zijn, voordat de routine is aangeroepen; dat kan met de muis of met de cursor-toetsen zijn gebeurd.
Om hierna geen foutmeldingen te krijgen, wordt in regel 7 beoordeeld of rngAll niet leeg is (de selectie bevatte geen enkele cel met daarin een tekst).
Nu we weten, dat rngAll minstens 1 tekstcel bevat, wordt in regel 8 ieder los bereik (groepje cellen) apart bekeken via de variabele rng.
In regel 9 vindt de echte bewerking plaats: alle cellen in rng worden met behulp van de functie Trim ontdaan van overbodige spaties.
Via regel 10 wordt een eventueel volgend blokje cellen opgehaald.
NB1 regel 5 zou iets compacter kunnen:
Set rngAll = Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
In sommige situaties blijkt dit echter niet goed te werken. Google wist me te vertellen, dat dit met behulp van Intersect dit op te lossen is.
NB2 ook regel 9 zou compacter kunnen:
rng.Value = Trim(rng.Value)
Maar de Trim-functie van VBA verwijdert geen dubbele spaties, dus hebben we gebruik gemaakt van de Excel-functie Trim.
Vragen over de hiervoor gebruikte routines? Neem contact op met G-Info.