Excel VBA események - Egyszerű (és teljes) útmutató

Amikor makrót hoz létre vagy rögzít az Excelben, futtassa a makrót a kód lépéseinek végrehajtásához.

A makró futtatásának néhány módja magában foglalja a makró párbeszédpanel használatát, a makró gombhoz való hozzárendelését, parancsikon használatát stb.

A felhasználó által kezdeményezett makró-végrehajtásokon kívül a VBA-eseményeket is használhatja a makró futtatásához.

Excel VBA események - Bevezetés

Először hadd magyarázzam meg, mi az esemény a VBA -ban.

Az esemény olyan művelet, amely kiválthatja a megadott makró végrehajtását.

Például amikor új munkafüzetet nyit meg, az esemény. Amikor új munkalapot helyez be, az esemény. Ha duplán kattint egy cellára, az esemény.

Sok ilyen esemény létezik a VBA -ban, és ezekhez az eseményekhez kódokat is létrehozhat. Ez azt jelenti, hogy amint egy esemény bekövetkezik, és ha az adott eseményhez kódot adott meg, a kód azonnal végrehajtásra kerül.

Az Excel automatikusan ezt teszi, amint észreveszi, hogy esemény történt. Tehát csak meg kell írnia a kódot, és el kell helyeznie a megfelelő esemény alprogramba (erről ebben a cikkben később lesz szó).

Például, ha új munkalapot szúr be, és azt szeretné, hogy egy év előtaggal rendelkezzen, akkor írja be a kódot.

Most, amikor valaki új munkalapot szúr be, ez a kód automatikusan végrehajtásra kerül, és hozzáadja az év előtagot a munkalap nevéhez.

Egy másik példa lehet, hogy meg szeretné változtatni a cella színét, ha valaki duplán rákattint. Ehhez használhatja a duplakattintásos eseményt.

Hasonlóképpen számos ilyen eseményhez hozhat létre VBA -kódokat (amint ezt a cikk későbbi részében látni fogjuk).

Az alábbiakban egy rövid kép látható, amely bemutatja a dupla kattintás eseményét működés közben. Amint duplán kattintok az A1 cellára. Az Excel azonnal megnyit egy üzenetmezőt, amely a cella címét mutatja.

A dupla kattintás egy esemény, és az üzenetmező megjelenítése az, amit megadtam a kódban, amikor a dupla kattintás megtörténik.

Bár a fenti példa haszontalan esemény, remélem, segít megérteni, hogy valójában mik az események.

Különböző típusú Excel VBA események

Az Excelben különböző objektumok találhatók - például maga az Excel (amelyre gyakran alkalmazásként hivatkozunk), munkafüzetek, munkalapok, diagramok stb.

Ezen objektumok mindegyikéhez különféle események társulhatnak. Például:

• Ha új munkafüzetet hoz létre, akkor ez egy alkalmazásszintű esemény.
• Ha új munkalapot ad hozzá, ez egy munkafüzet szintű esemény.
• Ha megváltoztatja az értéket egy lap cellájában, akkor ez egy munkalap szintű esemény.

Az alábbiakban az Excelben létező különböző típusú események találhatók:

1. Munkalap szintű események: Ezek azok az eseménytípusok, amelyek a munkalapon végrehajtott műveletek alapján kiváltódnak. Ilyen események például a munkalapon lévő cella módosítása, a kijelölés módosítása, a cellára való dupla kattintás, a jobb gombbal a cellára való kattintás stb.
2. Munkafüzet szintű események: Ezeket az eseményeket a munkafüzet szintjén végrehajtott műveletek alapján váltanák ki. Ilyen események például az új munkalap hozzáadása, a munkafüzet mentése, a munkafüzet megnyitása, egy rész vagy a teljes munkafüzet kinyomtatása stb.
3. Alkalmazás szintű események: Ezek az események fordulnak elő az Excel alkalmazásban. Ilyen például a nyitott munkafüzetek bezárása vagy új munkafüzet megnyitása.
4. UserForm szintű események: Ezeket az eseményeket a „UserForm” műveletei indítják el. Ilyen például a UserForm inicializálása vagy a UserForm egyik gombjának kattintása.
5. Chart események: Ezek a diagramlaphoz kapcsolódó események. A diagramlap más, mint a munkalap (ahol a legtöbben Excelben dolgozunk). A diagramlapok célja a diagram tartása. Ilyen események például a diagram sorozatának megváltoztatása vagy a diagram átméretezése.
6. OnTime és OnKey események: Ez két olyan esemény, amely nem illik a fenti kategóriák egyikébe sem. Tehát ezeket külön felsoroltam. Az „OnTime” esemény lehetővé teszi egy kód végrehajtását egy adott időpontban vagy egy meghatározott idő letelte után. Az „OnKey” esemény lehetővé teszi a kód végrehajtását, amikor egy adott billentyűleütést (vagy billentyűkombinációk kombinációját) használnak.

Hová tegye az eseményekkel kapcsolatos kódot

A fenti részben a különböző típusú eseményeket ismertettem.

Az esemény típusa alapján be kell helyeznie a kódot a megfelelő objektumba.

Például, ha egy munkalappal kapcsolatos eseményről van szó, akkor a munkalap -objektum kódablakában kell szerepelnie. Ha a munkafüzethez kapcsolódik, akkor a munkafüzet -objektum kódablakában kell megjelennie.

A VBA -ban a különböző objektumok - például munkalapok, munkafüzetek, diagramlapok, felhasználói formák stb. - saját kódablakkal rendelkeznek. Az esemény kódját be kell helyeznie a megfelelő objektum kódablakába. Például - ha egy munkafüzet szintű eseményről van szó, akkor a munkafüzet kódablakában meg kell adnia az eseménykódot.

A következő szakaszok azokat a helyeket tartalmazzák, ahol elhelyezheti az eseménykódot:

A munkalap kódablakában

Amikor megnyitja a VB szerkesztőt (az ALT + F11 billentyűparancs használatával), észre fogja venni a munkalap objektumot a Project Explorerben. A munkafüzet minden munkalapján egy objektum látható.

Ha duplán rákattint arra a munkalap-objektumra, amelybe a kódot elhelyezni kívánja, az megnyitja az adott munkalap kódablakát.

Bár kezdheti a kód írását a semmiből, sokkal jobb, ha kiválasztja az eseményt a lehetőségek listájából, és hagyja, hogy a VBA automatikusan beszúrja a kiválasztott esemény megfelelő kódját.

Ehhez először ki kell választania a munkalapot a kódablak bal felső sarkában található legördülő menüből.

Miután kiválasztotta a munkalapot a legördülő menüből, megjelenik a munkalaphoz kapcsolódó összes esemény listája. A használni kívánt elemet a kódablak jobb felső sarkában található legördülő menüből választhatja ki.

Amint kiválasztja az eseményt, automatikusan beírja a kiválasztott esemény kódjának első és utolsó sorát. Most hozzáadhatja a kódot a két sor közé.

Megjegyzés: Amint kiválasztja a Munkalapot a legördülő menüből, két kódsor jelenik meg a kódablakban. Miután kiválasztotta az eseményt, amelyhez kódot szeretne, törölheti az alapértelmezés szerint megjelenő sorokat.

Vegye figyelembe, hogy minden munkalapnak saját kódablaka van. Ha megadja a Sheet1 kódját, az csak akkor működik, ha az esemény a Sheet1 -ben történik.

A ThisWorkbook Code ablakban

A munkalapokhoz hasonlóan, ha rendelkezik munkafüzet szintű eseménykóddal, akkor elhelyezheti azt a ThisWorkbook kódablakban.

Ha duplán kattint a ThisWorkbookra, megnyílik a kódablak.

A kódablak bal felső sarkában található legördülő menüből ki kell választania a Munkafüzetet.

Miután a legördülő menüből kiválasztotta a Munkafüzetet, megjelenik a Munkafüzethez kapcsolódó összes esemény listája. A használni kívánt elemet a kódablak jobb felső sarkában található legördülő menüből választhatja ki.

Amint kiválasztja az eseményt, automatikusan beírja a kiválasztott esemény kódjának első és utolsó sorát. Most hozzáadhatja a kódot a két sor közé.

Megjegyzés: Amint kiválasztja a Munkafüzetet a legördülő menüből, két kódsor jelenik meg a kódablakban. Miután kiválasztotta az eseményt, amelyhez kódot szeretne, törölheti az alapértelmezett sorokat.

A Userform Code ablakban

Amikor UserForms -ot hoz létre az Excelben, a UserForm -eseményekkel is végrehajthat kódokat meghatározott műveletek alapján. Például megadhat egy kódot, amely a gombra kattintáskor kerül végrehajtásra.

Míg a Sheet objektumok és a ThisWorkbook objektumok már elérhetők a VB Editor megnyitásakor, a UserForm az, amit először létre kell hoznia.

UserForm létrehozásához kattintson a jobb gombbal bármelyik objektumra, lépjen a Beszúrás menüpontra, majd kattintson a UserForm elemre.

Ez egy UserForm objektumot illesztene be a munkafüzetbe.

Ha duplán kattint a UserForm-ra (vagy a UserForm-hoz hozzáadott bármely objektumra), az megnyitja a UserForm kódablakát.

A munkalapokhoz vagy a ThisWorkbookhoz hasonlóan most is kiválaszthatja az eseményt, és beilleszti az esemény első és utolsó sorát. Ezután hozzáadhatja a kódot a közepére.

A Chart Code ablakban

Az Excelben diagramlapokat is beszúrhat (amelyek különböznek a munkalapoktól). A diagramlap csak diagramokat tartalmaz.

Ha beillesztett egy diagramlapot, láthatja a Diagramlap objektumot a VB szerkesztőben.

Az eseménykódot hozzáadhatja a diagramlap kódablakához, ahogy a munkalapon tettük.

Kattintson duplán a Diagramlap objektumra a Project Explorerben. Ez megnyitja a diagramlap kódablakát.

Most ki kell választania a Diagramot a kódablak bal felső sarkában található legördülő menüből.

Miután kiválasztotta a Diagramot a legördülő menüből, megjelenik a Diagram lappal kapcsolatos összes esemény listája. A használni kívánt elemet a kódablak jobb felső sarkában található legördülő menüből választhatja ki.

Megjegyzés: Amint kiválasztja a Diagramot a legördülő menüből, két kódsor jelenik meg a kódablakban. Miután kiválasztotta az eseményt, amelyhez kódot szeretne, törölheti az alapértelmezés szerint megjelenő sorokat.

Osztály modulban

Az osztálymodulokat ugyanúgy be kell illeszteni, mint a UserForms -ot.

Egy osztálymodul tárolhatja az alkalmazáshoz kapcsolódó kódot - ami maga az Excel lenne, és a beágyazott diagramokat.

A következő hetekben külön oktatóanyagként lefedem az osztálymodult.

Ne feledje, hogy az OnTime és OnKey eseményeken kívül a fenti események egyike sem tárolható a normál VBA modulban.

Az eseménysorozat megértése

Amikor elindít egy eseményt, az nem elszigetelten történik. Ezenkívül több triggerek sorozatához is vezethet.

Például, amikor új munkalapot helyez be, a következő dolgok történnek:

1. Új munkalap kerül hozzáadásra
2. Az előző munkalap deaktiválódik
3. Az új munkalap aktiválódik

Bár a legtöbb esetben nem kell aggódnia a sorrend miatt, ha összetett, eseményekre támaszkodó kódokat hoz létre, akkor jobb, ha ismeri a sorrendet, hogy elkerülje a váratlan eredményeket.

Az érvek szerepének megértése a VBA eseményekben

Mielőtt rátérnénk az események példáira és a fantasztikus dolgokra, amelyeket ezzel megtehetsz, van egy fontos fogalom, amelyet le kell fednem.

A VBA eseményekben kétféle kód létezik:

• Minden érv nélkül
• Érvekkel

És ebben a részben szeretnék gyorsan kitérni az érvek szerepére.

Az alábbiakban egy kód található, amelyben nincs argumentum (a zárójelek üresek):

Private Sub Workbook_Open () MsgBox "Ne felejtse el kitölteni a munkalapot" End Sub

A fenti kóddal a munkafüzet megnyitásakor egyszerűen megjelenik egy üzenetdoboz az „ - Ne felejtse el kitölteni a munkalapot” üzenettel.

Most nézzünk meg egy kódot, amelynek van érve.

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

A fenti kód az Sh argumentumot használja, amely objektumtípusként van definiálva. Az Sh argumentum lehet egy munkalap vagy egy diagramlap, mivel a fenti esemény akkor indul el, amikor új lapot adnak hozzá.

Azáltal, hogy a munkafüzethez hozzáadott új lapot hozzárendeli az Sh objektumváltozóhoz, a VBA lehetővé tette számunkra, hogy ezt használjuk a kódban. Tehát az új lap nevére hivatkozva használhatom az Sh.Name -et.

Az érvek koncepciója hasznos lesz, ha áttekinti a VBA események példáit a következő szakaszokban.

Munkafüzet szintű események (példákkal magyarázva)

Az alábbiakban a munkafüzet leggyakrabban használt eseményeit mutatjuk be.

ESEMÉNY NEVE	MILYEN HALLGATÓ AZ ESEMÉNY
Aktiválja	Amikor a munkafüzet aktiválva van
AfterSave	Ha a munkafüzet bővítményként van telepítve
BeforeSave	A munkafüzet mentésekor
ElőtteZár	Amikor a munkafüzet zárva van
BeforePrint	Munkafüzet nyomtatásakor
Kapcsolja ki	Amikor a munkafüzet deaktiválva van
NewSheet	Amikor új lap kerül hozzáadásra
Nyisd ki	Amikor megnyílik a munkafüzet
SheetActivate	Amikor a munkafüzet bármelyik lapja aktiválva van
SheetBeforeDelete	Amikor bármelyik lap törlődik
SheetBeforeDoubleClick	Amikor bármelyik lapra duplán kattint
SheetBeforeRightClick	Amikor bármelyik lapon jobb egérgombbal kattint
SheetCalculate	Amikor bármelyik lapot kiszámítják vagy újraszámítják
SheetDeactivate	Amikor a munkafüzet deaktiválva van
SheetPivotTableUpdate	A munkafüzet frissítésekor
SheetSelectionChange	Amikor a munkafüzet megváltozik
WindowActivate	Amikor a munkafüzet aktiválva van
WindowDeactivate	Amikor a munkafüzet deaktiválva van

Vegye figyelembe, hogy ez nem teljes lista. A teljes listát itt találja.

Ne feledje, hogy a Munkafüzet esemény kódja a ThisWorkbook objektumok kódablakában tárolódik.

Most nézzünk meg néhány hasznos munkafüzet-eseményt, és nézzük meg, hogyan lehet ezeket használni a mindennapi munkában.

Munkafüzet nyitott esemény

Tegyük fel, hogy barátságos emlékeztetőt szeretne megjeleníteni a felhasználónak, hogy töltse ki az időbeosztását, amikor kinyit egy adott munkafüzetet.

Ehhez használhatja az alábbi kódot:

Private Sub Workbook_Open () MsgBox "Ne felejtse el kitölteni a munkalapot" End Sub

Most, amint megnyitja ezt a kódot tartalmazó munkafüzetet, megjelenik egy üzenetdoboz a megadott üzenettel.

A kóddal (vagy általában a munkafüzet -eseménykódokkal) való munkavégzés során néhány dolgot tudnia kell:

• Ha egy munkafüzet makróval rendelkezik, és el szeretné menteni, akkor .XLSM formátumban kell mentenie. Ellenkező esetben a makrókód elvész.
• A fenti példában az eseménykód csak akkor lesz végrehajtva, ha a makrók engedélyezve vannak. Láthat egy sárga sávot, amely engedélyt kér a makrók engedélyezéséhez. Amíg ez nincs engedélyezve, az eseménykód nem hajtódik végre.
• A munkafüzet eseménykódja a ThisWorkbook objektum kódablakába kerül.

Ezt a kódot tovább finomíthatja, és csak pénteken jelenítheti meg az üzenetet.

Az alábbi kód ezt tenné:

Privát almunkafüzet_Open () wkday = Hétköznap (Dátum) Ha wkday = 6 Akkor MsgBox "Ne felejtse el kitölteni a munkalapot" End Sub

Ne feledje, hogy a Hétköznap funkcióban a vasárnap az 1 -es értéket, a hétfő a 2 -et és így tovább.

Ezért pénteken 6 -ot használtam.

A Workbook Open esemény sok esetben hasznos lehet, például:

• Ha üdvözlő üzenetet szeretne megjeleníteni a személynek a munkafüzet megnyitásakor.
• Ha emlékeztetőt szeretne megjeleníteni a munkafüzet megnyitásakor.
• Ha mindig egy adott munkalapot szeretne aktiválni a munkafüzetben, amikor megnyílik.
• Ha a kapcsolódó fájlokat a munkafüzettel együtt szeretné megnyitni.
• Ha a munkafüzet minden megnyitásakor rögzíteni szeretné a dátumot és az időbélyeget.

Munkafüzet NewSheet esemény

A NewSheet esemény akkor aktiválódik, amikor új lapot helyez be a munkafüzetbe.

Tegyük fel, hogy be szeretné írni a dátumot és az időt az újonnan beszúrt lap A1 cellájába. Ehhez használhatja az alábbi kódot:

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

A fenti kód az „On Error Resume Next” (Hiba folytatása a továbbiakban) parancsot használja az olyan esetek kezelésére, amikor valaki egy diagramlapot szúr be, és nem egy munkalapot. Mivel a diagramlap nem rendelkezik A1 cellával, hibát jelenítene meg, ha az „On Error Resume Next” opciót nem használja.

Egy másik példa lehet az, amikor néhány alapvető beállítást vagy formázást szeretne alkalmazni egy új lapra, amint azt hozzáadta. Például, ha új lapot szeretne hozzáadni, és azt szeretné, hogy automatikusan kapjon egy sorszámot (legfeljebb 100), akkor használhatja az alábbi kódot.

Private Sub Workbook_NewSheet (ByVal Sh as Object) On Error Folytassa a következővel 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

A fenti kód formázást is végez. Kék színt ad a fejléccellának, és fehérré teszi a betűtípust. Az összes kitöltött cellára szegélyt is alkalmaz.

A fenti kód egy példa arra, hogyan segíthet egy rövid VBA -kód néhány másodperc ellopásában, amikor új munkalapot helyez be (ha ezt minden alkalommal meg kell tennie).

Munkafüzet az esemény mentése előtt

Mielőtt a Mentés esemény elindul a munkafüzet mentésekor. Ne feledje, hogy az esemény először aktiválódik, majd a munkafüzet mentésre kerül.

Excel -munkafüzet mentésekor két lehetséges forgatókönyv lehetséges:

1. Először menti, és megjelenik a Mentés másként párbeszédpanel.
2. Korábban már elmentette, és egyszerűen elmenti és felülírja a módosításokat a már mentett verzióban.

Most nézzünk néhány példát, ahol használhatja az BeforeSave eseményt.

Tegyük fel, hogy van egy új munkafüzete, amelyet először ment, és emlékeztetni szeretné a felhasználót, hogy mentse el a K meghajtóba, és használhatja az alábbi kódot:

Privát almunkafüzet_BeforeSave (ByVal SaveAsUI Booleanként, Mégsem Booleanként) If SaveAsUI then MsgBox "Save this file in the K Drive" End Sub

A fenti kódban, ha a fájlt soha nem mentették, a SaveAsUI igaz, és megjelenik a Mentés másként párbeszédpanel. A fenti kód megjeleníti az üzenetet, mielőtt megjelenik a Mentés másként párbeszédpanel.

Egy másik példa lehet a dátum és az idő frissítése, amikor a fájlt egy adott cellába menti.

Az alábbi kód a dátumot és az időbélyeget az 1. lap A1 cellájába illeszti be, amikor a fájlt elmenti.

Privát almunkafüzet_BeforeSave (ByVal SaveAsUI mint Boolean, Cancel as Boolean) Munkalapok ("Sheet1"). Tartomány ("A1") = Formátum (Most, "dd-mmm-yyyy hh: mm: ss") Sub

Ne feledje, hogy ez a kód akkor kerül végrehajtásra, amikor a felhasználó elmenti a munkafüzetet. Ha a munkafüzetet először menti, akkor megjelenik a Mentés másként párbeszédpanel. De a kód már végrehajtásra került, amikor megjelenik a Mentés másként párbeszédpanel. Ha ezen a ponton úgy dönt, hogy törli, és nem menti el a munkafüzetet, akkor a dátum és az idő már be lesz írva a cellába.

Munkafüzet az esemény bezárása előtt

A Bezárás előtt esemény közvetlenül a munkafüzet bezárása előtt történik.

Az alábbi kód védi az összes munkalapot a munkafüzet bezárása előtt.

Privát almunkafüzet_BeforeClose (Mégse logikai értékként) Dim sh munkalapként minden egyes munkához ebben a munkafüzetben. Munkalapok sh.

Ne feledje, hogy az eseménykód a munkafüzet bezárásakor aktiválódik.

Az egyik fontos tudnivaló erről az eseményről az, hogy nem mindegy, hogy a munkafüzet valóban zárva van -e vagy sem.

Abban az esetben, ha a munkafüzetet nem mentette el, és megjelenik a prompt, amely megkérdezi, hogy mentse -e a munkafüzetet vagy sem, és kattintson a Mégse gombra, akkor nem menti a munkafüzetet.Az eseménykód azonban addigra már végrehajtásra került.

Esemény nyomtatása előtti munkafüzet

Amikor megadja a nyomtatási parancsot (vagy Nyomtatási előnézet parancsot), a Nyomtatás előtt esemény aktiválódik.

Az alábbi kód újraszámítja az összes munkalapot a munkafüzet kinyomtatása előtt.

Privát almunkafüzet_BeforePrint (Mégse logikai értékként) Minden ws -hez a munkalapok ws -jében. Számítsa ki a következő ws végét

Amikor a felhasználó kinyomtatja a munkafüzetet, az esemény aktiválódik, függetlenül attól, hogy a teljes munkafüzetet nyomtatja, vagy csak annak egy részét.

Egy másik példa az alábbi kódra vonatkozik, amely hozzáadja a dátumot és az időt a lábléchez a munkafüzet nyomtatásakor.

Privát almunkafüzet_BeforePrint (Mégse logikai értékként) Dim ws munkalapként minden ws-re ebben a munkafüzetben.Worksheets ws.PageSetup.LeftFooter = "Printed On-" & Format (Now, "dd-mmm-yyyy hh: mm") Next ws End Sub

Munkalap szintű események (példákkal magyarázva)

A munkalap eseményei a munkalap triggerei alapján zajlanak.

Az alábbiakban a munkalap leggyakrabban használt eseményei láthatók.

Esemény neve	Mi váltja ki az eseményt
Aktiválja	Amikor a munkalap aktiválva van
BeforeDelete	A munkalap törlése előtt
BeforeDoubleClick	Mielőtt duplán kattintana a munkalapra
BeforeRightClick	A munkalap jobb egérgombbal történő kattintása előtt
Kiszámítja	A munkalap kiszámítása vagy újraszámítása előtt
változás	Amikor a munkalap cellái megváltoznak
Kapcsolja ki	Amikor a munkalap ki van kapcsolva
PivotTableUpdate	Amikor a munkalap pivot táblája frissül
SelectionChange	Amikor a munkalapon a kijelölés megváltozik

Vegye figyelembe, hogy ez nem teljes lista. A teljes listát itt találja.

Ne feledje, hogy a Munkalap esemény kódja a munkalap objektumkód ablakában van tárolva (abban az ablakban, amelyben az eseményt aktiválni szeretné). Egy munkafüzetben több munkalap is lehet, és a kód csak akkor aktiválódik, ha az esemény a munkalapon történik, amelybe be van helyezve.

Most nézzünk meg néhány hasznos feladatlap eseményt, és nézzük meg, hogyan lehet ezeket használni a mindennapi munkában.

Munkalap Esemény aktiválása

Ez az esemény aktiválódik, amikor aktiválja a munkalapot.

Az alábbi kód feloldja a lap védelmét, amint aktiválja.

Privát almunkalap_Activate () ActiveSheet. Védje fel a vége aloldalt

Ezt az eseményt arra is használhatja, hogy megbizonyosodjon arról, hogy egy adott cella vagy cellatartomány (vagy elnevezett tartomány) van kiválasztva, amint aktiválja a munkalapot. Az alábbi kód a D1 cellát választja, amint aktiválja a lapot.

Privát almunkalap_Aktiválás () ActiveSheet.Range ("D1"). Válassza ki az Alvége vége lehetőséget

Munkalap módosítási esemény

A módosítási esemény minden alkalommal aktiválódik, amikor módosítást végez a munkalapon.

Hát… nem mindig.

Vannak változások, amelyek kiváltják az eseményt, és vannak, amelyek nem. Íme néhány olyan módosítás, amely nem indítja el az eseményt:

• Amikor megváltoztatja a cella formátumát (betűméret, szín, szegély stb.).
• Amikor egyesíti a cellákat. Ez meglepő, mivel néha a cellák egyesítése eltávolítja a tartalmat az összes cellából, kivéve a bal felsőt.
• Cella megjegyzés hozzáadása, törlése vagy szerkesztése során.
• Amikor cellatartományt rendez.
• Amikor a Célkeresőt használja.

A következő módosítások kiváltják az eseményt (bár azt gondolhatja, hogy nem kellene):

• A formázás másolása és beillesztése kiváltja az eseményt.
• A formázás törlése kiváltaná az eseményt.
• A helyesírás -ellenőrzés futtatása kiváltaná az eseményt.

Az alábbiakban egy kód jelenít meg egy üzenetdobozt a módosított cella címével.

Privát almunkalap_csere (ByVal Target tartományként) MsgBox "Most módosított" és Target.Add End Sub

Bár ez egy haszontalan makró, megmutatja, hogyan használhatja a Cél argumentumot annak megállapítására, hogy mely cellák módosultak.

Most nézzünk meg néhány hasznos példát.

Tegyük fel, hogy van egy cellája (mondjuk A1: D10), és meg szeretne jeleníteni egy promptot, és megkérdezi a felhasználót, hogy valóban meg akart -e változtatni egy cellát ezen a tartományon, vagy sem, használhatja az alábbi kódot.

Képernyőt jelenít meg két gombbal - Igen és Nem. Ha a felhasználó az „Igen” lehetőséget választja, akkor a módosítás megtörtént, ellenkező esetben megfordul.

Privát almunkalap_változás (ByVal cél, mint tartomány) Ha Target.Row <= 10 And Target.Column <= 4 Aztán Ans = MsgBox ("Módosítod a cellákat az A1: D10 -ben. Biztos, hogy akarod?", vbYesNo) Vége, ha Ha Ans = vbNo Akkor Application.EnableEvents = Hamis alkalmazás. Az alkalmazás visszavonása. EnableEvents = Igaz vége, ha vége al

A fenti kódban ellenőrizzük, hogy a Cél cella az első 4 oszlopban és az első 10 sorban van -e. Ebben az esetben megjelenik az üzenetdoboz. Továbbá, ha a felhasználó a Nem lehetőséget választotta az üzenetmezőben, akkor a változtatás megfordul (az Application.Undo paranccsal).

Vegye figyelembe, hogy az Application.EnableEvents = hamisat használtam az Application.Undo sor előtt. Aztán megfordítottam az Application.EnableEvent = True használatával a következő sorban.

Erre szükség van, mivel amikor a visszavonás megtörténik, a változási eseményt is kiváltja. Ha nem állítom hamisra az EnableEvent értéket, akkor továbbra is kiváltja a változási eseményt.

A módosított esemény használatával figyelemmel kísérheti a megnevezett tartomány változásait is. Például, ha van egy „DataRange” nevű tartománya, és szeretne megjeleníteni egy értesítést arra az esetre, ha a felhasználó módosít ezen a megnevezett tartományon, akkor használja az alábbi kódot:

Privát almunkalap_változás (ByVal cél, mint tartomány) Dim DRange mint tartomány beállítása DRange = Range ("DataRange") Ha nem metszi (Target, DRange) nem semmi, akkor MsgBox "Most módosított az adattartományon" End If End Sub

A fenti kód ellenőrzi, hogy a cella/tartomány, ahol a módosításokat végrehajtotta, tartalmaz -e közös cellákat az adattartományban. Ha igen, akkor megjelenik az üzenetdoboz.

Munkafüzet -kiválasztási esemény módosítása

A kiválasztás módosítása esemény akkor aktiválódik, amikor a munkalapon változás történik.

Az alábbi kód újraszámítja a lapot, amint megváltoztatja a kijelölést.

Privát almunkalap_SelectionChange (ByVal Target as Range) Alkalmazás.

Egy másik példa erre az eseményre, amikor a kijelölt cella aktív sorát és oszlopát szeretné kiemelni.

Valami az alábbiak szerint:

A következő kód képes erre:

Private Sub Worksheet_SelectionChange (ByVal Target as Range) cellák.Interior.ColorIndex = xlNone ActiveCell .EntireRow.Interior.Color = RGB (248, 203, 173) .EntireColumn.Interior.Color = RGB (180, 198, 231) Vége End Sub

A kód először eltávolítja a háttérszínt az összes cellából, majd a kódban említett színt alkalmazza az aktív sorra és oszlopra.

És ez a probléma ezzel a kóddal. Hogy eltávolítja a színt minden sejtből.

Ha ki szeretné emelni az aktív sort/oszlopot, miközben a többi cellában lévő színt nem éri el, használja az oktatóanyagban bemutatott technikát.

Munkafüzet DoubleClick esemény

Ez az egyik kedvenc munkalap eseményem, és sok oktatóanyagot fog látni, ahol ezt használtam (például ezt vagy ezt).

Ez az esemény akkor aktiválódik, ha duplán kattint egy cellára.

Hadd mutassam meg, milyen fantasztikus ez.

Az alábbi kóddal duplán kattinthat egy cellára, és a háttér színét alkalmazza, megváltoztatja a betűtípust, és félkövérré teszi a cella szövegét;

Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel as Boolean) Cancel = True With Target .Interior.Color = vbKék.

Ez akkor lehet hasznos, ha a cellák listáját nézi, és néhány kiválasztottat szeretne kiemelni. Bár az F4 gombbal megismételheti az utolsó lépést, az csak egyféle formázást alkalmazhat. Ezzel a duplakattintásos eseménnyel mindhármat csak dupla kattintással alkalmazhatja.

Ne feledje, hogy a fenti kódban a Cancel = True értéket adtam meg.

Ez úgy történik, hogy a dupla kattintás alapértelmezett művelete le van tiltva - vagyis a szerkesztési módba való belépés. Mégse = Igaz, az Excel nem jutna szerkesztési módba, ha duplán kattint a cellára.

Itt egy másik példa.

Ha Excelben van teendőlistája, akkor a dupla kattintással esemény segítségével alkalmazhatja az áthúzott formátumot, hogy befejezettként jelölje meg a feladatot.

Valami az alábbiak szerint:

Itt van a kód, ami ezt teszi:

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

Ne feledje, hogy ebben a kódban dupla kattintást tettem váltóeseményként. Ha duplán kattint egy cellára, az ellenőrzi, hogy az áthúzott formátum már alkalmazásra került-e. Ha igen, a dupla kattintás eltávolítja az áthúzott formátumot, ha pedig nem, akkor az áthúzási formátumot alkalmazza.

Excel VBA OnTime esemény

A cikkben eddig látott események az Excel egyik objektumához kapcsolódtak, legyen az munkafüzet, munkalap, diagramlap vagy UserForms stb.

Az OnTime esemény különbözik a többi eseménytől, mivel a normál VBA modulban tárolható (míg a többit az objektumok, például a ThisWorkbook vagy a Munkalapok vagy a Felhasználói űrlapok kódablakában kell elhelyezni).

A normál VBA modulon belül az alkalmazásobjektum módszereként használják.

Ez azért tekinthető eseménynek, mert az Ön által megadott idő alapján aktiválható. Például, ha azt akarom, hogy a lap 5 percenként újraszámoljon, használhatom az OnTime eseményt.

Vagy ha üzenetet/emlékeztetőt akarok megjeleníteni a nap egy adott időpontjában, használhatom az OnTime eseményt.

Az alábbiakban egy kód látható, amely minden nap 14 órakor üzenetet jelenít meg.

Sub MessageTime () Application.OnTime TimeValue ("14:00:00"), "ShowMessage" End Sub Sub ShowMessage () MsgBox "Itt az ebéd ideje" End Sub

Ne feledje, hogy ezt a kódot be kell helyeznie a szokásos VBA modulba,

Továbbá, bár az OnTime eseményt a megadott időpontban aktiválják, a makrót bármikor manuálisan kell futtatnia. A makró futtatása után várni fog délután 2 óráig, majd meghívja a „ShowMessage” makrót.

A ShowMessage makró ekkor megjeleníti az üzenetet.

Az OnTime esemény négy érvet tartalmaz:

Application.OnTime (Legkorábbi időpont, Eljárás, LatestTime, Menetrend)

• Legkorábbi időpont: Az idő, amikor futtatni szeretné az eljárást.
• Eljárás: A végrehajtandó eljárás neve.
• LatestTime (nem kötelező): Ha egy másik kód fut, és a megadott kód nem futtatható a megadott időben, akkor megadhatja a LatestTime -t, amelyre várnia kell. Például ez lehet az EarliestTime + 45 (ami azt jelenti, hogy 45 másodpercet vár, amíg a másik eljárás befejeződik). Ha az eljárás még 45 másodperc elteltével sem tud futni, akkor megszakad. Ha ezt nem adja meg, az Excel megvárja, amíg a kód futtatható, majd futtatja.
• Ütemezés (nem kötelező): Ha True értékre van állítva, akkor új időbeosztást ütemez. Ha hamis, akkor törli az előzőleg beállított eljárást. Alapértelmezés szerint ez igaz.

A fenti példában csak az első két érvet használtuk.

Nézzünk egy másik példát.

Az alábbi kód 5 percenként frissíti a munkalapot.

Dim NextRefresh as Date Sub RefreshSheet () ThisWorkbook.Worksheets ("Sheet1"). Calculate NextRefresh = Now + TimeValue ("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh () Hiba Folytatás Következő Application.OnTime NextRefresh, "RefreshSheet",, False End Sub

A fenti kód 5 percenként frissíti a munkalapot.

A Most funkcióval határozza meg az aktuális időt, majd 5 percet ad hozzá az aktuális időhöz.

Az OnTime esemény addig fut, amíg le nem állítja. Ha bezárja a munkafüzetet, és az Excel alkalmazás még mindig fut (a többi munkafüzet nyitva van), akkor az a munkafüzet, amelyben az OnTime esemény fut, újra megnyílik.

Ezt jobban lehet kezelni, ha kifejezetten leállítja az OnTime eseményt.

A fenti kódban megvan a StopRefresh kód, de végre kell hajtania az OnTime esemény leállításához. Ezt manuálisan is elvégezheti, hozzárendelheti egy gombhoz, és ezt a gomb megnyomásával vagy a Munkafüzet bezárása eseményből hívhatja.

Privát almunkafüzet_BeforeClose (Mégse logikai értékként) Hívás StopRefresh End Sub

A fenti „BeforeClose” eseménykód a ThisWorkbook kódablakába kerül.

Excel VBA OnKey esemény

Amikor Excel -sel dolgozik, folyamatosan figyeli az Ön által használt billentyűleütéseket. Ez lehetővé teszi számunkra, hogy billentyűleütéseket használjunk esemény kiváltó okává.

Az OnKey esemény segítségével megadhatja a billentyűleütést (vagy a billentyűkombinációk kombinációját) és a kódot, amelyet a billentyűleütés használatakor végre kell hajtani. Amikor megnyomja ezeket a billentyűleütéseket, végrehajtja a kódot.

Csakúgy, mint az OnTime esemény, meg kell adni az OnKey esemény törlésének módját. Továbbá, ha az OnKey eseményt egy adott billentyűleütésre állítja be, az az összes nyitott munkafüzetben elérhetővé válik.

Mielőtt mutatnék egy példát az OnKey esemény használatára, hadd osszam meg először a VBA -ban elérhető kulcskódokat.

KULCS	KÓD
Backspace	{BACKSPACE} vagy {BS}
Szünet	{SZÜNET}
Caps Lock	{CAPSLOCK}
Töröl	{DELETE} vagy {DEL}
Lefele nyíl	{LE}
Vége	{END}
Belép	~
Enter (a nueric billentyűzeten)	{BELÉP}
Menekülni	{ESCAPE} vagy {ESC}
itthon	{ITTHON}
Ins	{INSERT}
Bal nyíl	{BAL}
Szám zár	{SZÁM ZÁR}
Lapozás lefelé	{PGDN}
Oldal fel	{PGUP}
Jobb nyíl	{JOBB}
Scroll Lock	{SCROLLOCK}
Tab	{TAB}
Felfelé nyíl	{FEL}
F1 -F15	{F1} - {F15}

Ha bármilyen onkey eseményt kell használnia, akkor a kódot kell használnia.

A fenti táblázat az egyes billentyűleütések kódjait tartalmazza.

Ezeket a következő kódokkal is kombinálhatja:

• Váltás: + (Plusz jel)
• Ellenőrzés: ^ (Hiányjel)
• Alt: % (Százalék)

Például az Alt F4 esetén a következő kódot kell használnia: "%{F4}” - ahol a % az ALT és az {F4} az F4 billentyű.

Most nézzünk egy példát (ne feledje, hogy az OnKey események kódja a szokásos VBA modulban található).

Amikor megnyomja a PageUp vagy a PageDown gombot, 29 sorral ugrik az aktív cella fölé/alá (legalábbis ezt teszi a laptopomon).

Ha azt szeretné, hogy egyszerre csak 5 sort ugorjon, használja az alábbi kódot:

Sub PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub PageUpMod () On Hiba Folytatás Következő ActiveCell.Offset (-5, 0). Sub Sub PageDownMod () On Error Folytassa a következő ActiveCell -t. Offset (5, 0). Aktiválja a Befejező aloldalt

Amikor futtatja a kód első részét, az az OnKey eseményeket futtatja. Ha ez megtörtént, a PageUp és a PageDown billentyű használatával a kurzor csak 5 sort ugrik egyszerre.

Ne feledje, hogy az „On Error Resume Next” (On Error Resume Next) lehetőséget használtuk a hibák figyelmen kívül hagyásának biztosítására. Ezek a hibák akkor fordulhatnak elő, ha megnyomja a PageUp gombot, még akkor is, ha a munkalap tetején van. Mivel nincs több ugrandó sor, a kód hibát mutat. De mivel az „On Error Resume Next” funkciót használtuk, figyelmen kívül hagyjuk.

Annak érdekében, hogy ezek az OnKey események elérhetők legyenek, futtassa a kód első részét. Ha azt szeretné, hogy a munkafüzet megnyitása után azonnal elérhető legyen, akkor ezt elhelyezheti a ThisWorkbook kódablakban.

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

Az alábbi kód visszaállítja a kulcsok normál működését.

Sub Cancel_PageUpDownKeysMod () Application.OnKey "{PgUp}" Application.OnKey "{PgDn}" Befejező al

Ha nem adja meg a második argumentumot az OnKey metódusban, akkor a billentyűleütés visszatér a szokásos funkcióhoz.

Ha törölni szeretné a billentyűleütés funkcióit, és az Excel nem tesz semmit a billentyűleütés használatakor, akkor üres karakterláncot kell használnia második argumentumként.

Az alábbi kódban az Excel semmit sem tenne, ha a PageUp vagy PageDown billentyűket használjuk.

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

Események letiltása a VBA -ban

Néha előfordulhat, hogy le kell tiltania az eseményeket, hogy a kód megfelelően működjön.

Tegyük fel például, hogy van egy tartományom (A1: D10), és szeretnék üzenetet megjeleníteni, amikor egy cella megváltozik ebben a tartományban. Megmutatok egy üzenetdobozt, és megkérdezem a felhasználót, hogy biztos -e abban, hogy meg akarja változtatni. Ha a válasz igen, a változtatás megtörténik, és ha a válasz nem, akkor a VBA visszavonja.

Használhatja az alábbi kódot:

Privát almunkalap_változás (ByVal cél, mint tartomány) Ha Target.Row <= 10 And Target.Column <= 4 Aztán Ans = MsgBox ("Módosítod a cellákat az A1: D10 -ben. Biztos, hogy akarod?", vbYesNo) End If If Ans = vbNo then Application. Undo End If End Sub

A probléma ezzel a kóddal az, hogy amikor a felhasználó a Nem lehetőséget választja az üzenetmezőben, a művelet megfordul (ahogy az Application.Undo -t használtam).

Amikor a visszavonás megtörténik, és az értéket visszaállítják az eredeti értékre, a VBA változási esemény ismét aktiválódik, és a felhasználó ismét ugyanazt az üzenetmezőt jeleníti meg.

Ez azt jelenti, hogy továbbra is a NEM gombra kattinthat az üzenetmezőn, és továbbra is megjelenik. Ez akkor történik, amikor ebben az esetben beragadt a végtelen hurokba.

Az ilyen esetek elkerülése érdekében le kell tiltania az eseményeket, hogy a változási esemény (vagy bármely más esemény) ne induljon el.

Ebben az esetben a következő kód jól működik:

Privát almunkalap_változás (ByVal cél, mint tartomány) Ha Target.Row <= 10 And Target.Column <= 4 then Ans = MsgBox ("Módosítod a cellákat az A1: D10 -ben. Biztos vagy benne, hogy akarod?", vbYesNo) Vége, ha Ha Ans = vbNo Akkor Application.EnableEvents = Hamis alkalmazás. Az alkalmazás visszavonása. EnableEvents = Igaz vége, ha vége al

A fenti kódban, közvetlenül az Application.Undo sor felett használtuk - Application.EnableEvents = False.

Ha az EnableEvents értéket hamisra állítja, az nem vált ki eseményt (az aktuális vagy a nyitott munkafüzetekben).

Miután befejeztük a visszavonási műveletet, visszakapcsolhatjuk az EnableEvents tulajdonságot True értékre.

Ne feledje, hogy az események letiltása hatással van az összes munkafüzetre, amely jelenleg van megnyitva (vagy amikor az EnableEvents értéke False). Például, ha a kód részeként új munkafüzetet nyit meg, akkor a Munkafüzet megnyitása esemény nem fog működni.

Az események hatása A verem visszavonása

Először hadd mondjam el, mi az Undo Stack.

Amikor Excelben dolgozik, folyamatosan figyelemmel kíséri a műveleteket. Ha hibázik, a Control + Z billentyűkombinációval bármikor visszatérhet az előző lépéshez (azaz visszavonhatja az aktuális műveletet).

Ha kétszer megnyomja a Control + Z billentyűt, két lépést visz vissza. Ezeket a lépéseket a visszavonási verem részeként tárolja.

Bármely esemény, amely megváltoztatja a munkalapot, megsemmisíti ezt a visszavonási köteget.Ez azt jelenti, hogy ha 5 dolgot elvégeztem az esemény kiváltása előtt, akkor nem fogom tudni használni a Control + Z billentyűt az előző lépésekhez való visszatéréshez. Az esemény kiváltása számomra elpusztította ezt a veremt.

Az alábbi kódban a VBA -t használom az időbélyegző beviteléhez az A1 cellába, amikor változás történik a munkalapon.

Privát almunkalap_változtatás (ByVal cél tartományként) Application.EnableEvents = Hamis tartomány ("A1"). Érték = Formátum (Most, "dd-mmm-yyyy hh: mm: ss") Application.EnableEvents = True End Sub

Mivel módosítok a munkalapon, ez tönkreteszi a visszavonási köteget.

Azt is vegye figyelembe, hogy ez nem korlátozódik csak az eseményekre.

Ha rendelkezik olyan kóddal, amelyet a szokásos VBA modulban tárol, és módosít a munkalapon, az az Excel visszavonási veremét is megsemmisíti.

Például az alábbi kód egyszerűen írja be a „Hello” szöveget az A1 cellába, de még ennek futtatása is megsemmisítené a visszavonási köteget.

Sub TypeHello () tartomány ("A1"). Érték = "Hello" End Sub

A következő Excel VBA oktatóanyagok is tetszhetnek:

• Munka cellákkal és tartományokkal az Excel VBA -ban.
• Munkalapok használata Excel VBA -ban.
• Munkafüzetek használata Excel VBA -ban.
• Excel VBA hurkok - A végső útmutató.
• Ha IF akkor más kimutatás használata Excel VBA -ban.
• A következő ciklushoz az Excelben.
• Felhasználó által definiált függvények létrehozása az Excel VBA-ban.
• Bővítmények létrehozása és használata Excelben.
• Makrókat hozhat létre és használhat újra a Személyes makró munkafüzetbe mentéssel.