Excel VBA-evenementen - Een eenvoudige (en complete) gids

Wanneer u een macro in Excel maakt of opneemt, moet u de macro uitvoeren om de stappen in de code uit te voeren.

Een paar manieren om een ​​macro uit te voeren, zijn onder meer het gebruik van het macro-dialoogvenster, het toewijzen van de macro aan een knop, het gebruik van een snelkoppeling, enz.

Afgezien van deze door de gebruiker geïnitieerde macro-uitvoeringen, kunt u ook VBA-gebeurtenissen gebruiken om de macro uit te voeren.

Excel VBA-evenementen - Inleiding

Laat me eerst uitleggen wat een gebeurtenis is in VBA.

Een gebeurtenis is een actie die de uitvoering van de opgegeven macro kan activeren.

Wanneer u bijvoorbeeld een nieuwe werkmap opent, is dit een gebeurtenis. Wanneer u een nieuw werkblad invoegt, is dit een gebeurtenis. Wanneer u dubbelklikt op een cel, is het een gebeurtenis.

Er zijn veel van dergelijke gebeurtenissen in VBA en u kunt codes voor deze gebeurtenissen maken. Dit betekent dat zodra een gebeurtenis plaatsvindt, en als u een code voor die gebeurtenis hebt opgegeven, die code onmiddellijk wordt uitgevoerd.

Excel doet dit automatisch zodra het merkt dat er een gebeurtenis heeft plaatsgevonden. U hoeft dus alleen de code te schrijven en in de juiste gebeurtenissubroutine te plaatsen (dit wordt verderop in dit artikel besproken).

Als u bijvoorbeeld een nieuw werkblad invoegt en u wilt dat het een jaarvoorvoegsel heeft, kunt u de code ervoor schrijven.

Wanneer iemand nu een nieuw werkblad invoegt, wordt deze code automatisch uitgevoerd en wordt het jaarprefix aan de naam van het werkblad toegevoegd.

Een ander voorbeeld kan zijn dat u de kleur van de cel wilt wijzigen wanneer iemand erop dubbelklikt. U kunt hiervoor de dubbelklikgebeurtenis gebruiken.

Op dezelfde manier kunt u VBA-codes maken voor veel van dergelijke evenementen (zoals we later in dit artikel zullen zien).

Hieronder ziet u een korte visual die de dubbelklikgebeurtenis in actie laat zien. Zodra ik dubbelklik op cel A1. Excel opent onmiddellijk een berichtvenster met het adres van de cel.

Dubbelklik is een gebeurtenis en het weergeven van het berichtvenster is wat ik in de code heb gespecificeerd wanneer de dubbelklikgebeurtenis plaatsvindt.

Hoewel het bovenstaande voorbeeld een nutteloze gebeurtenis is, hoop ik dat het u helpt te begrijpen wat gebeurtenissen werkelijk zijn.

Verschillende soorten Excel VBA-gebeurtenissen

Er zijn verschillende objecten in Excel - zoals Excel zelf (waar we vaak naar verwijzen als de applicatie), werkmappen, werkbladen, grafieken, enz.

Aan elk van deze objecten kunnen verschillende gebeurtenissen zijn gekoppeld. Bijvoorbeeld:

• Als u een nieuwe werkmap maakt, is dit een gebeurtenis op toepassingsniveau.
• Als u een nieuw werkblad toevoegt, is dit een gebeurtenis op werkmapniveau.
• Als u de waarde in een cel in een blad wijzigt, is dit een gebeurtenis op werkbladniveau.

Hieronder staan ​​de verschillende soorten gebeurtenissen die in Excel voorkomen:

1. Evenementen op werkbladniveau: Dit zijn de soorten gebeurtenissen die zouden worden geactiveerd op basis van de acties die in het werkblad zijn uitgevoerd. Voorbeelden van deze gebeurtenissen zijn het wijzigen van een cel in het werkblad, het wijzigen van de selectie, dubbelklikken op een cel, rechtsklikken op een cel, enz.
2. Evenementen op werkmapniveau: Deze gebeurtenissen worden geactiveerd op basis van de acties op werkmapniveau. Voorbeelden van deze gebeurtenissen zijn het toevoegen van een nieuw werkblad, het opslaan van de werkmap, het openen van de werkmap, het afdrukken van een deel of de hele werkmap, enz.
3. Gebeurtenissen op applicatieniveau: Dit zijn de gebeurtenissen die plaatsvinden in de Excel-toepassing. Een voorbeeld hiervan is het sluiten van een van de geopende werkmappen of het openen van een nieuwe werkmap.
4. Gebeurtenissen op gebruikersformulierniveau: Deze gebeurtenissen worden geactiveerd op basis van de acties in het 'UserForm'. Voorbeelden hiervan zijn het initialiseren van een UserForm of het klikken op een knop in het UserForm.
5. Grafiekgebeurtenissen: Dit zijn gebeurtenissen die verband houden met het kaartblad. Een grafiekblad is anders dan een werkblad (waar de meesten van ons gewend zijn om in Excel te werken). Het doel van een kaartblad is om een ​​kaart bij te houden. Voorbeelden van dergelijke gebeurtenissen zijn het wijzigen van de reeks van de grafiek of het wijzigen van de grootte van de grafiek.
6. OnTime en OnKey-gebeurtenissen: Dit zijn twee evenementen die niet in een van de bovenstaande categorieën passen. Deze heb ik dus apart vermeld. Met de 'OnTime'-gebeurtenis kunt u een code uitvoeren op een specifiek tijdstip of nadat een specifieke tijd is verstreken. Met de 'OnKey'-gebeurtenis kunt u een code uitvoeren wanneer een specifieke toetsaanslag (of een combinatie van toetsaanslagen) wordt gebruikt.

Waar plaats je de gebeurtenisgerelateerde code?

In het bovenstaande gedeelte heb ik de verschillende soorten evenementen behandeld.

Op basis van het type gebeurtenis moet u de code in het relevante object plaatsen.

Als het bijvoorbeeld een werkbladgerelateerde gebeurtenis is, moet deze in het codevenster van het werkbladobject komen. Als het werkmapgerelateerd is, zou het in het codevenster voor een werkmapobject moeten gaan.

In VBA hebben verschillende objecten - zoals werkbladen, werkmappen, grafiekbladen, gebruikersformulieren, enz. hun eigen codevensters. U moet de gebeurteniscode in het codevenster van het relevante object plaatsen. Als het bijvoorbeeld een gebeurtenis op werkmapniveau is, moet u de gebeurteniscode in het venster Werkmapcode hebben.

De volgende secties behandelen de plaatsen waar u de gebeurteniscode kunt plaatsen:

In werkbladcodevenster

Wanneer u de VB Editor opent (met de sneltoets ALT + F11), ziet u het werkbladobject in de Projectverkenner. Voor elk werkblad in de werkmap ziet u één object.

Als u dubbelklikt op het werkbladobject waarin u de code wilt plaatsen, wordt het codevenster voor dat werkblad geopend.

Hoewel je de code helemaal opnieuw kunt schrijven, is het veel beter om de gebeurtenis uit een lijst met opties te selecteren en VBA automatisch de relevante code voor de geselecteerde gebeurtenis te laten invoegen.

Om dit te doen, moet u eerst het werkblad selecteren in de vervolgkeuzelijst linksboven in het codevenster.

Nadat u het werkblad in de vervolgkeuzelijst hebt geselecteerd, krijgt u een lijst met alle gebeurtenissen met betrekking tot het werkblad. U kunt degene die u wilt gebruiken selecteren in de vervolgkeuzelijst rechtsboven in het codevenster.

Zodra u de gebeurtenis selecteert, wordt automatisch de eerste en laatste regel van de code voor de geselecteerde gebeurtenis ingevoerd. Nu kunt u uw code tussen de twee regels invoegen.

Opmerking: zodra u Werkblad selecteert in de vervolgkeuzelijst, ziet u twee regels code in het codevenster. Nadat u de gebeurtenis hebt geselecteerd waarvoor u de code wilt, kunt u de regels verwijderen die standaard zijn verschenen.

Merk op dat elk werkblad een eigen codevenster heeft. Wanneer u de code voor Blad1 plaatst, werkt deze alleen als de gebeurtenis in Blad1 plaatsvindt.

In dit werkboekcodevenster

Net als werkbladen, als u een gebeurteniscode op werkmapniveau hebt, kunt u deze in het ThisWorkbook-codevenster plaatsen.

Wanneer u dubbelklikt op ThisWorkbook, wordt het codevenster ervoor geopend.

U moet Werkmap selecteren in de vervolgkeuzelijst linksboven in het codevenster.

Nadat u Werkmap hebt geselecteerd in de vervolgkeuzelijst, krijgt u een lijst met alle gebeurtenissen die verband houden met de werkmap. U kunt degene die u wilt gebruiken selecteren in de vervolgkeuzelijst rechtsboven in het codevenster.

Zodra u de gebeurtenis selecteert, wordt automatisch de eerste en laatste regel van de code voor de geselecteerde gebeurtenis ingevoerd. Nu kunt u uw code tussen de twee regels invoegen.

Opmerking: zodra u Werkmap selecteert in de vervolgkeuzelijst, ziet u twee regels code in het codevenster. Nadat u de gebeurtenis hebt geselecteerd waarvoor u de code wilt, kunt u de regels verwijderen die standaard zijn verschenen.

In het gebruikersformuliercodevenster

Wanneer u UserForms in Excel maakt, kunt u UserForm-gebeurtenissen ook gebruiken om codes uit te voeren op basis van specifieke acties. U kunt bijvoorbeeld een code opgeven die wordt uitgevoerd wanneer op de knop wordt geklikt.

Hoewel de objecten Sheet en ThisWorkbook al beschikbaar zijn wanneer u de VB Editor opent, moet u eerst UserForm maken.

Om een ​​UserForm aan te maken, klikt u met de rechtermuisknop op een van de objecten, gaat u naar Invoegen en klikt u op UserForm.

Dit zou een UserForm-object in de werkmap invoegen.

Wanneer u dubbelklikt op het gebruikersformulier (of een van de objecten die u aan het gebruikersformulier toevoegt), wordt het codevenster voor het gebruikersformulier geopend.

Nu, net als werkbladen of ThisWorkbook, kunt u de gebeurtenis selecteren en de eerste en de laatste regel voor die gebeurtenis invoegen. En dan kun je de code in het midden ervan toevoegen.

In het kaartcodevenster

In Excel kunt u ook grafiekbladen invoegen (die anders zijn dan werkbladen). Een kaartblad is bedoeld om alleen grafieken te bevatten.

Wanneer u een grafiekblad heeft ingevoegd, kunt u het grafiekbladobject in de VB Editor zien.

U kunt de gebeurteniscode toevoegen aan het codevenster van het kaartblad, net zoals we deden in het werkblad.

Dubbelklik op het grafiekbladobject in de Projectverkenner. Dit opent het codevenster voor het kaartblad.

Nu moet u Grafiek selecteren in de vervolgkeuzelijst linksboven in het codevenster.

Nadat u Grafiek in de vervolgkeuzelijst hebt geselecteerd, krijgt u een lijst met alle gebeurtenissen die verband houden met het grafiekblad. U kunt degene die u wilt gebruiken selecteren in de vervolgkeuzelijst rechtsboven in het codevenster.

Opmerking: zodra u Grafiek selecteert in de vervolgkeuzelijst, ziet u dat er twee regels code in het codevenster verschijnen. Nadat u de gebeurtenis hebt geselecteerd waarvoor u de code wilt, kunt u de regels verwijderen die standaard zijn verschenen.

In de klas-module

Class Modules moeten net als UserForms worden ingevoegd.

Een klassenmodule kan code bevatten die betrekking heeft op de toepassing - wat Excel zelf zou zijn, en de ingesloten grafieken.

Ik zal de klasmodule de komende weken als een aparte tutorial behandelen.

Merk op dat, afgezien van OnTime en OnKey-gebeurtenissen, geen van de bovenstaande gebeurtenissen kan worden opgeslagen in de reguliere VBA-module.

De gebeurtenisvolgorde begrijpen

Wanneer u een gebeurtenis activeert, gebeurt dit niet op zichzelf. Het kan ook leiden tot een reeks van meerdere triggers.

Wanneer u bijvoorbeeld een nieuw werkblad invoegt, gebeuren de volgende dingen:

1. Er is een nieuw werkblad toegevoegd
2. Het vorige werkblad wordt gedeactiveerd
3. Het nieuwe werkblad wordt geactiveerd

Hoewel u zich in de meeste gevallen geen zorgen hoeft te maken over de volgorde, is het beter om de volgorde te kennen om onverwachte resultaten te voorkomen als u complexe codes maakt die afhankelijk zijn van gebeurtenissen.

De rol van argumenten in VBA-gebeurtenissen begrijpen

Voordat we naar voorbeelden van evenementen gaan en de geweldige dingen die je ermee kunt doen, is er één belangrijk concept dat ik moet bespreken.

In VBA-gebeurtenissen zijn er twee soorten codes:

• Zonder enige argumenten
• Met argumenten

En in deze sectie wil ik snel de rol van argumenten bespreken.

Hieronder staat een code die geen argument bevat (de haakjes zijn leeg):

Private Sub Workbook_Open() MsgBox "Vergeet niet om de urenstaat in te vullen" End Sub

Met de bovenstaande code wordt bij het openen van een werkmap eenvoudig een berichtvenster weergegeven met het bericht - "Vergeet niet om het rooster in te vullen".

Laten we nu eens kijken naar een code die een argument heeft.

Private Sub Workbook_NewSheet(ByVal Sh As Object) Sh.Range("A1") = Sh.Name End Sub

De bovenstaande code gebruikt het Sh-argument dat is gedefinieerd als een objecttype. Het Sh-argument kan een werkblad of een grafiekblad zijn, omdat de bovenstaande gebeurtenis wordt geactiveerd wanneer een nieuw blad wordt toegevoegd.

Door het nieuwe blad dat aan de werkmap is toegevoegd toe te wijzen aan de objectvariabele Sh, heeft VBA ons in staat gesteld om het in de code te gebruiken. Dus om naar de nieuwe bladnaam te verwijzen, kan ik Sh.Name gebruiken.

Het concept van argumenten is handig wanneer u de voorbeelden van VBA-gebeurtenissen in de volgende secties doorneemt.

Gebeurtenissen op werkmapniveau (uitgelegd met voorbeelden)

Hieronder volgen de meest gebruikte gebeurtenissen in een werkmap.

EVENEMENT NAAM	WAT HET EVENEMENT IN VORM GAAT
Activeren	Wanneer een werkmap is geactiveerd
Na Opslaan	Wanneer een werkmap is geïnstalleerd als een invoegtoepassing
VoorOpslaan	Wanneer een werkmap wordt opgeslagen
VoorSluiten	Wanneer een werkmap is gesloten
Voorafdrukken	Wanneer een werkmap wordt afgedrukt
Deactiveren	Wanneer een werkmap is gedeactiveerd
Nieuw Blad	Wanneer een nieuw blad wordt toegevoegd
Open	Wanneer een werkmap wordt geopend
BladActiveren	Wanneer een blad in de werkmap is geactiveerd
BladVóórVerwijderen	Wanneer een blad wordt verwijderd
BladVóórDoubleClick	Wanneer er op een blad wordt gedubbelklikt
BladBeforeRightClick	Wanneer er met de rechtermuisknop op een blad wordt geklikt
BladBerekenen	Wanneer een blad wordt berekend of herberekend
BladDeactiveren	Wanneer een werkmap is gedeactiveerd
BladdraaitabelUpdate	Wanneer een werkmap wordt bijgewerkt
BladSelectieWijzigen	Wanneer een werkmap wordt gewijzigd
VensterActiveren	Wanneer een werkmap is geactiveerd
VensterDeactiveren	Wanneer een werkmap is gedeactiveerd

Let op: dit is geen volledige lijst. De volledige lijst vind je hier.

Onthoud dat de code voor de Workbook-gebeurtenis is opgeslagen in het codevenster van ThisWorkbook-objecten.

Laten we nu eens kijken naar enkele nuttige werkboekgebeurtenissen en kijken hoe deze kunnen worden gebruikt in uw dagelijkse werk.

Werkboek open evenement

Stel dat u de gebruiker een vriendelijke herinnering wilt laten zien om hun urenstaten in te vullen wanneer ze een specifieke werkmap openen.

U kunt hiervoor de onderstaande code gebruiken:

Private Sub Workbook_Open() MsgBox "Vergeet niet om de urenstaat in te vullen" End Sub

Zodra u nu de werkmap met deze code opent, wordt er een berichtvenster weergegeven met het opgegeven bericht.

Er zijn een paar dingen die u moet weten als u met deze code (of Workbook Event-codes in het algemeen) werkt:

• Als een werkmap een macro heeft en u deze wilt opslaan, moet u deze opslaan in de .XLSM-indeling. Anders zou de macrocode verloren gaan.
• In het bovenstaande voorbeeld zou de gebeurteniscode alleen worden uitgevoerd als de macro's zijn ingeschakeld. Mogelijk ziet u een gele balk die om toestemming vraagt ​​om macro's in te schakelen. Zolang dat niet is ingeschakeld, wordt de gebeurteniscode niet uitgevoerd.
• De Workbook-gebeurteniscode wordt in het codevenster van het ThisWorkbook-object geplaatst.

Je kunt deze code verder verfijnen en alleen het bericht van vrijdag tonen.

De onderstaande code zou dit doen:

Private Sub Workbook_Open() wkday = Weekday(Date) If wkday = 6 Dan MsgBox "Vergeet niet om het rooster in te vullen" End Sub

Merk op dat in de functie Weekdag aan zondag de waarde 1 wordt toegewezen, is maandag 2 enzovoort.

Daarom heb ik er voor vrijdag 6 gebruikt.

Workbook Open-gebeurtenis kan in veel situaties nuttig zijn, zoals:

• Wanneer u een welkomstbericht aan de persoon wilt tonen wanneer een werkmap wordt geopend.
• Wanneer u een herinnering wilt weergeven wanneer de werkmap wordt geopend.
• Wanneer u altijd één specifiek werkblad in de werkmap wilt activeren wanneer deze wordt geopend.
• Wanneer u gerelateerde bestanden samen met de werkmap wilt openen.
• Wanneer u de datum- en tijdstempel wilt vastleggen telkens wanneer de werkmap wordt geopend.

Werkmap NewSheet-gebeurtenis

NewSheet-gebeurtenis wordt geactiveerd wanneer u een nieuw blad in de werkmap invoegt.

Stel dat u de datum- en tijdwaarde in cel A1 van het nieuw ingevoegde blad wilt invoeren. U kunt hiervoor de onderstaande code gebruiken:

Private Sub Workbook_NewSheet(ByVal Sh As Object) On Error Hervat volgende Sh.Range("A1") = Format(Now, "dd-mmm-yyyy uu:mm:ss") End Sub

De bovenstaande code gebruikt 'On Error Resume Next' om gevallen af ​​te handelen waarin iemand een grafiekblad invoegt en geen werkblad. Aangezien het grafiekblad geen cel A1 heeft, zou er een fout worden weergegeven als 'On Error Resume Next' niet wordt gebruikt.

Een ander voorbeeld kan zijn wanneer u een basisinstelling of opmaak op een nieuw blad wilt toepassen zodra het is toegevoegd. Wil je bijvoorbeeld een nieuw blad toevoegen en wil je dat dit automatisch een serienummer krijgt (tot 100), dan kun je onderstaande code gebruiken.

Private Sub Workbook_NewSheet (ByVal Sh As Object) Bij fout Volgende hervatten met Sh.Range ("A1") .Value = "S. No." .Interior.Color = vbBlue .Font.Color = vbWhite End With For i = 1 To 100 Sh.Range("A1").Offset(i, 0).Value = i Next i Sh.Range("A1", Range ("A1").End(xlDown)).Borders.LineStyle = xlContinuous End Sub

De bovenstaande code doet ook een beetje opmaak. Het geeft de kopcel een blauwe kleur en maakt het lettertype wit. Het past ook een rand toe op alle gevulde cellen.

De bovenstaande code is een voorbeeld van hoe een korte VBA-code u kan helpen een paar seconden te stelen elke keer dat u een nieuw werkblad invoegt (voor het geval dit iets is dat u elke keer moet doen).

Werkmap Voordat Evenement Opslaan

De gebeurtenis Voor opslaan wordt geactiveerd wanneer u een werkmap opslaat. Houd er rekening mee dat de gebeurtenis eerst wordt geactiveerd en dat vervolgens de werkmap wordt opgeslagen.

Bij het opslaan van een Excel-werkmap kunnen er twee mogelijke scenario's zijn:

1. U slaat het voor de eerste keer op en het dialoogvenster Opslaan als wordt weergegeven.
2. Je hebt het al eerder opgeslagen en het zal gewoon de wijzigingen in de reeds opgeslagen versie opslaan en overschrijven.

Laten we nu een paar voorbeelden bekijken waar u de BeforeSave-gebeurtenis kunt gebruiken.

Stel dat u een nieuwe werkmap hebt die u voor de eerste keer opslaat en u wilt de gebruiker eraan herinneren deze op te slaan in de K-schijf, dan kunt u de onderstaande code gebruiken:

Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Dan MsgBox "Save this File in the K Drive" End Sub

Als het bestand in de bovenstaande code nog nooit is opgeslagen, is SaveAsUI True en wordt het dialoogvenster Opslaan als weergegeven. De bovenstaande code zou het bericht weergeven voordat het dialoogvenster Opslaan als verschijnt.

Een ander voorbeeld zou kunnen zijn om de datum en tijd bij te werken waarop het bestand in een specifieke cel is opgeslagen.

De onderstaande code voegt de datum- en tijdstempel in cel A1 van Blad1 in wanneer het bestand wordt opgeslagen.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Worksheets("Blad1").Range("A1") = Format(Now, "dd-mmm-yyyy hh:mm:ss") End Sub

Merk op dat deze code wordt uitgevoerd zodra de gebruiker de werkmap opslaat. Als de werkmap voor de eerste keer wordt opgeslagen, wordt een dialoogvenster Opslaan als weergegeven. Maar de code is al uitgevoerd tegen de tijd dat u het dialoogvenster Opslaan als ziet. Als u nu besluit de werkmap te annuleren en niet op te slaan, zijn de datum en tijd al in de cel ingevoerd.

Werkboek Vooraf Evenement sluiten

De gebeurtenis Before Close vindt plaats vlak voordat de werkmap wordt gesloten.

De onderstaande code beveiligt alle werkbladen voordat de werkmap wordt gesloten.

Private Sub Workbook_BeforeClose (Cancel As Boolean) Dim sh As Worksheet voor elke sh In ThisWorkbook.Worksheets sh.Protect Next sh End Sub

Onthoud dat de gebeurteniscode wordt geactiveerd zodra u de werkmap sluit.

Een belangrijk ding om te weten over deze gebeurtenis is dat het niet uitmaakt of de werkmap daadwerkelijk is gesloten of niet.

Als de werkmap niet is opgeslagen en u wordt gevraagd of u de werkmap wilt opslaan of niet, en u klikt op Annuleren, wordt uw werkmap niet opgeslagen.De gebeurteniscode zou dan echter al zijn uitgevoerd.

Werkboek voorafdrukgebeurtenis

Wanneer u de printopdracht (of Print Preview-opdracht) geeft, wordt de gebeurtenis Before Print geactiveerd.

De onderstaande code berekent alle werkbladen opnieuw voordat uw werkmap wordt afgedrukt.

Private Sub Workbook_BeforePrint (Cancel As Boolean) For Each ws in Worksheets ws.Calculate Next ws End Sub

Wanneer de gebruiker de werkmap afdrukt, wordt de gebeurtenis geactiveerd, of hij/zij nu de hele werkmap afdrukt of slechts een deel ervan.

Een ander voorbeeld hieronder is van de code die de datum en tijd zou toevoegen aan de voettekst wanneer de werkmap wordt afgedrukt.

Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Printed On - " & Format(Now, "dd-mmm-yyyy hh:mm") Volgende ws End Sub

Gebeurtenissen op werkbladniveau (uitgelegd met voorbeelden)

Werkbladgebeurtenissen vinden plaats op basis van de triggers in het werkblad.

Hieronder volgen de meest gebruikte gebeurtenissen in een werkblad.

Evenement naam	Wat triggert de gebeurtenis?
Activeren	Wanneer het werkblad is geactiveerd
VoorVerwijderen	Voordat het werkblad wordt verwijderd
Voor DoubleClick	Voordat op het werkblad wordt gedubbelklikt
VoorRechtsKlikken	Voordat er met de rechtermuisknop op het werkblad wordt geklikt
Berekenen	Voordat het werkblad wordt berekend of herberekend
Verandering	Wanneer de cellen in het werkblad worden gewijzigd
Deactiveren	Wanneer het werkblad is gedeactiveerd
Draaitabel bijwerken	Wanneer de draaitabel in het werkblad wordt bijgewerkt
SelectieWijzigen	Wanneer de selectie op het werkblad wordt gewijzigd

Let op: dit is geen volledige lijst. De volledige lijst vind je hier.

Onthoud dat de code voor de werkbladgebeurtenis is opgeslagen in het werkbladobjectcodevenster (in het venster waarin u de gebeurtenis wilt activeren). Er kunnen meerdere werkbladen in een werkmap zijn en uw code wordt alleen geactiveerd wanneer de gebeurtenis plaatsvindt in het werkblad waarin deze is geplaatst.

Laten we nu eens kijken naar enkele nuttige werkbladgebeurtenissen en kijken hoe deze in uw dagelijkse werk kunnen worden gebruikt.

Werkblad Evenement activeren

Deze gebeurtenis wordt geactiveerd wanneer u een werkblad activeert.

Met de onderstaande code wordt de beveiliging van een blad opgeheven zodra het is geactiveerd.

Private Sub Worksheet_Activate() ActiveSheet.Unprotect End Sub

U kunt deze gebeurtenis ook gebruiken om ervoor te zorgen dat een specifieke cel of een celbereik (of een benoemd bereik) wordt geselecteerd zodra u het werkblad activeert. De onderstaande code zou cel D1 selecteren zodra u het blad activeert.

Private Sub Worksheet_Activate() ActiveSheet.Range("D1").Select End Sub

Werkblad Wijzigingsgebeurtenis

Een wijzigingsgebeurtenis wordt geactiveerd wanneer u een wijziging aanbrengt in het werkblad.

Nou… niet altijd.

Er zijn enkele wijzigingen die de gebeurtenis activeren, en andere niet. Hier is een lijst met enkele wijzigingen die de gebeurtenis niet activeren:

• Wanneer u de opmaak van de cel wijzigt (lettergrootte, kleur, rand, enz.).
• Wanneer u cellen samenvoegt. Dit is verrassend, omdat het samenvoegen van cellen soms ook de inhoud van alle cellen verwijdert, behalve de linkerbovenhoek.
• Wanneer u een celopmerking toevoegt, verwijdert of bewerkt.
• Wanneer u een celbereik sorteert.
• Wanneer u Doel zoeken gebruikt.

De volgende wijzigingen zouden de gebeurtenis activeren (ook al zou je denken dat dit niet zou moeten):

• Kopiëren en plakken van opmaak zou de gebeurtenis activeren.
• Het wissen van de opmaak zou de gebeurtenis activeren.
• Het uitvoeren van een spellingcontrole zou de gebeurtenis activeren.

Hieronder ziet u een code die een berichtvenster toont met het adres van de cel die is gewijzigd.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "Je hebt zojuist gewijzigd" & Target.Address End Sub

Hoewel dit een nutteloze macro is, laat het je zien hoe je het Target-argument kunt gebruiken om erachter te komen welke cellen zijn gewijzigd.

Laten we nu een paar meer bruikbare voorbeelden bekijken.

Stel dat je een celbereik hebt (laten we zeggen A1:D10) en je wilt een prompt weergeven en de gebruiker vragen of ze echt een cel in dit bereik willen wijzigen of niet, dan kun je de onderstaande code gebruiken.

Het toont een prompt met twee knoppen - Ja en Nee. Als de gebruiker 'Ja' selecteert, is de wijziging voltooid, anders wordt het teruggedraaid.

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("U brengt een wijziging aan in cellen in A1:D10. Weet u zeker dat u dit wilt?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

In de bovenstaande code controleren we of de doelcel in de eerste 4 kolommen en de eerste 10 rijen staat. Als dat het geval is, wordt het berichtvenster weergegeven. Als de gebruiker Nee heeft geselecteerd in het berichtvenster, wordt de wijziging ook ongedaan gemaakt (met de opdracht Application.Undo).

Merk op dat ik Application.EnableEvents = False heb gebruikt voor de regel Application.Undo. En toen heb ik het omgekeerd door Application.EnableEvent = True in de volgende regel te gebruiken.

Dit is nodig, want wanneer het ongedaan maken plaatsvindt, wordt ook de wijzigingsgebeurtenis geactiveerd. Als ik EnableEvent niet op False zet, blijft de change-gebeurtenis activeren.

U kunt de wijzigingen in een benoemd bereik ook controleren met behulp van de wijzigingsgebeurtenis. Als u bijvoorbeeld een benoemd bereik hebt met de naam "DataRange" en u een prompt wilt weergeven voor het geval de gebruiker een wijziging aanbrengt in dit benoemde bereik, kunt u de onderstaande code gebruiken:

Private Sub Worksheet_Change(ByVal Target As Range) Dim DRange As Range Set DRange = Range("DataRange") If Not Intersect(Target, DRange) Is Nothing Dan MsgBox "U heeft zojuist een wijziging aangebracht in het gegevensbereik" End If End Sub

De bovenstaande code controleert of de cel/het bereik waarin u de wijzigingen hebt aangebracht, cellen heeft die gemeenschappelijk zijn voor het gegevensbereik. Als dit het geval is, wordt het berichtvenster weergegeven.

WerkmapselectieEvent wijzigen

De selectiewijzigingsgebeurtenis wordt geactiveerd wanneer er een selectiewijziging in het werkblad plaatsvindt.

De onderstaande code berekent het blad opnieuw zodra u de selectie wijzigt.

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Application.Calculate End Sub

Een ander voorbeeld van deze gebeurtenis is wanneer u de actieve rij en kolom van de geselecteerde cel wilt markeren.

Iets zoals hieronder weergegeven:

De volgende code kan dit doen:

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.Color = RGB (248, 203, 173) .EntireColumn.Interior.Color = RGB(180, 198, 231) Eindig met Einde sub

De code verwijdert eerst de achtergrondkleur van alle cellen en past vervolgens de kleur toe die in de code wordt vermeld op de actieve rij en kolom.

En dat is het probleem met deze code. Dat het kleur uit alle cellen verwijdert.

Als u de actieve rij/kolom wilt markeren terwijl u de kleur in andere cellen intact wilt houden, gebruikt u de techniek die in deze zelfstudie wordt getoond.

DoubleClick-gebeurtenis in werkmap

Dit is een van mijn favoriete werkbladevenementen en je zult veel tutorials zien waarin ik dit heb gebruikt (zoals deze of deze).

Deze gebeurtenis wordt geactiveerd wanneer u dubbelklikt op een cel.

Laat me je laten zien hoe geweldig dit is.

Met de onderstaande code kun je dubbelklikken op een cel en het zal een achtergrondkleur toepassen, de letterkleur wijzigen en de tekst in de cel vet maken;

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Cancel = True With Target .Interior.Color = vbBlue .Font.Color = vbWhite .Font.Bold = True End With End Sub

Dit kan handig zijn wanneer u een lijst met cellen doorloopt en een paar geselecteerde cellen wilt markeren. Hoewel u de F4-toets kunt gebruiken om de laatste stap te herhalen, zou deze slechts één soort opmaak kunnen toepassen. Met deze dubbelklikgebeurtenis kunt u ze alle drie toepassen met slechts een dubbelklik.

Merk op dat ik in de bovenstaande code de waarde van Cancel = True heb gemaakt.

Dit wordt gedaan zodat de standaardactie van dubbelklikken is uitgeschakeld - namelijk om in de bewerkingsmodus te komen. Met Annuleren = True, zou Excel u niet in de bewerkingsmodus brengen wanneer u dubbelklikt op de cel.

Hier is nog een voorbeeld.

Als u een takenlijst in Excel hebt, kunt u een dubbelklikgebeurtenis gebruiken om de doorgestreepte indeling toe te passen om de taak als voltooid te markeren.

Iets zoals hieronder weergegeven:

Hier is de code die dit zal doen:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True CurrFormat = Target.Font.Strikethrough If CurrFormat Then Target.Font.Strikethrough = False Else Target.Font.Strikethrough = True End If End Sub

Merk op dat ik in deze code dubbelklik heb gemaakt als een schakelgebeurtenis. Wanneer u dubbelklikt op een cel, wordt gecontroleerd of het doorgestreepte formaat al is toegepast. Als dit het geval is, wordt door dubbelklikken het doorgestreepte formaat verwijderd, en als dat niet het geval is geweest, wordt het doorgestreepte formaat toegepast.

Excel VBA OnTime-gebeurtenis

De gebeurtenissen die we tot nu toe in dit artikel hebben gezien, waren gekoppeld aan een van de Excel-objecten, of het nu de werkmap, het werkblad, het grafiekblad of UserForms was, enz.

OnTime-gebeurtenis is anders dan andere gebeurtenissen omdat het kan worden opgeslagen in de reguliere VBA-module (terwijl de andere in het codevenster van objecten zoals ThisWorkbook of Worksheets of UserForms zouden worden geplaatst).

Binnen de reguliere VBA-module wordt het gebruikt als een methode van het applicatie-object.

De reden dat dit als een gebeurtenis wordt beschouwd, is dat deze kan worden geactiveerd op basis van de tijd die u opgeeft. Als ik bijvoorbeeld wil dat het blad elke 5 minuten opnieuw wordt berekend, kan ik de OnTime-gebeurtenis daarvoor gebruiken.

Of, als ik een bericht/herinnering op een bepaald tijdstip van de dag wil tonen, kan ik de OnTime-gebeurtenis gebruiken.

Hieronder staat een code die elke dag om 14.00 uur een bericht toont.

Sub MessageTime() Application.OnTime TimeValue("14:00:00"), "ShowMessage" End Sub Sub ShowMessage() MsgBox "It's Lunch Time" End Sub

Onthoud dat je deze code in de reguliere VBA-module moet plaatsen,

Hoewel de OnTime-gebeurtenis op het opgegeven tijdstip zou worden geactiveerd, moet u de macro op elk moment handmatig uitvoeren. Nadat u de macro hebt uitgevoerd, wacht deze tot 14:00 uur en roept vervolgens de macro 'ShowMessage' op.

De ShowMessage-macro zou dan het bericht weergeven.

De OnTime-gebeurtenis heeft vier argumenten:

Toepassing.OnTime(Vroegste tijd, Procedure, Laatste tijd, Planning)

• Vroegste tijd: Het tijdstip waarop u de procedure wilt uitvoeren.
• Procedure: De naam van de procedure die moet worden uitgevoerd.
• Laatste tijd (optioneel): Als er een andere code actief is en uw opgegeven code niet op het opgegeven tijdstip kan worden uitgevoerd, kunt u de LatestTime opgeven waarop deze moet wachten. Het kan bijvoorbeeld EarliestTime + 45 zijn (wat betekent dat het 45 seconden zal wachten voordat de andere procedure is voltooid). Als zelfs na 45 seconden de procedure niet kan worden uitgevoerd, wordt deze verlaten. Als u dit niet opgeeft, wacht Excel totdat de code kan worden uitgevoerd en voert deze vervolgens uit.
• Schema (optioneel): Indien ingesteld op True, plant het een nieuwe tijdprocedure. Indien False, annuleert het de eerder ingestelde procedure. Standaard is dit True.

In het bovenstaande voorbeeld hebben we alleen de eerste twee argumenten gebruikt.

Laten we naar een ander voorbeeld kijken.

De onderstaande code zou het werkblad elke 5 minuten verversen.

Dim NextRefresh as Date Sub RefreshSheet() ThisWorkbook.Worksheets("Sheet1").Calculate NextRefresh = Now + TimeValue("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh() On Error Resume Next Application.OnTime NextRefresh, "RefreshSheet", , False End Sub

De bovenstaande code zou het werkblad elke 5 minuten verversen.

Het gebruikt de Nu-functie om de huidige tijd te bepalen en voegt vervolgens 5 minuten toe aan de huidige tijd.

De OnTime-gebeurtenis zou blijven lopen totdat u deze stopt. Als u de werkmap sluit en de Excel-toepassing nog steeds actief is (andere werkmappen zijn geopend), wordt de werkmap waarin de OnTime-gebeurtenis wordt uitgevoerd, zichzelf opnieuw geopend.

Dit kan beter worden afgehandeld door de OnTime-gebeurtenis specifiek te stoppen.

In de bovenstaande code heb ik de StopRefresh-code, maar u moet deze uitvoeren om de OnTime-gebeurtenis te stoppen. U kunt dit handmatig doen, toewijzen aan een knop en dit doen door op de knop te drukken of deze aan te roepen vanuit de Workbook Close-gebeurtenis.

Private Sub Workbook_BeforeClose (Annuleren als Boolean) Bel StopRefresh End Sub

De bovenstaande gebeurteniscode 'BeforeClose' komt in het codevenster van ThisWorkbook.

Excel VBA OnKey-gebeurtenis

Wanneer u met Excel werkt, houdt het de toetsaanslagen die u gebruikt in de gaten. Hierdoor kunnen we toetsaanslagen gebruiken als trigger voor een gebeurtenis.

Met OnKey-gebeurtenis kunt u een toetsaanslag (of een combinatie van toetsaanslagen) specificeren en de code die moet worden uitgevoerd wanneer die toetsaanslag wordt gebruikt. Wanneer deze toetsaanslagen worden ingedrukt, zal het de code ervoor uitvoeren.

Net als de OnTime-gebeurtenis, moet u een manier hebben om de OnKey-gebeurtenis te annuleren. Wanneer u de OnKey-gebeurtenis instelt voor een specifieke toetsaanslag, wordt deze ook beschikbaar in alle geopende werkmappen.

Voordat ik u een voorbeeld laat zien van het gebruik van de OnKey-gebeurtenis, wil ik eerst de sleutelcodes delen die voor u beschikbaar zijn in VBA.

SLEUTEL	CODE
Backspace	{BACKSPACE} of {BS}
Pauze	{PAUZE}
Caps Lock	{CAPS LOCK}
Verwijderen	{VERWIJDEREN} of {DEL}
Pijl omlaag	{OMLAAG}
Einde	{EINDE}
Binnenkomen	~
Enter (op het numerieke toetsenbord)	{BINNENKOMEN}
Ontsnappen	{ESCAPE} of {ESC}
Thuis	{THUIS}
Ins	{INSERT}
Linker pijl	{LINKS}
NumLock	{NUMLOCK}
PageDown	{PGDN}
Pagina omhoog	{PGUP}
Rechter pijl	{RECHTSAF}
Scroll Lock	{SCROLLOCK}
Tab	{TAB}
Pijltje omhoog	{OMHOOG}
F1 tot en met F15	{F1} tot en met {F15}

Wanneer u een onkey-gebeurtenis moet gebruiken, moet u de code ervoor gebruiken.

De bovenstaande tabel bevat de codes voor enkele toetsaanslagen.

U kunt deze ook combineren met de volgende codes:

• Verschuiving: + (Plusteken)
• Controle: ^ (Caret)
• Alt: % (Percentage)

Voor bijvoorbeeld Alt F4 moet u de code gebruiken: "%{F4}” - waarbij % voor de ALT-toets is en {F4} voor de F4-toets.

Laten we nu een voorbeeld bekijken (onthoud dat de code voor OnKey-evenementen in de reguliere VBA-module wordt geplaatst).

Wanneer je op de PageUp- of PageDown-toets drukt, springt het 29 rijen boven / onder de actieve cel (dat is tenminste wat het doet op mijn laptop).

Als je wilt dat het slechts 5 rijen tegelijk springt, kun je de onderstaande code gebruiken:

Sub PageUpDownKeys() Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub Sub PageUpMod() On Error Resume Next ActiveCell.Offset(-5, 0).Activate End Sub Sub PageDownMod() On Error Hervat volgende ActiveCell.Offset(5, 0). Activeer End Sub

Wanneer u het eerste deel van de code uitvoert, worden de OnKey-gebeurtenissen uitgevoerd. Als dit eenmaal is uitgevoerd, zou het gebruik van de PageUp- en de PageDown-toets ervoor zorgen dat de cursor slechts 5 rijen tegelijk verspringt.

Merk op dat we 'On Error Resume Next' hebben gebruikt om ervoor te zorgen dat fouten worden genegeerd. Deze fouten kunnen optreden wanneer u op de PageUp-toets drukt, zelfs als u zich bovenaan het werkblad bevindt. Aangezien er geen rijen meer zijn om te springen, zou de code een fout weergeven. Maar aangezien we 'On Error Resume Next' hebben gebruikt, wordt dit genegeerd.

Om ervoor te zorgen dat deze OnKey-gebeurtenissen beschikbaar zijn, moet u het eerste deel van de code uitvoeren. Als je wilt dat deze beschikbaar is zodra je de werkmap opent, kun je deze in het codevenster van ThisWorkbook plaatsen.

Private Sub Workbook_Open() Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub

De onderstaande code zal de sleutels terugbrengen naar hun normale functionaliteit.

Sub Cancel_PageUpDownKeysMod() Application.OnKey "{PgUp}" Application.OnKey "{PgDn}" End Sub

Als u het tweede argument in de OnKey-methode niet opgeeft, keert het de toetsaanslag terug naar zijn normale functionaliteit.

Als u de functionaliteit van een toetsaanslag wilt annuleren, zodat Excel niets doet wanneer die toetsaanslag wordt gebruikt, moet u een lege string als tweede argument gebruiken.

In de onderstaande code zou Excel niets doen als we de PageUp- of PageDown-toetsen gebruiken.

Sub Ignore_PageUpDownKeys() Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" End Sub

Gebeurtenissen in VBA uitschakelen

Soms moet u gebeurtenissen mogelijk uitschakelen om uw code correct te laten werken.

Stel dat ik een bereik heb (A1:D10) en dat ik een bericht wil weergeven wanneer een cel in dit bereik wordt gewijzigd. Dus ik laat een berichtvenster zien en vraag de gebruiker of ze zeker weten dat ze de wijziging willen aanbrengen. Als het antwoord Ja is, wordt de wijziging aangebracht en als het antwoord Nee is, zou VBA het ongedaan maken.

U kunt de onderstaande code gebruiken:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("U brengt een wijziging aan in cellen in A1:D10. Weet u zeker dat u dit wilt?", vbYesNo) End If If Ans = vbNo Then Application.Ongedaan maken End If End Sub

Het probleem met deze code is dat wanneer de gebruiker Nee selecteert in het berichtvenster, de actie wordt omgekeerd (zoals ik Application.Undo heb gebruikt).

Wanneer het ongedaan maken plaatsvindt en de waarde wordt teruggezet naar de oorspronkelijke waarde, wordt de VBA-wijzigingsgebeurtenis opnieuw geactiveerd en krijgt de gebruiker opnieuw hetzelfde berichtvenster te zien.

Dit betekent dat u op NEE kunt blijven klikken in het berichtvenster en dat het blijft verschijnen. Dit gebeurt omdat je in dit geval vastzit in de oneindige lus.

Om dergelijke gevallen te voorkomen, moet u gebeurtenissen uitschakelen zodat de wijzigingsgebeurtenis (of een andere gebeurtenis) niet wordt geactiveerd.

De volgende code zou in dit geval goed werken:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("U brengt een wijziging aan in cellen in A1:D10. Weet u zeker dat u dit wilt?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

In de bovenstaande code, direct boven de regel Application.Undo, hebben we - Application.EnableEvents = False gebruikt.

Als u EnableEvents instelt op False, wordt er geen gebeurtenis geactiveerd (in de huidige of geopende werkmappen).

Nadat we de bewerking ongedaan hebben gemaakt, kunnen we de eigenschap EnableEvents terugzetten op True.

Houd er rekening mee dat het uitschakelen van gebeurtenissen van invloed is op alle werkmappen die momenteel zijn geopend (of geopend terwijl EnableEvents is ingesteld op False). Als u bijvoorbeeld een nieuwe werkmap opent als onderdeel van de code, werkt de gebeurtenis Werkmap openen niet.

Impact van gebeurtenissen Stapel ongedaan maken

Laat me je eerst vertellen wat een Undo Stack is.

Wanneer u in Excel werkt, blijft het uw acties volgen. Als je een fout maakt, kun je altijd Control + Z gebruiken om terug te gaan naar de vorige stap (d.w.z. je huidige actie ongedaan maken).

Als u tweemaal op Control + Z drukt, gaat u twee stappen terug. Deze stappen die u hebt uitgevoerd, worden opgeslagen als onderdeel van de Undo-stack.

Elke gebeurtenis die het werkblad verandert, vernietigt deze stapel Ongedaan maken.Dit betekent dat als ik 5 dingen heb gedaan voordat ik een gebeurtenis activeer, ik Control + Z niet kan gebruiken om terug te gaan naar die vorige stappen. Het activeren van de gebeurtenis heeft die stapel voor mij vernietigd.

In de onderstaande code gebruik ik VBA om de tijdstempel in cel A1 in te voeren wanneer er een wijziging in het werkblad is.

Private Sub Worksheet_Change (ByVal Target As Range) Application.EnableEvents = False Range ("A1").Value = Format (Now, "dd-mmm-yyyy hh:mm:ss") Application.EnableEvents = True End Sub

Aangezien ik een wijziging in het werkblad aanbreng, zal dit de stapel voor ongedaan maken vernietigen.

Houd er ook rekening mee dat dit niet beperkt is tot alleen evenementen.

Als u een code hebt die is opgeslagen in de gewone VBA-module en u een wijziging aanbrengt in het werkblad, zou dit ook de stapel voor ongedaan maken in Excel vernietigen.

De onderstaande code voert bijvoorbeeld eenvoudig de tekst "Hallo" in cel A1 in, maar zelfs als u dit uitvoert, wordt de stapel voor ongedaan maken vernietigd.

SubtypeHallo() Bereik ("A1"). Waarde = "Hallo" Einde Sub

Misschien vind je de volgende Excel VBA-zelfstudies misschien ook leuk:

• Werken met cellen en bereiken in Excel VBA.
• Werken met werkbladen in Excel VBA.
• Werken met werkmappen in Excel VBA.
• Excel VBA-loops - De ultieme gids.
• IF Then Else-verklaring gebruiken in Excel VBA.
• Voor volgende lus in Excel.
• Door de gebruiker gedefinieerde functies maken in Excel VBA.
• Invoegtoepassingen maken en gebruiken in Excel
• Maak en hergebruik macro's door ze op te slaan in de persoonlijke macrowerkmap.