Excel VBA -evenemang - En enkel (och komplett) guide

När du skapar eller spelar in ett makro i Excel måste du köra makrot för att utföra stegen i koden.

Några sätt att köra ett makro inkluderar att använda makrodialogrutan, tilldela makrot till en knapp, använda en genväg etc.

Förutom dessa användarinitierade makrokörningar kan du också använda VBA-händelser för att köra makrot.

Excel VBA -evenemang - Introduktion

Låt mig först förklara vad som är en händelse i VBA.

En händelse är en åtgärd som kan utlösa körningen av det angivna makrot.

Till exempel, när du öppnar en ny arbetsbok är det en händelse. När du sätter in ett nytt kalkylblad är det en händelse. När du dubbelklickar på en cell är det en händelse.

Det finns många sådana händelser i VBA, och du kan skapa koder för dessa händelser. Det betyder att så snart en händelse inträffar, och om du har angett en kod för den händelsen, skulle den koden direkt köras.

Excel gör detta automatiskt så snart det märks att en händelse har ägt rum. Så du behöver bara skriva koden och placera den i rätt händelsubrutin (detta täcks senare i denna artikel).

Om du till exempel sätter in ett nytt kalkylblad och vill att det ska ha ett årsprefix kan du skriva koden för det.

Nu, när någon sätter in ett nytt kalkylblad, körs denna kod automatiskt och lägger till årsprefixet i kalkylbladets namn.

Ett annat exempel kan vara att du vill ändra cellens färg när någon dubbelklickar på den. Du kan använda dubbelklickhändelsen för detta.

På samma sätt kan du skapa VBA -koder för många sådana händelser (som vi kommer att se senare i denna artikel).

Nedan visas en kort bild som visar dubbelklickhändelsen i aktion. Så snart jag dubbelklickar på cell A1. Excel öppnar omedelbart en meddelanderuta som visar cellens adress.

Dubbelklicka är en händelse, och att visa meddelanderutan är vad jag har angett i koden när dubbelklickhändelsen äger rum.

Även om exemplet ovan är en värdelös händelse, hoppas jag att det hjälper dig att förstå vad händelser verkligen är.

Olika typer av Excel VBA -evenemang

Det finns olika objekt i Excel - till exempel Excel själv (som vi ofta kallar applikationen), arbetsböcker, kalkylblad, diagram, etc.

Var och en av dessa objekt kan ha olika händelser associerade med sig. Till exempel:

• Om du skapar en ny arbetsbok är det en händelse på applikationsnivå.
• Om du lägger till ett nytt kalkylblad är det en händelse på arbetsboksnivå.
• Om du ändrar värdet i en cell i ett ark är det en händelse på kalkylbladsnivå.

Nedan finns de olika typer av händelser som finns i Excel:

1. Händelser på arbetsbladsnivå: Det här är de typer av händelser som skulle utlösa baserat på de åtgärder som vidtas i kalkylbladet. Exempel på dessa händelser inkluderar att ändra en cell i kalkylbladet, ändra markeringen, dubbelklicka på en cell, högerklicka på en cell, etc.
2. Händelser på arbetsboknivå: Dessa händelser skulle utlösas baserat på åtgärderna på arbetsboknivån. Exempel på dessa händelser inkluderar att lägga till ett nytt kalkylblad, spara arbetsboken, öppna arbetsboken, skriva ut en del eller hela arbetsboken, etc.
3. Händelser på applikationsnivå: Det här är de händelser som inträffar i Excel -programmet. Exempel på dessa skulle inkludera att stänga någon av de öppna arbetsböckerna eller öppna en ny arbetsbok.
4. UserForm -nivåhändelser: Dessa händelser skulle utlösas baserat på åtgärderna i "UserForm". Exempel på dessa inkluderar att initiera en UserForm eller klicka på en knapp i UserForm.
5. Karthändelser: Det här är händelser relaterade till diagrambladet. Ett diagramark är annorlunda än ett kalkylblad (det är där de flesta av oss är vana att arbeta i Excel). Ett diagramarkets syfte är att hålla ett diagram. Exempel på sådana händelser skulle innefatta att ändra diagramets serie eller ändra storlek på diagrammet.
6. OnTime och OnKey Events: Det här är två evenemang som inte passar i någon av kategorierna ovan. Så jag har listat dessa separat. Med "OnTime" -händelse kan du köra en kod vid en viss tidpunkt eller efter att en viss tid har gått. Med "OnKey" -händelse kan du köra en kod när en specifik knapptryckning (eller en kombination av knapptryckningar) används.

Var man ska lägga den händelserelaterade koden

I avsnittet ovan täckte jag de olika typerna av evenemang.

Baserat på typen av händelse måste du lägga koden i det relevanta objektet.

Till exempel, om det är en kalkylbladrelaterad händelse, bör den gå i kodfönstret för kalkylbladobjektet. Om det är arbetsbokrelaterat bör det gå i kodfönstret för ett arbetsbokobjekt.

I VBA har olika objekt - till exempel kalkylblad, arbetsböcker, diagramark, användarformer, etc. sina egna kodfönster. Du måste ange händelsekoden i det relevanta objektets kodfönster. Till exempel - om det är en händelse på arbetsboksnivå måste du ha händelsekoden i fönstret för arbetsbokskod.

Följande avsnitt täcker de platser där du kan lägga till evenemangskoden:

I fönstret Kodbladskod

När du öppnar VB -redigeraren (med tangentbordsgenväg ALT + F11) märker du kalkylbladobjektet i Project Explorer. För varje kalkylblad i arbetsboken ser du ett objekt.

När du dubbelklickar på det kalkylbladobjekt där du vill placera koden öppnas kodfönstret för det kalkylbladet.

Även om du kan börja skriva koden från grunden är det mycket bättre att välja händelsen från en lista med alternativ och låta VBA automatiskt infoga relevant kod för den valda händelsen.

För att göra detta måste du först välja kalkylblad från rullgardinsmenyn längst upp till vänster i kodfönstret.

Efter att ha valt kalkylblad från rullgardinsmenyn får du en lista över alla händelser relaterade till kalkylbladet. Du kan välja den du vill använda från rullgardinsmenyn längst upp till höger i kodfönstret.

Så snart du väljer händelsen kommer den automatiskt att ange den första och sista raden i koden för den valda händelsen. Nu kan du lägga till din kod mellan de två raderna.

Obs! Så snart du väljer kalkylblad från rullgardinsmenyn kommer du att märka att två kodrader visas i kodfönstret. När du har valt den händelse som du vill ha koden för kan du radera raderna som visas som standard.

Observera att varje kalkylblad har ett eget kodfönster. När du sätter koden för Sheet1 fungerar det bara om händelsen händer i Sheet1.

I fönstret ThisWorkbook Code

Precis som kalkylblad, om du har en händelsekod på arbetsboksnivå kan du placera den i ThisWorkbook -kodfönstret.

När du dubbelklickar på ThisWorkbook öppnas kodfönstret för den.

Du måste välja Workbook från rullgardinsmenyn längst upp till vänster i kodfönstret.

Efter att ha valt arbetsbok från rullgardinsmenyn får du en lista över alla händelser som är relaterade till arbetsboken. Du kan välja den du vill använda från rullgardinsmenyn längst upp till höger i kodfönstret.

Så snart du väljer händelsen kommer den automatiskt att ange den första och sista raden i koden för den valda händelsen. Nu kan du lägga till din kod mellan de två raderna.

Obs! Så snart du väljer Workbook från rullgardinsmenyn kommer du att märka att två kodrader visas i kodfönstret. När du har valt den händelse som du vill ha koden för kan du radera raderna som visas som standard.

I fönstret Användarkodskod

När du skapar UserForms i Excel kan du också använda UserForm -händelser för att köra koder baserat på specifika åtgärder. Du kan till exempel ange en kod som körs när du klickar på knappen.

Medan Sheet -objekten och ThisWorkbook -objekten redan är tillgängliga när du öppnar VB Editor, är UserForm något du måste skapa först.

För att skapa en UserForm, högerklicka på något av objekten, gå till Infoga och klicka på UserForm.

Detta skulle infoga ett UserForm -objekt i arbetsboken.

När du dubbelklickar på UserForm (eller något av objektet som du lägger till i UserForm) öppnas kodfönstret för UserForm.

Precis som arbetsblad eller ThisWorkbook kan du välja händelsen och den kommer att infoga den första och sista raden för den händelsen. Och sedan kan du lägga till koden i mitten av den.

I fönstret Diagramkod

I Excel kan du också infoga diagramark (som skiljer sig från arbetsblad). Ett diagramblad är endast avsett att innehålla diagram.

När du har infogat ett diagramark kommer du att kunna se diagramarkobjektet i VB -redigeraren.

Du kan lägga till händelsekoden i diagramarkets kodfönster precis som vi gjorde i kalkylbladet.

Dubbelklicka på diagramarkobjektet i Project Explorer. Detta öppnar kodfönstret för diagramarket.

Nu måste du välja Diagram från rullgardinsmenyn längst upp till vänster i kodfönstret.

Efter att ha valt diagram från rullgardinsmenyn får du en lista över alla händelser relaterade till diagramarket. Du kan välja den du vill använda från rullgardinsmenyn längst upp till höger i kodfönstret.

Obs! Så snart du väljer diagram från rullgardinsmenyn kommer du att märka att två rader kod visas i kodfönstret. När du har valt den händelse som du vill ha koden för kan du radera raderna som visas som standard.

I klassmodul

Klassmoduler måste infogas precis som UserForms.

En klassmodul kan innehålla kod relaterad till applikationen - vilket skulle vara Excel själv och de inbäddade diagrammen.

Jag kommer att täcka klassmodulen som en separat handledning under de kommande veckorna.

Observera att förutom OnTime- och OnKey -händelser kan ingen av ovanstående händelser lagras i den vanliga VBA -modulen.

Förstå händelseförloppet

När du utlöser en händelse sker det inte isolerat. Det kan också leda till en sekvens av flera triggers.

Till exempel, när du sätter in ett nytt kalkylblad, händer följande saker:

1. Ett nytt kalkylblad läggs till
2. Det föregående kalkylbladet inaktiveras
3. Det nya kalkylbladet aktiveras

Även om du i de flesta fall inte behöver oroa dig för sekvensen, om du skapar komplexa koder som är beroende av händelser är det bättre att känna till sekvensen för att undvika oväntade resultat.

Förstå argumentens roll i VBA -evenemang

Innan vi hoppar till händelseexempel och de fantastiska saker du kan göra med det, finns det ett viktigt koncept som jag måste täcka.

I VBA -evenemang skulle det finnas två typer av koder:

• Utan några argument
• Med argument

Och i det här avsnittet vill jag snabbt täcka argumentens roll.

Nedan finns en kod som inte har något argument (parentesen är tom):

Private Sub Workbook_Open () MsgBox "Kom ihåg att fylla i tidrapporten" Slut Sub

Med koden ovan, när du öppnar en arbetsbok, visar den helt enkelt en meddelanderuta med meddelandet - "Kom ihåg att fylla i tidrapporten".

Låt oss nu titta på en kod som har ett argument.

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

Ovanstående kod använder Sh -argumentet som definieras som en objekttyp. Sh -argumentet kan vara ett kalkylblad eller ett diagramblad, eftersom ovanstående händelse utlöses när ett nytt ark läggs till.

Genom att tilldela det nya bladet som läggs till i arbetsboken till objektvariabeln Sh har VBA gjort det möjligt för oss att använda det i koden. Så för att hänvisa till det nya bladnamnet kan jag använda Sh.Name.

Argumentbegreppet kommer att vara användbart när du går igenom VBA -händelseexemplen i nästa avsnitt.

Händelser på arbetsboknivå (förklaras med exempel)

Följande är de vanligaste händelserna i en arbetsbok.

EVENT NAMN	VAD UTLÖSAR HÄNDELSEN
Aktivera	När en arbetsbok är aktiverad
Efterspara	När en arbetsbok är installerad som ett tillägg
Innan Spara	När en arbetsbok sparas
Innan Stäng	När en arbetsbok är stängd
Innan Skriv ut	När en arbetsbok skrivs ut
Avaktivera	När en arbetsbok är inaktiverad
Nytt blad	När ett nytt ark läggs till
Öppen	När en arbetsbok öppnas
SheetActivate	När något blad i arbetsboken är aktiverat
SheetBeforeDelete	När något blad raderas
SheetBeforeDoubleClick	När något ark dubbelklickas
SheetBeforeRightClick	När något ark högerklickas
Ark Beräkna	När ett ark beräknas eller beräknas om
ArkDeaktivera	När en arbetsbok är inaktiverad
SheetPivotTableUpdate	När en arbetsbok uppdateras
SheetSelectionChange	När en arbetsbok ändras
FönsterAktivera	När en arbetsbok är aktiverad
FönsterDeaktivera	När en arbetsbok är inaktiverad

Observera att detta inte är en komplett lista. Du hittar hela listan här.

Kom ihåg att koden för Workbook -händelsen är lagrad i ThisWorkbook -objektkodfönstret.

Låt oss nu titta på några användbara arbetsbokshändelser och se hur dessa kan användas i ditt dagliga arbete.

Workbook Open Event

Låt oss säga att du vill visa användaren en vänlig påminnelse om att fylla i sina tidtabeller när de öppnar en specifik arbetsbok.

Du kan använda koden nedan för att göra detta:

Private Sub Workbook_Open () MsgBox "Kom ihåg att fylla i tidrapporten" Slut Sub

Så snart du öppnar arbetsboken som har den här koden kommer den att visa dig en meddelanderuta med det angivna meddelandet.

Det finns några saker att veta när du arbetar med den här koden (eller arbetsbokshändelsekoder i allmänhet):

• Om en arbetsbok har ett makro och du vill spara den måste du spara den i .XLSM -format. Annars skulle makrokoden gå förlorad.
• I exemplet ovan körs händelsekoden endast när makron är aktiverade. Du kan se en gul stapel som ber om tillstånd för att aktivera makron. Tills detta är aktiverat körs inte händelsekoden.
• Händelsekoden för arbetsboken placeras i kodfönstret för ThisWorkbook -objektet.

Du kan ytterligare förfina denna kod och visa meddelandet bara på fredagen.

Koden nedan skulle göra detta:

Private Sub Workbook_Open () wkday = Weekday (Date) If wkday = 6 Då kommer MsgBox "Kom ihåg att fylla i tidrapporten" Avsluta sub

Observera att i veckodagsfunktionen tilldelas söndag värdet 1, måndag är 2 och så vidare.

Därför för fredag ​​har jag använt 6.

Workbook Open -händelse kan vara användbar i många situationer, till exempel:

• När du vill visa ett välkomstmeddelande till personen när en arbetsbok öppnas.
• När du vill visa en påminnelse när arbetsboken öppnas.
• När du alltid vill aktivera ett specifikt kalkylblad i arbetsboken när det öppnas.
• När du vill öppna relaterade filer tillsammans med arbetsboken.
• När du vill fånga datum och tidsstämpel varje gång arbetsboken öppnas.

Arbetsbok NewSheet -händelse

NewSheet -händelse utlöses när du sätter in ett nytt blad i arbetsboken.

Låt oss säga att du vill ange datum- och tidsvärdet i cell A1 på det nyligen infogade arket. Du kan använda koden nedan för att göra detta:

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

Ovanstående kod använder "On Error Resume Next" för att hantera fall där någon infogar ett diagramark och inte ett kalkylblad. Eftersom diagrambladet inte har cell A1, skulle det visa ett fel om "Vid fel återuppta nästa" inte används.

Ett annat exempel kan vara när du vill tillämpa någon grundinställning eller formatering på ett nytt ark så snart det läggs till. Om du till exempel vill lägga till ett nytt ark och vill att det automatiskt ska få ett serienummer (upp till 100), kan du använda koden nedan.

Private Sub Workbook_NewSheet (ByVal Sh As Object) On Error Resume Next With 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

Koden ovan formaterar också lite. Det ger rubrikcellen en blå färg och gör teckensnittet vitt. Det tillämpar också en kant för alla fyllda celler.

Ovanstående kod är ett exempel på hur en kort VBA -kod kan hjälpa dig att stjäla några sekunder varje gång du sätter in ett nytt kalkylblad (om detta är något du måste göra varje gång).

Workbook BeforeSave -händelse

Innan Spara händelse utlöses när du sparar en arbetsbok. Observera att händelsen utlöses först och sedan sparas arbetsboken.

När du sparar en Excel -arbetsbok kan det finnas två möjliga scenarier:

1. Du sparar det för första gången och dialogrutan Spara som visas.
2. Du har redan sparat det tidigare och det kommer helt enkelt att spara och skriva över ändringarna i den redan sparade versionen.

Låt oss nu titta på några exempel där du kan använda händelsen BeforeSave.

Antag att du har en ny arbetsbok som du sparar för första gången, och du vill påminna användaren om att spara den i K -enheten, då kan du använda koden nedan:

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

I filen ovan, om filen aldrig har sparats, är SaveAsUI True och öppnar dialogrutan Spara som. Koden ovan visar meddelandet innan dialogrutan Spara som visas.

Ett annat exempel kan vara att uppdatera datum och tid när filen sparas i en specifik cell.

Koden nedan skulle infoga datum- och tidsstämpeln i cell A1 i Sheet1 när filen sparas.

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

Observera att denna kod körs så snart användaren sparar arbetsboken. Om arbetsboken sparas för första gången visas dialogrutan Spara som. Men koden körs redan när du ser dialogrutan Spara som. Vid denna tidpunkt, om du bestämmer dig för att avbryta och inte spara arbetsboken, skulle datum och tid redan anges i cellen.

Workbook BeforeClose -händelse

Händelsen före stängning händer precis innan arbetsboken stängs.

Koden nedan skyddar alla kalkylblad innan arbetsboken stängs.

Private Sub Workbook_BeforeClose (Cancel as Boolean) Dim sh As Worksheet For each sh In ThisWorkbook.Worksheets sh.Protect Next sh End Sub

Kom ihåg att händelsekoden utlöses så snart du stänger arbetsboken.

En viktig sak att veta om den här händelsen är att det inte spelar någon roll om arbetsboken faktiskt är stängd eller inte.

Om arbetsboken inte har sparats och du får uppmaningen att fråga om du vill spara arbetsboken eller inte, och du klickar på Avbryt, sparas inte din arbetsbok.Händelsekoden hade dock redan körts då.

Arbetsbok BeforePrint -händelse

När du ger utskriftskommandot (eller utskriftskommandot) utlöses händelsen Före utskrift.

Koden nedan beräknar alla kalkylblad innan arbetsboken skrivs ut.

Private Sub Workbook_BeforePrint (Cancel as Boolean) För varje ws i kalkylblad ws.Calculate Next ws End Sub

När användaren skriver ut arbetsboken avbryts händelsen oavsett om han/hon skriver ut hela arbetsboken eller bara en del av den.

Ett annat exempel nedan är på koden som skulle lägga till datum och tid i sidfoten när arbetsboken skrivs ut.

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") Next ws End Sub

Händelser på arbetsbladsnivå (förklaras med exempel)

Arbetsbladshändelser äger rum baserat på utlösarna i kalkylbladet.

Följande är de vanligaste händelserna i ett kalkylblad.

Event namn	Vad som utlöser händelsen
Aktivera	När kalkylbladet är aktiverat
Innan Radera	Innan kalkylbladet raderas
Innan DoubleClick	Innan kalkylbladet dubbelklickas
BeforeRightClick	Innan kalkylbladet högerklickas
Beräkna	Innan kalkylbladet beräknas eller beräknas om
Förändra	När cellerna i kalkylbladet ändras
Avaktivera	När kalkylbladet är inaktiverat
PivotTableUpdate	När pivottabellen i kalkylbladet uppdateras
Urval Ändra	När valet på kalkylbladet ändras

Observera att detta inte är en komplett lista. Du hittar hela listan här.

Kom ihåg att koden för kalkylbladshändelsen lagras i fönstret för kalkylarkets objektkod (i den där du vill att händelsen ska utlösas). Det kan finnas flera kalkylblad i en arbetsbok, och din kod avfyras endast när händelsen äger rum i kalkylbladet där den placeras.

Låt oss nu titta på några användbara arbetsbladshändelser och se hur dessa kan användas i ditt dagliga arbete.

Arbetsblad Aktivera händelse

Denna händelse aktiveras när du aktiverar ett kalkylblad.

Koden nedan skyddar ett ark så snart det aktiveras.

Private Sub Worksheet_Activate () ActiveSheet.Onprotect End Sub

Du kan också använda den här händelsen för att se till att en specifik cell eller ett cellområde (eller ett namngivet område) väljs så snart du aktiverar kalkylbladet. Koden nedan väljer cell D1 så snart du aktiverar arket.

Private Sub Worksheet_Activate () ActiveSheet.Range ("D1"). Välj End Sub

Arbetsbladshändelse

En ändringshändelse aktiveras när du gör en ändring i kalkylbladet.

Tja … inte alltid.

Det finns några förändringar som utlöser händelsen, och några som inte gör det. Här är en lista över några ändringar som inte utlöser händelsen:

• När du ändrar cellens formatering (teckenstorlek, färg, kant, etc.).
• När du slår ihop celler. Detta är förvånande eftersom ibland sammanslagna celler också tar bort innehåll från alla celler utom den övre vänstra.
• När du lägger till, tar bort eller redigerar en cellkommentar.
• När du sorterar ett cellområde.
• När du använder Målsök.

Följande ändringar skulle utlösa händelsen (även om du kanske tror att den inte borde göra det):

• Kopiera och klistra in formatering skulle utlösa händelsen.
• Rensning av formatering skulle utlösa händelsen.
• Att köra en stavningskontroll skulle utlösa händelsen.

Nedan finns en kod som skulle visa en meddelanderuta med adressen till cellen som har ändrats.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "Du har just ändrat" & Target.Address End Sub

Även om detta är ett värdelöst makro, visar det dig hur du använder Target -argumentet för att ta reda på vilka celler som har ändrats.

Låt oss nu se några fler användbara exempel.

Antag att du har ett cellområde (låt oss säga A1: D10) och du vill visa en prompt och fråga användaren om de verkligen ville ändra en cell i det här intervallet eller inte, du kan använda koden nedan.

Den visar en uppmaning med två knappar - Ja och Nej. Om användaren väljer "Ja" är ändringen klar, annars vänds den.

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Du gör en ändring i celler i A1: D10. Är du säker på att du vill ha det?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

I koden ovan kontrollerar vi om målcellen finns i de första 4 kolumnerna och de första 10 raderna. Om så är fallet visas meddelanderutan. Om användaren också valde Nej i meddelanderutan, ändras ändringen (med kommandot Application.Undo).

Observera att jag har använt Application.EnableEvents = False före raden Application.Undo. Och sedan vände jag det genom att använda Application.EnableEvent = True på nästa rad.

Detta behövs eftersom när ångra sker, utlöser det också ändringshändelsen. Om jag inte ställer in EnableEvent till False fortsätter det att utlösa ändringshändelsen.

Du kan också övervaka ändringarna i ett namngivet område med hjälp av ändringshändelsen. Om du till exempel har ett namngivet intervall som heter "DataRange" och du vill visa en prompt om användaren gör en ändring i det här namngivna intervallet kan du använda koden nedan:

Private Sub Worksheet_Change (ByVal Target As Range) Dim DRange As Range Set DRange = Range ("DataRange") If Not Intersect (Target, DRange) Is Nothing Then MsgBox "You just made a change to the Data Range" End If End Sub

Ovanstående kod kontrollerar om cellen/intervallet där du har gjort ändringarna har några celler som är gemensamma för dataområdet. Om den gör det, visas meddelanderutan.

Val av arbetsbok Ändra händelse

Urvalshändelsen utlöses när det finns en urvalsändring i kalkylbladet.

Koden nedan beräknar arket igen så snart du ändrar valet.

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

Ett annat exempel på denna händelse är när du vill markera den aktiva raden och kolumnen i den markerade cellen.

Något som visas nedan:

Följande kod kan göra detta:

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) Avsluta med Avsluta Sub

Koden tar först bort bakgrundsfärgen från alla celler och applicerar sedan den som nämns i koden på den aktiva raden och kolumnen.

Och det är problemet med den här koden. Att den tar bort färg från alla celler.

Om du vill markera den aktiva raden/kolumnen medan du behåller färgen i andra celler intakta, använder du tekniken som visas i den här självstudien.

Arbetsbok DoubleClick -händelse

Detta är en av mina favorit kalkylbladshändelser och du kommer att se en hel del självstudier där jag har använt detta (som den här eller den här).

Denna händelse utlöses när du dubbelklickar på en cell.

Låt mig visa dig hur fantastiskt det här är.

Med koden nedan kan du dubbelklicka på en cell och den kommer att tillämpa en bakgrundsfärg, ändra teckensnittsfärgen och göra texten i cellen fet;

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

Detta kan vara användbart när du går igenom en lista med celler och vill markera några utvalda. Medan du kan använda F4 -tangenten för att upprepa det sista steget, skulle den bara kunna tillämpa en formatering. Med denna dubbelklickhändelse kan du tillämpa alla tre med bara ett dubbelklick.

Observera att i koden ovan har jag gjort värdet Cancel = True.

Detta görs så att standardåtgärden för dubbelklick inaktiveras - vilket är att komma in i redigeringsläget. Med Avbryt = Sant, skulle Excel inte få dig till redigeringsläge när du dubbelklickar på cellen.

Här är ett annat exempel.

Om du har en att-göra-lista i Excel kan du använda dubbelklickhändelsen för att tillämpa genomstrykningsformatet för att markera uppgiften som avslutad.

Något som visas nedan:

Här är koden som gör detta:

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

Observera att i den här koden har jag dubbelklickat som en växlingshändelse. När du dubbelklickar på en cell kontrollerar den om formatet för genomslag redan har tillämpats. Om det har varit det, dubbelklickar du på att ta bort genomstrykningsformatet, och om det inte har varit det tillämpas genomstrykningsformatet.

Excel VBA OnTime -evenemang

Händelserna som vi hittills har sett i den här artikeln var associerade med ett av Excel -objekten, vare sig det var arbetsboken, kalkylbladet, diagramarket eller UserForms, etc.

OnTime -händelsen är annorlunda än andra händelser eftersom den kan lagras i den vanliga VBA -modulen (medan de andra skulle placeras i kodfönstret för objekt som ThisWorkbook eller Worksheets eller UserForms).

Inom den vanliga VBA -modulen används den som en metod för applikationsobjektet.

Anledningen till att detta anses vara en händelse är att det kan utlösas baserat på den tid du anger. Om jag till exempel vill att arket ska räkna om var 5: e minut kan jag använda OnTime -händelsen för det.

Eller, om jag vill visa ett meddelande/påminnelse vid en viss tid på dagen, kan jag använda OnTime -händelsen.

Nedan finns en kod som visar ett meddelande kl. 14.00 varje dag.

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

Kom ihåg att du måste placera den här koden i den vanliga VBA -modulen,

Även om OnTime -händelsen skulle utlösas vid den angivna tiden måste du köra makrot manuellt när som helst. När du har kört makrot väntar det tills klockan är 14.00 och kallar sedan makrot "ShowMessage".

ShowMessage -makrot visar sedan meddelandet.

OnTime -händelsen tar fyra argument:

Application.OnTime (Tidigaste tid, Procedur, Senaste tid, Schema)

• Tidigaste tid: Den tid då du vill köra proceduren.
• Procedur: Namnet på proceduren som ska köras.
• Senaste tid (valfritt): Om en annan kod körs och din angivna kod inte kan köras vid den angivna tiden kan du ange den senaste tiden som den ska vänta på. Det kan till exempel vara EarliestTime + 45 (vilket betyder att det kommer att vänta i 45 sekunder för att den andra proceduren ska bli klar). Om proceduren inte kan köras efter 45 sekunder, överges den. Om du inte anger detta skulle Excel vänta tills koden kan köras och sedan köra den.
• Schema (valfritt): Om den är inställd på True schemaläggs den nya tidsproceduren. Om False, avbryter den tidigare inställda proceduren. Som standard är detta sant.

I exemplet ovan använde vi bara de två första argumenten.

Låt oss titta på ett annat exempel.

Koden nedan uppdaterar kalkylbladet var 5: e minut.

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

Ovanstående kod skulle uppdatera kalkylbladet var 5: e minut.

Den använder funktionen Nu för att bestämma den aktuella tiden och lägger sedan till 5 minuter till den aktuella tiden.

OnTime -evenemanget fortsätter att köras tills du stoppar det. Om du stänger arbetsboken och Excel -programmet fortfarande körs (andra arbetsböcker är öppna) öppnas arbetsboken som har OnTime -händelsen i sig igen.

Detta hanteras bättre genom att specifikt stoppa OnTime -evenemanget.

I koden ovan har jag StopRefresh -koden, men du måste köra den för att stoppa OnTime -händelsen. Du kan göra detta manuellt, tilldela det till en knapp och göra detta genom att trycka på knappen eller ringa det från Workbook Close -händelsen.

Private Sub Workbook_BeforeClose (Cancel As Boolean) Call StopRefresh End Sub

Ovanstående "BeforeClose" -händelsekod går i ThisWorkbook -kodfönstret.

Excel VBA OnKey -händelse

När du arbetar med Excel fortsätter den att övervaka de tangenttryckningar du använder. Detta gör att vi kan använda knapptryckningar som utlösare för en händelse.

Med OnKey -händelse kan du ange en tangenttryckning (eller en kombination av tangenttryckningar) och koden som ska köras när tangenttryckningen används. När du trycker på dessa knapptryckningar körs koden för den.

Precis som OnTime -evenemanget måste du ha ett sätt att avbryta OnKey -evenemanget. När du ställer in OnKey -händelsen för en viss knapptryckning blir den tillgänglig i alla öppna arbetsböcker.

Innan jag visar ett exempel på hur du använder OnKey -evenemanget, låt mig först dela de nyckelkoder som är tillgängliga för dig i VBA.

NYCKEL	KODA
Backspace	{BACKSPACE} eller {BS}
Ha sönder	{HA SÖNDER}
Caps Lock	{CAPS LOCK}
Radera	{DELETE} eller {DEL}
Nedåtpil	{NER}
Slutet	{SLUTET}
Stiga på	~
Ange (på den nueriska knappsatsen)	{STIGA PÅ}
Fly	{ESCAPE} eller {ESC}
Hem	{HEM}
Ins	{FÖRA IN}
Vänster pil	{VÄNSTER}
Num lock	{NUM LOCK}
Sida ned	{PGDN}
Sida upp	{PGUP}
Höger pil	{RÄTT}
Scroll Lock	{SCROLLOCK}
Flik	{FLIK}
Uppåtpil	{UPP}
F1 till F15	{F1} till {F15}

När du behöver använda någon onkey -händelse måste du använda koden för den.

Tabellen ovan har koder för enkla knapptryckningar.

Du kan också kombinera dessa med följande koder:

• Flytta: + (Plustecken)
• Kontrollera: ^ (Markör)
• Alt: % (Procentsats)

Till exempel, för Alt F4, måste du använda koden: "%{F4}” - där % är för ALT -tangenten och {F4} är ​​för F4 -tangenten.

Låt oss nu titta på ett exempel (kom ihåg att koden för OnKey -händelser är placerade i den vanliga VBA -modulen).

När du trycker på knappen PageUp eller PageDown hoppar den 29 rader ovanför/under den aktiva cellen (det är åtminstone vad den gör på min bärbara dator).

Om du vill att den bara ska hoppa 5 rader åt gången kan du använda koden nedan:

Sub PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub Sub PageUpMod () Vid fel Återuppta nästa ActiveCell.Offset (-5, 0) .Aktivera Slut Sub Sub PageDownMod () Vid fel Återuppta Nästa ActiveCell.Offset (5, 0) .Aktivera End Sub

När du kör den första delen av koden körs OnKey -händelserna. När detta väl har utförts, skulle PageUp och PageDown -tangenten bara få markören att hoppa 5 rader åt gången.

Observera att vi har använt "Vid fel Återuppta nästa" för att se till att fel ignoreras. Dessa fel kan uppstå när du trycker på PageUp -tangenten även när du är högst upp i kalkylbladet. Eftersom det inte finns fler rader att hoppa, visar koden ett fel. Men eftersom vi har använt "On Error Resume Next" kommer det att ignoreras.

För att säkerställa att dessa OnKey -händelser är tillgängliga måste du köra den första delen av koden. Om du vill att detta ska vara tillgängligt så snart du öppnar arbetsboken kan du placera detta i ThisWorkbook -kodfönstret.

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

Nedanstående kod återställer nycklarna till deras normala funktionalitet.

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

När du inte anger det andra argumentet i OnKey -metoden återställer tangenttryckningen till dess vanliga funktionalitet.

Om du vill avbryta funktionen för ett tangenttryck, så att Excel inte gör någonting när tangenttryckningen används, måste du använda en tom sträng som det andra argumentet.

I koden nedan skulle Excel inte göra någonting när vi använder tangenterna PageUp eller PageDown.

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

Inaktivera händelser i VBA

Ibland kan du behöva inaktivera händelser för att din kod ska fungera korrekt.

Anta till exempel att jag har ett intervall (A1: D10) och jag vill visa ett meddelande när en cell ändras i detta område. Så jag visar en meddelanderuta och frågar användaren om de är säkra på att de vill göra ändringen. Om svaret är Ja, görs ändringen, och om svaret är Nej, skulle VBA ångra det.

Du kan använda koden nedan:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Du gör en ändring i celler i A1: D10. Är du säker på att du vill ha det?", vbYesNo) End If If Ans = vbNo Then Application.Ondo End If End Sub

Problemet med den här koden är att när användaren väljer Nej i meddelandefältet, är åtgärden omvänd (som jag har använt Application.Undo).

När ångra sker och värdet ändras tillbaka till det ursprungliga, utlöses VBA -ändringshändelsen igen, och användaren visas igen samma meddelanderuta.

Det betyder att du kan fortsätta att klicka på NEJ i meddelanderutan och det kommer att dyka upp. Detta händer när du har fastnat i den oändliga slingan i det här fallet.

För att undvika sådana fall måste du inaktivera händelser så att ändringshändelsen (eller någon annan händelse) inte utlöses.

Följande kod skulle fungera bra i det här fallet:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Du gör en ändring i celler i A1: D10. Är du säker på att du vill ha det?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = Falsk Application.Undo Application.EnableEvents = True End If End Sub

I koden ovan, precis ovanför raden Application.Undo, har vi använt - Application.EnableEvents = False.

Att ställa in EnableEvents till False skulle inte utlösa någon händelse (i den aktuella eller öppna arbetsböcker).

När vi har slutat ångra åtgärden kan vi byta tillbaka egenskapen EnableEvents till True.

Tänk på att inaktivering av händelser påverkar alla arbetsböcker som för närvarande öppnas (eller öppnas medan EnableEvents är inställd på False). Till exempel, som en del av koden, om du öppnar en ny arbetsbok, fungerar inte Workbook Open -händelsen.

Händelsernas inverkan Ångra stacken

Låt mig först berätta vad en Ångra stack är.

När du arbetar i Excel fortsätter den att övervaka dina handlingar. När du gör ett misstag kan du alltid använda Ctrl + Z för att gå tillbaka till föregående steg (dvs ångra din aktuella åtgärd).

Om du trycker två gånger på Ctrl + Z tar det två steg tillbaka. Dessa steg som du har utfört lagras som en del av Ångra -stacken.

Varje händelse som ändrar kalkylbladet förstör denna Ångra -stack.Det betyder att om jag har gjort fem saker innan jag utlöser en händelse kommer jag inte att kunna använda Control + Z för att gå tillbaka till de tidigare stegen. Att aktivera händelsen har förstört den stacken för mig.

I koden nedan använder jag VBA för att ange tidsstämpeln i cell A1 när det ändras i kalkylbladet.

Private Sub Worksheet_Change (ByVal Target As Range) Application.EnableEvents = Falskt intervall ("A1"). Value = Format (nu, "dd-mmm-åååå hh: mm: ss") Application.EnableEvents = True End Sub

Eftersom jag gör en ändring i kalkylbladet kommer det att förstöra ångestacken.

Observera också att detta inte endast är begränsat till evenemang.

Om du har en kod som är lagrad i en vanlig VBA -modul och du gör en ändring i kalkylbladet skulle det också förstöra ångra -stacken i Excel.

Till exempel anger koden nedan helt enkelt texten "Hej" i cell A1, men även att köra detta skulle förstöra ångestacken.

SubtypHello () Område ("A1"). Value = "Hello" End Sub

Du kanske också gillar följande Excel VBA -självstudier:

• Arbeta med celler och intervall i Excel VBA.
• Arbeta med kalkylblad i Excel VBA.
• Arbeta med arbetsböcker i Excel VBA.
• Excel VBA -slingor - Den ultimata guiden.
• Använda IF Then Else Statement i Excel VBA.
• För nästa loop i Excel.
• Skapa användardefinierade funktioner i Excel VBA.
• Hur man skapar och använder tillägg i Excel.
• Skapa och återanvänd makron genom att spara i Personal Macro Workbook.