Excel VBA Events - En nem (og komplet) vejledning

Når du opretter eller optager en makro i Excel, skal du køre makroen for at udføre trinene i koden.

Et par måder at køre en makro på inkluderer at bruge makrodialogboksen, tildele makroen til en knap, bruge en genvej osv.

Bortset fra disse brugerinitierede makroudførelser kan du også bruge VBA-hændelser til at køre makroen.

Excel VBA Events - Introduktion

Lad mig først forklare, hvad der er en begivenhed i VBA.

En begivenhed er en handling, der kan udløse udførelsen af ​​den angivne makro.

For eksempel, når du åbner en ny projektmappe, er det en begivenhed. Når du indsætter et nyt regneark, er det en begivenhed. Når du dobbeltklikker på en celle, er det en begivenhed.

Der er mange sådanne begivenheder i VBA, og du kan oprette koder til disse begivenheder. Det betyder, at så snart en hændelse forekommer, og hvis du har angivet en kode for denne hændelse, ville denne kode øjeblikkeligt blive eksekveret.

Excel gør dette automatisk, så snart det bemærker, at en begivenhed har fundet sted. Så du behøver kun at skrive koden og placere den i den korrekte hændelsessubrutine (dette er dækket senere i denne artikel).

Hvis du f.eks. Indsætter et nyt regneark, og du vil have et års præfiks, kan du skrive koden til det.

Når nogen nu indsætter et nyt regneark, ville denne kode automatisk blive eksekveret og tilføje årspræfikset til regnearkets navn.

Et andet eksempel kan være, at du vil ændre cellens farve, når nogen dobbeltklikker på den. Du kan bruge dobbeltklikhændelsen til dette.

På samme måde kan du oprette VBA -koder til mange sådanne begivenheder (som vi vil se senere i denne artikel).

Nedenfor er en kort visualisering, der viser dobbeltklikhændelsen i aktion. Så snart jeg dobbeltklikker på celle A1. Excel åbner øjeblikkeligt en meddelelsesboks, der viser adressen på cellen.

Dobbeltklik er en begivenhed, og visning af meddelelsesboksen er det, jeg har angivet i koden, når dobbeltklikhændelsen finder sted.

Selvom ovenstående eksempel er en ubrugelig begivenhed, håber jeg, at det hjælper dig med at forstå, hvad begivenheder virkelig er.

Forskellige typer Excel VBA -begivenheder

Der er forskellige objekter i Excel - såsom Excel selv (som vi ofte omtaler som applikationen), projektmapper, regneark, diagrammer osv.

Hver af disse objekter kan have forskellige begivenheder forbundet med det. For eksempel:

• Hvis du opretter en ny projektmappe, er det en begivenhed på applikationsniveau.
• Hvis du tilføjer et nyt regneark, er det en hændelse på projektmappeniveau.
• Hvis du ændrer værdien i en celle i et ark, er det en regnearkshændelse.

Nedenfor er de forskellige typer begivenheder, der findes i Excel:

1. Hændelser på regnearksniveau: Dette er de typer hændelser, der ville udløse baseret på de handlinger, der blev foretaget i regnearket. Eksempler på disse begivenheder omfatter ændring af en celle i regnearket, ændring af markeringen, dobbeltklik på en celle, højreklik på en celle osv.
2. Arrangementer på arbejdsbogsniveau: Disse begivenheder ville blive udløst baseret på handlingerne på projektmappeniveau. Eksempler på disse begivenheder omfatter tilføjelse af et nyt regneark, gemning af projektmappen, åbning af projektmappen, udskrivning af en del eller hele projektmappen osv.
3. Begivenheder på applikationsniveau: Dette er de hændelser, der forekommer i Excel -applikationen. Eksempel på disse ville omfatte lukning af en hvilken som helst af de åbne projektmapper eller åbning af en ny projektmappe.
4. UserForm -begivenheder: Disse hændelser ville blive udløst baseret på handlingerne i 'UserForm'. Eksempler på disse inkluderer initialisering af en UserForm eller klik på en knap i UserForm.
5. Chart begivenheder: Dette er begivenheder relateret til diagramarket. Et diagramark er anderledes end et regneark (det er her de fleste af os er vant til at arbejde i Excel). Formålet med et diagramark er at holde et diagram. Eksempler på sådanne begivenheder ville omfatte ændring af diagrammets serie eller ændring af diagrammet.
6. OnTime og OnKey Events: Dette er to begivenheder, der ikke passer ind i nogen af ​​ovenstående kategorier. Så jeg har angivet disse separat. "OnTime" -hændelse giver dig mulighed for at udføre en kode på et bestemt tidspunkt eller efter et bestemt tidspunkt er gået. "OnKey" -hændelse giver dig mulighed for at udføre en kode, når der bruges et bestemt tastetryk (eller en kombination af tastetryk).

Hvor placeres den begivenhedsrelaterede kode

I ovenstående afsnit dækkede jeg de forskellige typer arrangementer.

Baseret på hændelsestypen skal du sætte koden i det relevante objekt.

For eksempel, hvis det er en regnearksrelateret hændelse, skal den gå i kodevinduet i regnearkobjektet. Hvis det er relateret til projektmappe, skal det gå i kodevinduet for et projektmappeobjekt.

I VBA har forskellige objekter - såsom regneark, arbejdsbøger, diagramark, brugerformularer osv. Deres egne kodevinduer. Du skal sætte hændelseskoden i det relevante objekts vindue. For eksempel - hvis det er en hændelse på projektmappeniveau, skal du have hændelseskoden i vinduet Arbejdsbogskode.

De følgende afsnit dækker de steder, hvor du kan sætte begivenhedskoden:

I vinduet Regnskabskode

Når du åbner VB Editor (ved hjælp af tastaturgenvej ALT + F11), vil du bemærke regnearksobjektet i Project Explorer. For hvert regneark i projektmappen ser du et objekt.

Når du dobbeltklikker på det regnearksobjekt, hvor du vil placere koden, åbner det kodevinduet for det pågældende regneark.

Selvom du kan begynde at skrive koden fra bunden, er det meget bedre at vælge begivenheden fra en liste over muligheder og lade VBA automatisk indsætte den relevante kode for den valgte begivenhed.

For at gøre dette skal du først vælge regneark fra rullemenuen øverst til venstre i kodevinduet.

Efter at have valgt regneark fra rullemenuen, får du en liste over alle de begivenheder, der er relateret til regnearket. Du kan vælge den, du vil bruge, fra rullemenuen øverst til højre i kodevinduet.

Så snart du vælger begivenheden, vil den automatisk indtaste den første og sidste linje i koden for den valgte begivenhed. Nu kan du tilføje din kode mellem de to linjer.

Bemærk: Så snart du vælger regneark fra rullemenuen, vil du bemærke, at to linjer med kode vises i kodevinduet. Når du har valgt den begivenhed, du vil have koden til, kan du slette de linjer, der vises som standard.

Bemærk, at hvert regneark har et eget kodevindue. Når du sætter koden til Sheet1, fungerer det kun, hvis hændelsen sker i Sheet1.

I vinduet ThisWorkbook Code

Ligesom regneark, hvis du har en hændelseskode på projektmappeniveau, kan du placere den i ThisWorkbook -kodevinduet.

Når du dobbeltklikker på ThisWorkbook, åbner det kodevinduet for det.

Du skal vælge Workbook fra rullemenuen øverst til venstre i kodevinduet.

Efter at have valgt projektmappe i rullemenuen får du en liste over alle de begivenheder, der er relateret til projektmappen. Du kan vælge den, du vil bruge, fra rullemenuen øverst til højre i kodevinduet.

Så snart du vælger begivenheden, vil den automatisk indtaste den første og sidste linje i koden for den valgte begivenhed. Nu kan du tilføje din kode mellem de to linjer.

Bemærk: Så snart du vælger Workbook fra rullemenuen, vil du bemærke, at to linjer med kode vises i kodevinduet. Når du har valgt den begivenhed, du vil have koden til, kan du slette de linjer, der vises som standard.

I vinduet Brugerformkode

Når du opretter UserForms i Excel, kan du også bruge UserForm -hændelser til at udføre koder baseret på specifikke handlinger. For eksempel kan du angive en kode, der skal udføres, når der klikkes på knappen.

Mens arkobjekterne og ThisWorkbook -objekterne allerede er tilgængelige, når du åbner VB Editor, er UserForm noget, du skal oprette først.

For at oprette en UserForm skal du højreklikke på et af objekterne, gå til Indsæt og klik på UserForm.

Dette ville indsætte et UserForm -objekt i projektmappen.

Når du dobbeltklikker på UserForm (eller et af de objekter, du tilføjer til UserForm), åbner det kodevinduet for UserForm.

Ligesom regneark eller ThisWorkbook kan du vælge begivenheden, og den vil indsætte den første og den sidste linje for denne begivenhed. Og så kan du tilføje koden midt i den.

I vinduet Diagramkode

I Excel kan du også indsætte diagramark (som er forskellige fra regneark). Et diagramark er kun beregnet til at indeholde diagrammer.

Når du har indsat et diagramark, vil du kunne se diagramarkobjektet i VB Editor.

Du kan tilføje hændelseskoden til diagramarkets kodevindue ligesom vi gjorde i regnearket.

Dobbeltklik på diagramarkobjektet i Project Explorer. Dette åbner kodevinduet for diagramarket.

Nu skal du vælge Chart fra rullemenuen øverst til venstre i kodevinduet.

Efter at have valgt Chart fra rullemenuen, får du en liste over alle de begivenheder, der er relateret til diagramarket. Du kan vælge den, du vil bruge, fra rullemenuen øverst til højre i kodevinduet.

Bemærk: Så snart du vælger Chart fra rullemenuen, vil du bemærke, at to linjer med kode vises i kodevinduet. Når du har valgt den begivenhed, du vil have koden til, kan du slette de linjer, der vises som standard.

I klassemodul

Klassemoduler skal indsættes ligesom UserForms.

Et klassemodul kan indeholde kode relateret til applikationen - hvilket ville være Excel selv og de integrerede diagrammer.

Jeg vil dække klassemodulet som en separat tutorial i de kommende uger.

Bemærk, at bortset fra OnTime- og OnKey -begivenheder kan ingen af ​​ovenstående hændelser gemmes i det almindelige VBA -modul.

Forstå begivenhedssekvensen

Når du udløser en begivenhed, sker det ikke isoleret. Det kan også føre til en sekvens af flere udløsere.

Når du f.eks. Indsætter et nyt regneark, sker der følgende ting:

1. Et nyt regneark tilføjes
2. Det forrige regneark bliver deaktiveret
3. Det nye regneark aktiveres

Selvom du i de fleste tilfælde ikke behøver at bekymre dig om sekvensen, hvis du opretter komplekse koder, der er afhængige af hændelser, er det bedre at kende sekvensen for at undgå uventede resultater.

Forstå argumentenes rolle i VBA -begivenheder

Inden vi springer til begivenhedseksempler og de fantastiske ting, du kan gøre med det, er der et vigtigt koncept, jeg skal dække.

I VBA -begivenheder ville der være to typer koder:

• Uden argumenter
• Med argumenter

Og i dette afsnit vil jeg hurtigt dække argumenternes rolle.

Nedenfor er en kode, der ikke har noget argument (parentesen er tom):

Private Sub Workbook_Open () MsgBox "Husk at udfylde timesedlen" Slut Sub

Med ovenstående kode, når du åbner en projektmappe, viser den ganske enkelt en meddelelsesboks med beskeden - "Husk at udfylde timesedlen".

Lad os nu se på en kode, der har et argument.

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

Ovenstående kode bruger Sh -argumentet, der er defineret som en objekttype. Sh -argumentet kan være et regneark eller et diagramark, da ovenstående hændelse udløses, når et nyt ark tilføjes.

Ved at tildele det nye ark, der føjes til projektmappen til objektvariablen Sh, har VBA gjort det muligt for os at bruge det i koden. Så for at henvise til det nye arknavn, kan jeg bruge Sh.Name.

Argumentbegrebet vil være nyttigt, når du går igennem eksemplerne på VBA -begivenheder i de næste afsnit.

Arrangementer på arbejdsbogsniveau (forklaret med eksempler)

Følgende er de mest almindeligt anvendte begivenheder i en projektmappe.

Hændelsesnavn	HVAD UDLØSER Hændelsen
Aktiver	Når en projektmappe er aktiveret
Efter Gem	Når en projektmappe er installeret som et tilføjelsesprogram
Før Gem	Når en projektmappe er gemt
Før Luk	Når en projektmappe lukkes
Inden Udskriv	Når en projektmappe udskrives
Deaktiver	Når en projektmappe er deaktiveret
Nyt ark	Når der tilføjes et nyt ark
Åben	Når en projektmappe åbnes
Ark Aktiver	Når et ark i projektmappen er aktiveret
SheetBeforeDelete	Når et ark slettes
SheetBeforeDoubleClick	Når et ark er dobbeltklikket
SheetBeforeRightClick	Når et ark er højreklikket
Ark Beregn	Når et ark beregnes eller genberegnes
Ark Deaktiver	Når en projektmappe er deaktiveret
SheetPivotTableUpdate	Når en projektmappe opdateres
SheetSelectionChange	Når en projektmappe ændres
Vindue Aktiver	Når en projektmappe er aktiveret
Vindue Deaktiver	Når en projektmappe er deaktiveret

Bemærk, at dette ikke er en komplet liste. Du kan finde den komplette liste her.

Husk, at koden til Workbook -hændelse er gemt i vinduet ThisWorkbook -objektkode.

Lad os nu se på nogle nyttige projektmappehændelser og se, hvordan disse kan bruges i dit daglige arbejde.

Arbejdsbog Åben begivenhed

Lad os sige, at du vil vise brugeren en venlig påmindelse om at udfylde deres timesedler, når de åbner en bestemt projektmappe.

Du kan bruge nedenstående kode til at gøre dette:

Private Sub Workbook_Open () MsgBox "Husk at udfylde timesedlen" Slut Sub

Nu, så snart du åbner den projektmappe, der har denne kode, viser den dig en meddelelsesboks med den angivne meddelelse.

Der er et par ting at vide, når du arbejder med denne kode (eller Workbook -hændelseskoder generelt):

• Hvis en projektmappe har en makro, og du vil gemme den, skal du gemme den i .XLSM -format. Ellers ville makrokoden gå tabt.
• I ovenstående eksempel ville hændelseskoden kun blive udført, når makroerne er aktiveret. Du kan muligvis se en gul bjælke, der beder om tilladelse til at aktivere makroer. Indtil dette er aktiveret, udføres hændelseskoden ikke.
• Workbook -hændelseskoden placeres i kodevinduet for ThisWorkbook -objektet.

Du kan yderligere forfine denne kode og kun vise beskeden fredag.

Nedenstående kode ville gøre dette:

Private Sub Workbook_Open () wkday = Weekday (Date) If wkday = 6 Så MsgBox "Husk at udfylde timesedlen" Slut Sub

Bemærk, at i funktionen Ugedag tildeles søndag værdien 1, mandag er 2 og så videre.

Derfor til fredag ​​har jeg brugt 6.

Workbook Open -begivenhed kan være nyttig i mange situationer, f.eks .:

• Når du vil vise en velkomstbesked til personen, når en projektmappe åbnes.
• Når du vil vise en påmindelse, når projektmappen åbnes.
• Når du altid vil aktivere et specifikt regneark i projektmappen, når det åbnes.
• Når du vil åbne relaterede filer sammen med projektmappen.
• Når du vil registrere dato og klokkeslæt hver gang projektmappen åbnes.

Workbook NewSheet -begivenhed

NewSheet -hændelse udløses, når du indsætter et nyt ark i projektmappen.

Lad os sige, at du vil indtaste dato- og tidsværdien i celle A1 i det nyligt indsatte ark. Du kan bruge nedenstående kode til at gøre dette:

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

Ovenstående kode bruger 'On Error Resume Next' til at håndtere tilfælde, hvor nogen indsætter et diagramark og ikke et regneark. Da diagramark ikke har celle A1, viser det en fejl, hvis 'On Error Resume Next' ikke bruges.

Et andet eksempel kan være, når du vil anvende nogle grundlæggende indstillinger eller formateringer på et nyt ark, så snart det er tilføjet. For eksempel, hvis du vil tilføje et nyt ark og vil have det til automatisk at få et serienummer (op til 100), kan du bruge koden herunder.

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

Ovenstående kode formaterer også lidt. Det giver headercellen en blå farve og gør skrifttypen hvid. Det gælder også en grænse for alle de fyldte celler.

Ovenstående kode er et eksempel på, hvordan en kort VBA -kode kan hjælpe dig med at stjæle et par sekunder hver gang du indsætter et nyt regneark (hvis dette er noget, du skal gøre hver gang).

Workbook BeforeSave Event

Inden Gem hændelse udløses, når du gemmer en projektmappe. Bemærk, at hændelsen først udløses, og derefter gemmes projektmappen.

Når du gemmer en Excel -projektmappe, kan der være to mulige scenarier:

1. Du gemmer det for første gang, og det viser dialogboksen Gem som.
2. Du har allerede gemt det tidligere, og det vil simpelthen gemme og overskrive ændringerne i den allerede gemte version.

Lad os nu se på et par eksempler, hvor du kan bruge BeforeSave -begivenheden.

Antag, at du har en ny projektmappe, som du gemmer for første gang, og du vil minde brugeren om at gemme den i K -drevet, så kan du bruge nedenstående kode:

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 ovenstående kode, hvis filen aldrig er blevet gemt, er SaveAsUI True og åbner dialogboksen Gem som. Ovenstående kode viser meddelelsen, før dialogboksen Gem som.

Et andet eksempel kan være at opdatere dato og klokkeslæt, hvor filen gemmes i en bestemt celle.

Nedenstående kode vil indsætte dato- og tidsstemplet i celle A1 i Sheet1, når filen gemmes.

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

Bemærk, at denne kode udføres, så snart brugeren gemmer projektmappen. Hvis projektmappen gemmes for første gang, viser den en dialogboks Gem som. Men koden udføres allerede, når du ser dialogboksen Gem som. På dette tidspunkt, hvis du beslutter dig for at annullere og ikke gemme projektmappen, ville dato og klokkeslæt allerede være angivet i cellen.

Workbook BeforeClose -begivenhed

Før Luk -hændelse sker lige før projektmappen lukkes.

Nedenstående kode beskytter alle regnearkene, før projektmappen lukkes.

Private Sub Workbook_BeforeClose (Annuller som boolsk) Dim sh som regneark for hver sh i ThisWorkbook.Worksheets sh.Protect Næste sh End Sub

Husk, at hændelseskoden udløses, så snart du lukker projektmappen.

En vigtig ting at vide om denne begivenhed er, at det er ligeglad med, om projektmappen rent faktisk er lukket eller ej.

Hvis projektmappen ikke er blevet gemt, og du får vist prompten, der spørger, om projektmappen skal gemmes eller ej, og du klikker på Annuller, gemmer den ikke din projektmappe.Hændelseskoden ville dog allerede være blevet eksekveret på det tidspunkt.

Workbook BeforePrint -begivenhed

Når du giver kommandoen udskriv (eller kommandoen Udskriv forhåndsvisning), udløses hændelsen Før udskrivning.

Nedenstående kode vil genberegne alle regneark, før din projektmappe udskrives.

Private Sub Workbook_BeforePrint (Annuller som boolsk) For hver ws i regneark ws.Beregn næste ws End Sub

Når brugeren udskriver projektmappen, vil hændelsen blive affyret, uanset om han/hun udskriver hele projektmappen eller kun en del af den.

Et andet eksempel nedenfor er på koden, der tilføjer dato og klokkeslæt til sidefoden, når projektmappen udskrives.

Private Sub Workbook_BeforePrint (Annuller som boolsk) Dim ws Som regneark for hver gang i ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Printed On-" & Format (Now, "dd-mmm-åååå hh: mm") Næste ws End Sub

Hændelser på regnearksniveau (forklaret med eksempler)

Regnearkhændelser finder sted baseret på udløserne i regnearket.

Følgende er de mest almindeligt anvendte hændelser i et regneark.

Begivenhedsnavn	Hvad der udløser begivenheden
Aktiver	Når regnearket er aktiveret
Inden Slet	Inden regnearket slettes
Før DoubleClick	Inden regnearket er dobbeltklikket
BeforeRightClick	Inden regnearket højreklikkes
Beregn	Inden regnearket beregnes eller genberegnes
Lave om	Når cellerne i regnearket ændres
Deaktiver	Når regnearket er deaktiveret
PivotTableUpdate	Når pivottabellen i regnearket opdateres
Valg Ændre	Når markeringen på regnearket ændres

Bemærk, at dette ikke er en komplet liste. Du kan finde den komplette liste her.

Husk, at koden til regnearkhændelse er gemt i regnearkets objektkodevindue (i den, hvor du ønsker, at hændelsen skal udløses). Der kan være flere regneark i en projektmappe, og din kode udløses først, når hændelsen finder sted i det regneark, hvor den er placeret.

Lad os nu se på nogle nyttige regnearkhændelser og se, hvordan disse kan bruges i dit daglige arbejde.

Regneark Aktiver begivenhed

Denne begivenhed udløses, når du aktiverer et regneark.

Nedenstående kode afbeskytter et ark, så snart det er aktiveret.

Privat under regneark_Aktiver () ActiveSheet.Beskyt slut sub

Du kan også bruge denne hændelse til at sikre, at en bestemt celle eller et område af celler (eller et navngivet område) er valgt, så snart du aktiverer regnearket. Nedenstående kode vælger celle D1, så snart du aktiverer arket.

Private Sub Worksheet_Activate () ActiveSheet.Range ("D1"). Vælg Afslut sub

Hændelse til ændring af regneark

En ændringshændelse udløses, når du foretager en ændring i regnearket.

Tja… ikke altid.

Der er nogle ændringer, der udløser begivenheden, og nogle, der ikke gør det. Her er en liste over nogle ændringer, der ikke udløser begivenheden:

• Når du ændrer cellens formatering (skriftstørrelse, farve, kant osv.).
• Når du fletter celler. Dette er overraskende, da nogle gange fusionerer celler også fjerner indhold fra alle cellerne undtagen øverst til venstre.
• Når du tilføjer, sletter eller redigerer en cellekommentar.
• Når du sorterer en række celler.
• Når du bruger Målsøgning.

Følgende ændringer vil udløse begivenheden (selvom du måske tror, ​​den ikke burde):

• Kopiering og indsætning af formatering ville udløse begivenheden.
• Rydning af formatering ville udløse hændelsen.
• Kørsel af en stavekontrol ville udløse begivenheden.

Nedenfor er en kode, der viser en meddelelsesboks med adressen på den celle, der er blevet ændret.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "Du har lige ændret" & Target.Address End Sub

Selvom dette er en ubrugelig makro, viser den dig, hvordan du bruger Target -argumentet til at finde ud af, hvilke celler der er blevet ændret.

Lad os nu se et par flere nyttige eksempler.

Antag, at du har en række celler (lad os sige A1: D10), og du vil vise en prompt og spørge brugeren, om de virkelig ville ændre en celle i dette område eller ej, du kan bruge nedenstående kode.

Det viser en prompt med to knapper - Ja og Nej. Hvis brugeren vælger 'Ja', er ændringen udført, ellers vendes den.

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Du foretager en ændring i celler i A1: D10. Er du sikker på, at du vil have det?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

I ovenstående kode kontrollerer vi, om målcellen er i de første 4 kolonner og de første 10 rækker. Hvis det er tilfældet, vises meddelelsesboksen. Hvis brugeren også valgte Nej i meddelelsesboksen, ændres ændringen (ved hjælp af kommandoen Application.Undo).

Bemærk, at jeg har brugt Application.EnableEvents = Falsk før linjen Application.Undo. Og så vendte jeg det ved at bruge Application.EnableEvent = True i den næste linje.

Dette er nødvendigt, da når fortrydelsen sker, udløser det også ændringshændelsen. Hvis jeg ikke indstiller EnableEvent til Falsk, vil det blive ved med at udløse ændringshændelsen.

Du kan også overvåge ændringerne i et navngivet område ved hjælp af ændringshændelsen. Hvis du f.eks. Har et navngivet område kaldet "DataRange", og du vil vise en prompt, hvis brugeren foretager en ændring i dette navngivne område, kan du bruge koden herunder:

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 "Du har lige foretaget en ændring i dataområdet" End If End Sub

Ovenstående kode kontrollerer, om cellen/området, hvor du har foretaget ændringerne, har nogen celler, der er fælles for dataområdet. Hvis det gør det, viser det meddelelsesboksen.

Valg af projektmappe Skift begivenhed

Valgændringshændelsen udløses, når der er en markeringsændring i regnearket.

Nedenstående kode vil genberegne arket, så snart du ændrer valget.

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

Et andet eksempel på denne begivenhed er, når du vil fremhæve den aktive række og kolonne i den markerede celle.

Noget som vist herunder:

Følgende kode kan gøre dette:

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) Slut med Afslut Sub

Koden fjerner først baggrundsfarven fra alle cellerne og anvender derefter den, der er nævnt i koden, på den aktive række og kolonne.

Og det er problemet med denne kode. At den fjerner farve fra alle celler.

Hvis du vil fremhæve den aktive række/kolonne, mens farven i andre celler forbliver intakt, skal du bruge den teknik, der er vist i denne vejledning.

Workbook DoubleClick -begivenhed

Dette er en af ​​mine foretrukne regnearkbegivenheder, og du vil se en masse selvstudier, hvor jeg har brugt dette (f.eks. Denne eller denne).

Denne hændelse udløses, når du dobbeltklikker på en celle.

Lad mig vise dig, hvor fantastisk det er.

Med nedenstående kode kan du dobbeltklikke på en celle, og den anvender en baggrundsfarve, ændrer skrifttypefarven og gør teksten i cellen fed;

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

Dette kan være nyttigt, når du går igennem en liste med celler og vil fremhæve et par udvalgte. Selvom du kan bruge F4 -tasten til at gentage det sidste trin, ville det kun være i stand til at anvende en formatering. Med denne dobbeltklikshændelse kan du anvende alle tre med blot et dobbeltklik.

Bemærk, at jeg i ovenstående kode har angivet værdien Cancel = True.

Dette gøres, så standardhandlingen med dobbeltklik er deaktiveret - hvilket er at komme i redigeringstilstand. Med Cancel = True ville Excel ikke få dig til at redigere, når du dobbeltklikker på cellen.

Her er et andet eksempel.

Hvis du har en huskeliste i Excel, kan du bruge dobbeltklik på hændelse til at anvende gennemstikningsformatet til at markere opgaven som afsluttet.

Noget som vist herunder:

Her er koden, der gør dette:

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

Bemærk, at i denne kode har jeg dobbeltklikket som en skiftehændelse. Når du dobbeltklikker på en celle, kontrollerer den, om gennemsigtningsformatet allerede er blevet anvendt. Hvis det har været det, skal du dobbeltklikke for at fjerne gennemstikningsformatet, og hvis det ikke har været det, anvendes gennemstregningsformatet.

Excel VBA OnTime -begivenhed

De begivenheder, vi hidtil har set i denne artikel, var forbundet med et af Excel -objekterne, det være sig projektmappen, regnearket, diagramarket eller UserForms osv.

OnTime -hændelse er anderledes end andre hændelser, da den kan gemmes i det almindelige VBA -modul (mens de andre skulle placeres i kodevinduet for objekter som f.eks. ThisWorkbook eller Worksheets eller UserForms).

Inden for det almindelige VBA -modul bruges det som en metode til applikationsobjektet.

Grunden til, at dette betragtes som en hændelse, er, at det kan udløses baseret på det tidspunkt, du angiver. For eksempel, hvis jeg vil have arket til at genberegne hvert 5. minut, kan jeg bruge OnTime -hændelsen til det.

Eller hvis jeg vil vise en besked/påmindelse på et bestemt tidspunkt på dagen, kan jeg bruge OnTime -begivenheden.

Nedenfor er en kode, der viser en besked kl. 14 hver dag.

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

Husk, at du skal placere denne kode i det almindelige VBA -modul,

Selvom OnTime -hændelsen også ville blive udløst på det angivne tidspunkt, skal du til enhver tid køre makroen manuelt. Når du har kørt makroen, venter den til klokken er 14.00 og kalder derefter 'ShowMessage' -makroen.

ShowMessage -makroen viser derefter meddelelsen.

OnTime -begivenheden tager fire argumenter:

Application.OnTime (Tidligste tid, Procedure, Seneste tid, Tidsplan)

• Tidligste tid: Det tidspunkt, hvor du vil køre proceduren.
• Procedure: Navnet på den procedure, der skal køres.
• Seneste tid (valgfrit): Hvis en anden kode kører, og din angivne kode ikke kan køres på det angivne tidspunkt, kan du angive den seneste tid, som den skal vente på. For eksempel kan det være EarliestTime + 45 (hvilket betyder, at det vil vente i 45 sekunder på, at den anden procedure er fuldført). Hvis selv efter 45 sekunder proceduren ikke er i stand til at køre, bliver den opgivet. Hvis du ikke angiver dette, ville Excel vente, indtil koden kan køres, og derefter køre den.
• Tidsplan (valgfrit): Hvis den er indstillet til Sand, planlægger den en ny tidsprocedure. Hvis Falsk, annullerer den den tidligere indstillede procedure. Som standard er dette sandt.

I ovenstående eksempel brugte vi kun de to første argumenter.

Lad os se på et andet eksempel.

Nedenstående kode ville opdatere regnearket hvert 5. minut.

Dim NextRefresh as Date Sub RefreshSheet () ThisWorkbook.Worksheets ("Sheet1"). Beregn NextRefresh = Now + TimeValue ("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh () Ved fejl Genoptag næste Application.OnTime NextRefresh, "RefreshSheet",, False End Sub

Ovenstående kode vil opdatere regnearket hvert 5. minut.

Den bruger funktionen Nu til at bestemme den aktuelle tid og tilføjer derefter 5 minutter til den aktuelle tid.

OnTime -begivenheden vil fortsætte med at køre, indtil du stopper den. Hvis du lukker projektmappen, og Excel -applikationen stadig kører (andre projektmapper er åbne), åbner den projektmappe, der har OnTime -hændelsen, i sig selv igen.

Dette håndteres bedre ved specifikt at stoppe OnTime -arrangementet.

I ovenstående kode har jeg StopRefresh -koden, men du skal udføre den for at stoppe OnTime -begivenheden. Du kan gøre dette manuelt, tildele det til en knap og gøre dette ved at trykke på knappen eller kalde det fra Workbook Close -begivenheden.

Private Sub Workbook_BeforeClose (Cancel as Boolean) Opkald Stop Refresh End Sub

Ovenstående 'BeforeClose' hændelseskode går i ThisWorkbook -kodevinduet.

Excel VBA OnKey -begivenhed

Når du arbejder med Excel, bliver det ved med at overvåge de tastetryk, du bruger. Dette giver os mulighed for at bruge tastetryk som udløser for en begivenhed.

Med OnKey -hændelse kan du angive et tastetryk (eller en kombination af tastetryk) og den kode, der skal udføres, når dette tastetryk bruges. Når der trykkes på disse tastetryk, udføres koden for den.

Ligesom OnTime -begivenhed skal du have en måde at annullere OnKey -begivenheden. Når du indstiller OnKey -begivenheden til et bestemt tastetryk, bliver den også tilgængelig i alle de åbne projektmapper.

Før jeg viser dig et eksempel på at bruge OnKey -begivenheden, lad mig først dele de nøglekoder, der er tilgængelige for dig i VBA.

NØGLE	KODE
Tilbage	{BACKSPACE} eller {BS}
Pause	{PAUSE}
Caps Lock	{CAPS LOCK}
Slet	{DELETE} eller {DEL}
Pil ned	{NED}
Ende	{ENDE}
Gå ind	~
Enter (på det nueriske tastatur)	{GÅ IND}
Flugt	{ESCAPE} eller {ESC}
Hjem	{HJEM}
Ins	{INSERT}
Venstre pil	{VENSTRE}
NumLock	{NUMLOCK}
Side ned	{PGDN}
Side op	{PGUP}
Højre pil	{RET}
Scroll Lock	{SCROLLOCK}
Tab	{TAB}
Pil op	{OP}
F1 til F15	{F1} til {F15}

Når du skal bruge en hvilken som helst onkey -begivenhed, skal du bruge koden til den.

Ovenstående tabel har koderne for enkelte tastetryk.

Du kan også kombinere disse med følgende koder:

• Flytte: + (Plustegn)
• Styring: ^ (Caret)
• Alt: % (Procent)

For eksempel, for Alt F4, skal du bruge koden: “%{F4}” - hvor % er for ALT -tasten og {F4} er for F4 -tasten.

Lad os nu se på et eksempel (husk, at koden til OnKey -begivenheder er placeret i det almindelige VBA -modul).

Når du trykker på PageUp eller PageDown -tasten, hopper den 29 rækker over/under den aktive celle (det er i hvert fald det, den gør på min bærbare computer).

Hvis du kun vil have 5 rækker ad gangen, kan du bruge nedenstående kode:

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 () Ved fejl Genoptag Næste ActiveCell.Offset (5, 0) .Aktiver End Sub

Når du kører den første del af koden, kører den OnKey -begivenhederne. Når dette er udført, ville brug af PageUp og PageDown -tasten kun få markøren til at springe 5 rækker ad gangen.

Bemærk, at vi har brugt 'On Error Resume Next' for at sikre, at fejl ignoreres. Disse fejl kan opstå, når du trykker på PageUp -tasten, selv når du er øverst i regnearket. Da der ikke er flere rækker at springe, viser koden en fejl. Men da vi har brugt 'On Error Resume Next', vil det blive ignoreret.

For at sikre, at disse OnKey -begivenheder er tilgængelige, skal du køre den første del af koden. Hvis du vil have dette tilgængeligt, så snart du åbner projektmappen, kan du placere dette i vinduet ThisWorkbook -kode.

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

Nedenstående kode vil returnere nøglerne til deres normale funktionalitet.

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

Når du ikke angiver det andet argument i OnKey -metoden, vender tastetrykket tilbage til sin normale funktionalitet.

Hvis du vil annullere funktionaliteten af ​​et tastetryk, så Excel ikke gør noget, når tastetrykket bruges, skal du bruge en tom streng som det andet argument.

I nedenstående kode ville Excel ikke gøre noget, når vi bruger PageUp- eller PageDown -tasterne.

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

Deaktivering af begivenheder i VBA

Nogle gange skal du muligvis deaktivere hændelser for at få din kode til at fungere korrekt.

Antag for eksempel, at jeg har et område (A1: D10), og jeg vil vise en meddelelse, når en celle ændres i dette område. Så jeg viser en meddelelsesboks og spørger brugeren, om de er sikre på, at de vil foretage ændringen. Hvis svaret er Ja, foretages ændringen, og hvis svaret er Nej, ville VBA fortryde det.

Du kan bruge nedenstående kode:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Du foretager en ændring i celler i A1: D10. Er du sikker på, at du vil have det?", vbYesNo) End If If Ans = vbNo Then Application.Ondo End If End Sub

Problemet med denne kode er, at når brugeren vælger Nej i meddelelsesboksen, vendes handlingen omvendt (som jeg har brugt Application.Undo).

Når fortrydelsen sker, og værdien ændres tilbage til den oprindelige, udløses VBA -ændringshændelsen igen, og brugeren får igen vist den samme meddelelsesboks.

Det betyder, at du kan fortsætte med at klikke på NEJ i meddelelsesboksen, og det vil blive ved med at dukke op. Dette sker, da du er gået i stå i den uendelige loop i dette tilfælde.

For at undgå sådanne tilfælde skal du deaktivere hændelser, så ændringshændelsen (eller enhver anden hændelse) ikke udløses.

Følgende kode ville fungere godt i dette tilfælde:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Du foretager en ændring i celler i A1: D10. Er du sikker på, at du vil have det?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

I ovenstående kode, lige over linjen Application.Uno, har vi brugt - Application.EnableEvents = False.

Indstilling af EnableEvents til Falsk ville ikke udløse nogen hændelse (i den aktuelle eller i åbne projektmapper).

Når vi har gennemført fortrydelsesoperationen, kan vi skifte egenskaben EnableEvents tilbage til True.

Husk, at deaktivering af hændelser påvirker alle de projektmapper, der aktuelt åbnes (eller åbnes, mens EnableEvents er indstillet til Falsk). For eksempel, som en del af koden, hvis du åbner en ny projektmappe, fungerer Workbook Open -hændelsen ikke.

Begivenheders indvirkning Fortryd stak

Lad mig først fortælle dig, hvad en Fortryd stak er.

Når du arbejder i Excel, bliver det ved med at overvåge dine handlinger. Når du laver en fejl, kan du altid bruge Ctrl + Z til at gå tilbage til det forrige trin (dvs. fortryde din aktuelle handling).

Hvis du trykker to gange på Control + Z, tager det to trin tilbage. Disse trin, du har udført, gemmes som en del af Fortryd -stakken.

Enhver hændelse, der ændrer regnearket, ødelægger denne Fortryd -stak.Det betyder, at hvis jeg har gjort 5 ting, før jeg udløser en begivenhed, vil jeg ikke kunne bruge Control + Z til at gå tilbage til de tidligere trin. At udløse begivenheden har ødelagt den stak for mig.

I nedenstående kode bruger jeg VBA til at indtaste tidsstemplet i celle A1, når der er en ændring i regnearket.

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

Da jeg foretager en ændring i regnearket, ødelægger dette fortrydelsesstakken.

Bemærk også, at dette ikke kun er begrænset til begivenheder.

Hvis du har en kode, der er gemt i et almindeligt VBA -modul, og du foretager en ændring i regnearket, ville det også ødelægge fortrydelsesstakken i Excel.

For eksempel indtaster nedenstående kode blot teksten "Hej" i celle A1, men selv at køre dette ville ødelægge fortrydelsesstakken.

Sub TypeHello () Range ("A1"). Value = "Hello" End Sub

Du kan også lide følgende Excel VBA -vejledninger:

• Arbejde med celler og områder i Excel VBA.
• Arbejde med regneark i Excel VBA.
• Arbejde med projektmapper i Excel VBA.
• Excel VBA Loops - Den ultimative guide.
• Brug af IF Then Else Statement i Excel VBA.
• Til Next Loop i Excel.
• Oprettelse af brugerdefinerede funktioner i Excel VBA.
• Sådan oprettes og bruges tilføjelsesprogrammer i Excel.
• Opret og genbrug makroer ved at gemme i Personal Macro Workbook.