Af en toe is het handig, dat Excel automatisch de invoer in een bepaalde cel valideert of dat er automatisch een melding komt wanneer een bepaalde cel wordt gewijzigd of …
Wat ik bedoel is: soms zou je willen, dat wanneer een bepaalde cel wordt gewijzigd, er dan automatisch een actie wordt getriggerd.
Validaties kunnen vaak eenvoudig met de optie Gegevensvalidatie worden uitgevoerd; andere routines kunnen met behulp van VBA (eventueel door een macro op te nemen) worden geprogrammeerd.
Maar hoe zorg je er voor dat zo’n routine automatisch opstart?
In dit artikel zal ik aan de hand van enkele eenvoudige voorbeelden laten zien hoe dit in zijn werk gaat.
Invoer valideren
In het Voorbeeldbestand op het tabblad Vb1 staat een klein tabelletje, waarmee het kwadraat van een ingevoerd getal wordt berekend.
Wanneer in cel C2 (met de naam Invoer) een getal wordt ingetikt, dan wordt in cel C3 het kwadraat bepaald. Maar ook start er een VBA-routine die een controle uitvoert en wordt er een pop-up-scherm getoond, waarvan de inhoud afhankelijk is van het controle-resultaat.
NB de cellen C2, C3 en C4 hebben een naam gekregen: selecteer het bereik B2:C4, kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie; zorg dat alleen Linkerkolom staat aangevinkt.
- start de VBA-editor via het tabblad Ontwikkelaars of Alt-F11
- dubbelklik in de Projectverkenner op Blad1 (Vb1)
- daar staat de volgende routine:
Door de naam van de subroutine (Worksheet_Change) zal Excel deze routine automatisch opstarten als er ergens in dit werkblad (Vb1 dus) iets wordt gewijzigd. Welke cel(len) er is/zijn gewijzigd wordt dan via de variabele Target doorgegeven.
Allereerst tellen we het aantal cellen in Target; als dat niet gelijk is aan 1, dan zijn we klaar.
Dan testen we of het adres (de celverwijzing) van Target gelijk is aan die van Invoer; zo niet dan zijn we ook klaar.
Via Int([Invoer]) bepalen we het Integer-gedeelte (het gedeelte vóór de decimale komma) van de cel Invoer en kijken of dat gelijk is aan de Invoer zelf; zo niet dan zorgen we via Msgbox dat er een pop-up verschijnt met die melding.
Is de invoer wel een geheel getal, dan komt er een Msgbox, die laat zien wat er is ingevoerd en wat het resultaat in de cel Uitvoer is. Wanneer op OK wordt geklikt in de pop-up, dan wordt de cel met de naam VBA_result gevuld met het kwadraat van Invoer.
NB1 Denk aan de vierkante haken om de naam van een cel(-bereik). Anders ziet VBA dat als een interne variabele.
NB2 Via Format zorgen we er voor, dat bij grote getallen ieder blokje van 3 cijfers gescheiden wordt. Aangezien de VBA-editor Engelstalig is, krijgen we als scheiding een komma; met het commando Replace vervangen we die door een punt.
NB3 verschillende stukken teksten koppelen we aan elkaar met het &-teken (zorg dat er spaties omheen staan); vbCrLf zorgt er voor, dat de rest van de tekst op een volgende regel komt. De naam van de code (Carriage Return, Linefeed; wagen terug en 1 regel naar beneden) stamt nog uit het typemachine-tijdperk!.
NB4 met de spatie en het _-teken (underscore) kun je in de VBA-editor lange regels in ‘stukken knippen’; Excel weet dan dat ze bij elkaar horen.
Draaitabel vernieuwen
In het tabblad Vb2 van het Voorbeeldbestand staan een (simpele) Excel-tabel (met de naam tblData2), een draaitabel, die daarop gebaseerd is, en een controle-blokje. Wanneer in dat laatste gebied de inhoud van cel F2 gelijk wordt aan Ja (via gegevensvalidatie zijn alleen Nee en Ja toegestaan), dan wordt de draaitabel automatisch vernieuwd.
De VBA-routine, die deze klus voor zijn rekening neemt, vindt u in de VBA-projectverkenner in het object Blad2 (Vb2). Vergeet niet te dubbelklikken!
Waarschijnlijk behoeft de routine geen verdere uitleg; hij lijkt veel op die in het tabblad Vb1.
NB cel F3 wordt met behulp van de functie DRAAITABEL.OPHALEN gevuld; zie ook het artikel over Draaitabel.Ophalen.
Draaitabel vernieuwen 2
De 2 VBA-routines uit Vb1 en Vb2 zijn redelijk rechttoe-rechtaan geprogrammeerd; om ze makkelijker te kunnen begrijpen zijn niet alle officiële regels gehanteerd en zijn er weinig fout-controles ingebouwd.
Het voorbeeld in tabblad Vb3 is al iets netter: het vernieuwen van de draaitabel is in een aparte subroutine opgenomen (met de naam DraaiVernieuwen).
- start de VBA-editor (Alt-F11)
- kies in het VBA/menu Invoegen/Module
- en plaats in de nieuwe Module de volgende routine:
LET OP in Vb2 werd de draaitabel (in het Engels Pivottable) met de naam Draaitabel1 vernieuwd. Deze routine vernieuwt de draaitabel met het volgnummer 1 in het actieve werkblad.
De nieuwe subroutine kan ook op andere plaatsen en op andere manieren worden aangeroepen. In Vb3 is een knop gemaakt, die de routine aanroept.
Klik rechts op de knop en kies Macro toewijzen.
NB alleen macro’s/subroutines die in een module zijn opgenomen kunnen aan een knop worden toegewezen; vandaar dat de routine DraaiVernieuwen niet in het object Vb3 staat.
Draaitabel vernieuwen bij wijzigen bron
Maar het zou natuurlijk nog mooier zijn als de draaitabel automatisch zou worden vernieuwd bij het wijzigen van de bron-gegevens (waaronder ook wordt verstaan het toevoegen van nieuwe gegevens).
In het Voorbeeldbestand is in het tabblad Vb4 een nieuwe Excel-tabel opgenomen (met de naam tblData4) en een daarop gebaseerde draaitabel.
Verander je de Soort of het Aantal in een bestaande regel dan start automatisch de VBA-routine in het object Blad4 (Vb4). Beter gezegd: de routine start bij iedere wijziging van een cel in dit werkblad, maar er wordt slechts daadwerkelijk iets uitgevoerd als een cel in tblData4 wordt gewijzigd.
Omdat in regel 3 de Target maar 1 cel kan omvatten kun je die niet zomaar met tblData4 vergelijken. We willen weten of die ene Target-cel in tblData4 voorkomt.
De VBA-functie Intersect bepaalt wat de overlap tussen de twee bereiken is. Is die overlap leeg dan is Intersect gelijk aan Nothing, dus als die Not Nothing is dan is er wel overlap.
NB1 de truc met Not Nothing kom je op veel plaatsen op Internet tegen en is algemeen toepasbaar voor allerlei soorten ranges. In dit geval is de Target maar 1 cel; we kunnen regel 3 dan vervangen door een meer simpele constructie:
If Intersect(Target, [tblData4]) = Target Then
NB2 de Msgbox levert nu wel een vreemd resultaat; toch niet goed over de routine DraaiVernieuwen nagedacht, tja!
Hoi,
Ik probeer code uit te voeren die “getrigged” moet worden door een verandering in Cel C3 van de sheet “Test”. Deze cel heb ik ook “Omzet500k” genoemd.
Ik probeer het op twee manieren als volgt:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [Omzet500k].Address Then
MsgBox (“Looks good, man”)
Else
MsgBox (“Another field has changed”)
End If
If Target(“C3”).Value = “Ja” Then
MsgBox (“Waarde is Ja”)
Else
MsgBox (“Waarde is niet Ja”)
End If
End Sub
Ik verwacht twee messages. Helaas, er verschijnt geen enkele message, welk veld ik ook verander; het lijkt erop dat de Macro niet wordt getriggered door verandering in de sheet.
Andere macro’s op de sheet, die via een button worden getriggered, doe het wel.
Moet ik nog iets doen om deze macro te “activeren”?
Groeten en dank.
Beste Dhiradj,
zou het kunnen zijn, dat je de routine in een Module hebt geplaatst?
Zoals de naam van de routine al aangeeft moet deze geplaatst worden in het code-gedeelte van het tabblad/sheet waar het betrekkeing op moet hebben (in jouw geval “Test”).
De eerste manier moet werken, de tweede moet je snel vergeten.
Goedenavond,
Ik ben bezig met een plan programma, voor personeelsplanning.
Nu heb ik een vraag.
Ik heb meerdere cellen dezelfde naam gegeven. Deze naam gebruik ik in vba.
Als ik een cel een naam geef, doet de macro het wel als ik deze wijzig.
Maar geef ik meerdere cellen dezelfde naam, dan doet hij het niet. Waar zou dat aan kunnen liggen ?
Alvast bedankt voor uw tijd?
Dag Ben,
Als je dezelfde naam gebruikt dan zal Excel deze namen onderscheiden door ze te koppelen aan het betreffende tabblad.
Afhankelijk van hoe je in VBA programmeert is dan niet altijd duidelijk welke naam in een berekening wordt meegenomen.
Het is daarom aan te raden (maar ook voor jezelf om het overzicht te bewaren) om niet dezelfde namen te gebruiken.
Ik vind dit een zeer informatieve, netjes weergegeven, duidelijke website met goede (en werkende) voorbeeldbestanden.
Vooral de VBA gerelateerde zaken hebben mijn aandacht
Ik ben hier zeer tevreden over.
Joop, hartelijk dank voor de leuke reactie. Ik zal binnenkort nog maar eens wat handige VBA-routines bedenken.