Excel VBA događaji - jednostavan (i potpun) vodič

Kada stvarate ili snimate makronaredbu u Excelu, morate pokrenuti makro kako biste izvršili korake u kodu.

Nekoliko načina pokretanja makronaredbe uključuje korištenje dijaloškog okvira makronaredbe, dodjeljivanje makronaredbe gumbu, korištenje prečaca itd.

Osim ovih izvršenja makronaredbi koje pokreću korisnici, za pokretanje makronaredbe možete koristiti i VBA događaje.

Excel VBA događaji - uvod

Dopustite mi prvo da objasnim što je događaj u VBA -i.

Događaj je radnja koja može pokrenuti izvršavanje navedene makronaredbe.

Na primjer, kada otvorite novu radnu knjigu, to je događaj. Kada umetnete novi radni list, to je događaj. Kada dvaput kliknete na ćeliju, to je događaj.

U VBA -i ima mnogo takvih događaja, a za te događaje možete stvoriti kodove. To znači da bi se, čim se događaj dogodi, i ako ste naveli kôd za taj događaj, taj kôd odmah izvršio.

Excel to automatski čini čim primijeti da se dogodio događaj. Dakle, trebate samo napisati kôd i postaviti ga u ispravnu potprogram događaja (to će biti obrađeno kasnije u ovom članku).

Na primjer, ako umetnete novi radni list i želite da ima prefiks za godinu, možete napisati kôd za njega.

Sada, kad god netko umetne novi radni list, ovaj bi se kôd automatski izvršio i dodao prefiks godine u naziv radnog lista.

Drugi primjer može biti da želite promijeniti boju ćelije kada netko dvaput klikne na nju. Za to možete upotrijebiti događaj dvostrukog klika.

Slično, možete stvoriti VBA kodove za mnoge takve događaje (kao što ćemo vidjeti kasnije u ovom članku).

Ispod je kratka slika koja prikazuje događaj dvostrukog klika na djelu. Čim dvaput kliknem na ćeliju A1. Excel odmah otvara okvir s porukom koji prikazuje adresu ćelije.

Dvostruki klik je događaj, a prikaz okvira s porukom je ono što sam naveo u kodu kad god se dogodi događaj dvostrukog klika.

Iako je gornji primjer beskoristan događaj, nadam se da će vam pomoći da shvatite što su zapravo događaji.

Različite vrste Excel VBA događaja

U Excelu postoje različiti objekti - poput samog Excela (na koji se često nazivamo aplikacija), radnih knjiga, radnih listova, grafikona itd.

Svaki od ovih objekata može imati različite događaje povezane s njim. Na primjer:

• Ako izradite novu radnu knjigu, to je događaj na razini aplikacije.
• Ako dodate novi radni list, to je događaj na razini radne knjige.
• Ako promijenite vrijednost u ćeliji na listu, to je događaj na razini radnog lista.

Dolje su navedene različite vrste događaja koji postoje u Excelu:

1. Događaji na razini radnog lista: To su vrste događaja koji bi se pokrenuli na temelju radnji poduzetih na radnom listu. Primjeri ovih događaja uključuju promjenu ćelije na radnom listu, promjenu odabira, dvostruki klik na ćeliju, desni klik na ćeliju itd.
2. Događaji na razini radne knjige: Ti bi se događaji pokrenuli na temelju radnji na razini radne knjige. Primjeri ovih događaja uključuju dodavanje novog radnog lista, spremanje radne knjige, otvaranje radne knjige, ispis dijela ili cijele radne knjige itd.
3. Događaji na razini aplikacije: To su događaji koji se događaju u Excel aplikaciji. Primjer ovih bi uključivao zatvaranje bilo koje otvorene radne knjige ili otvaranje nove radne knjige.
4. Događaji na razini korisničkog obrasca: Ti bi se događaji pokrenuli na temelju radnji u "Korisničkom obrascu". Primjeri za to uključuju inicijalizaciju korisničkog obrasca ili klik na gumb u korisničkom obrascu.
5. Događaji na grafikonu: To su događaji povezani s listom grafikona. List grafikona razlikuje se od radnog lista (tu se većina nas koristi za rad u Excelu). Svrha listova grafikona je držanje grafikona. Primjeri takvih događaja uključuju promjenu niza grafikona ili promjenu veličine grafikona.
6. OnTime i OnKey događaji: Ovo su dva događaja koja se ne uklapaju ni u jednu od gore navedenih kategorija. Zato sam ih posebno naveo. Događaj "OnTime" omogućuje vam izvršavanje koda u određeno vrijeme ili nakon isteka određenog vremena. Događaj ‘OnKey’ omogućuje vam izvršavanje koda kada se koristi određeni pritisak tipke (ili kombinacija tipki).

Gdje staviti kôd povezan s događajem

U gornjem odjeljku obrađivao sam različite vrste događaja.

Ovisno o vrsti događaja, kôd morate staviti u odgovarajući objekt.

Na primjer, ako se radi o događaju povezanom s radnim listom, trebao bi ući u prozor koda objekta radnog lista. Ako je povezana s radnom knjigom, trebala bi ući u prozor koda za objekt radne knjige.

U VBA -i različiti objekti - poput radnih listova, radnih knjiga, tablica, korisničkih obrazaca itd. Imaju vlastite prozore koda. Morate unijeti kôd događaja u prozor koda relevantnog objekta. Na primjer - ako se radi o događaju na razini radne knjige, tada morate imati kôd događaja u prozoru koda radne knjige.

Sljedeći odjeljci pokrivaju mjesta na koja možete postaviti kôd događaja:

U prozoru koda radnog lista

Kada otvorite VB Editor (pomoću tipkovnih prečaca ALT + F11), primijetili biste objekt radnih listova u Project Exploreru. Za svaki radni list u radnoj knjizi vidjet ćete jedan objekt.

Kada dvaput kliknete objekt radnog lista u koji želite postaviti kôd, otvorit će se prozor koda za taj radni list.

Iako možete početi pisati kôd od početka, puno je bolje odabrati događaj s popisa opcija i dopustiti VBA -u da automatski umetne odgovarajući kôd za odabrani događaj.

Da biste to učinili, najprije morate odabrati radni list s padajućeg izbornika u gornjem lijevom kutu prozora koda.

Nakon odabira radnog lista s padajućeg izbornika, dobit ćete popis svih događaja povezanih s radnim listom. Možete odabrati onu koju želite koristiti s padajućeg izbornika u gornjem desnom kutu prozora koda.

Čim odaberete događaj, automatski će unijeti prvi i zadnji redak koda za odabrani događaj. Sada možete dodati svoj kod između dva retka.

Napomena: Čim odaberete Radni list s padajućeg izbornika, primijetit ćete da se u prozoru koda pojavljuju dva retka koda. Nakon što odaberete događaj za koji želite kôd, možete izbrisati retke koji su se pojavljivali prema zadanim postavkama.

Imajte na umu da svaki radni list ima svoj vlastiti prozor koda. Kada stavite kôd za Sheet1, on će funkcionirati samo ako se događaj dogodi u Sheet1.

U prozoru CodeWorkWorkbook

Baš kao i radni listovi, ako imate kôd događaja na razini radne knjige, možete ga postaviti u prozor koda ThisWorkbook.

Kada dvaput kliknete na ThisWorkbook, otvorit će se prozor koda za njega.

Morate odabrati Radnu knjigu s padajućeg izbornika u gornjem lijevom kutu prozora koda.

Nakon što odaberete Radnu knjigu s padajućeg izbornika, dobit ćete popis svih događaja koji se odnose na Radnu knjigu. Možete odabrati onu koju želite koristiti s padajućeg izbornika u gornjem desnom kutu prozora koda.

Čim odaberete događaj, automatski će unijeti prvi i zadnji redak koda za odabrani događaj. Sada možete dodati svoj kod između dva retka.

Napomena: Čim s padajućeg izbornika odaberete Radna knjiga, primijetit ćete da se u prozoru koda pojavljuju dva retka koda. Nakon što odaberete događaj za koji želite kôd, možete izbrisati retke koji su se pojavljivali prema zadanim postavkama.

U prozoru koda korisničkog obrasca

Kada stvarate UserForms u Excelu, možete koristiti i UserForm događaje za izvršavanje kodova na temelju određenih radnji. Na primjer, možete odrediti kôd koji se izvršava kada se pritisne gumb.

Dok su objekti Sheet i ThisWorkbook objekti već dostupni kada otvorite VB Editor, UserForm je nešto što morate prvo stvoriti.

Da biste stvorili korisnički obrazac, desnom tipkom miša kliknite bilo koji objekt, idite na Umetni i kliknite na korisnički obrazac.

To će umetnuti objekt UserForm u radnu knjigu.

Kada dvaput pritisnete UserForm (ili bilo koji objekt koji dodate u UserForm), otvorit će se kôd za UserForm.

Sada, poput radnih listova ili ThisWorkbook -a, možete odabrati događaj i on će umetnuti prvi i posljednji redak za taj događaj. A zatim možete dodati kôd usred njega.

U prozoru koda grafikona

U Excelu možete umetnuti i listove grafikona (koji se razlikuju od radnih listova). List grafikona treba sadržavati samo grafikone.

Kad umetnete list grafikona, moći ćete vidjeti objekt lista grafikona u VB Editoru.

Kôd događaja možete dodati u prozor koda lista grafikona, baš kao što smo to učinili na radnom listu.

Dvaput kliknite na objekt lista grafikona u Project Exploreru. Ovo će otvoriti prozor koda za list grafikona.

Sada morate odabrati grafikon s padajućeg izbornika u gornjem lijevom kutu prozora koda.

Nakon što odaberete grafikon s padajućeg izbornika, dobit ćete popis svih događaja povezanih s listom grafikona. Možete odabrati onu koju želite koristiti s padajućeg izbornika u gornjem desnom kutu prozora koda.

Napomena: Čim odaberete grafikon s padajućeg izbornika, primijetit ćete da se u prozoru koda pojavljuju dva retka koda. Nakon što odaberete događaj za koji želite kôd, možete izbrisati retke koji su se pojavljivali prema zadanim postavkama.

U modulu razreda

Moduli klase moraju biti umetnuti baš kao i korisnički obrasci.

Modul klase može sadržavati kôd koji se odnosi na aplikaciju - a to bi bio sam Excel i ugrađeni grafikoni.

Sljedećih ću tjedana obraditi nastavni modul kao zaseban vodič.

Imajte na umu da osim OnTime i OnKey događaja, niti jedan od gore navedenih događaja ne može biti pohranjen u obični VBA modul.

Razumijevanje niza događaja

Kad pokrenete događaj, to se ne događa izolirano. Također može dovesti do niza više okidača.

Na primjer, kada umetnete novi radni list, događaju se sljedeće stvari:

1. Dodaje se novi radni list
2. Prethodni radni list se deaktivira
3. Novi radni list se aktivira

Iako u većini slučajeva možda nećete morati brinuti o slijedu, ako stvarate složene kodove koji se oslanjaju na događaje, bolje je znati slijed kako biste izbjegli neočekivane rezultate.

Razumijevanje uloge argumenata u VBA događajima

Prije nego što prijeđemo na primjere događaja i sjajne stvari koje možete učiniti s njim, postoji jedan važan koncept koji moram pokriti.

U VBA događajima postojale bi dvije vrste kodova:

• Bez ikakvih argumenata
• S argumentima

I u ovom odjeljku želim brzo pokriti ulogu argumenata.

Ispod je kôd koji nema argument (zagrade su prazne):

Private Sub Workbook_Open () MsgBox "Ne zaboravite ispuniti vremenski list" End Sub

S gornjim kodom, kada otvorite radnu knjigu, ona jednostavno prikazuje okvir s porukom s porukom - "Ne zaboravite popuniti vremensku tablicu".

Pogledajmo sada kod koji ima argument.

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

Gornji kôd koristi Sh argument koji je definiran kao tip objekta. Argument Sh može biti radni list ili list grafikona jer se gornji događaj pokreće kad se doda novi list.

Dodjeljujući novi list koji se dodaje radnoj knjizi varijabli objekta Sh, VBA nam je omogućio da ga koristimo u kodu. Dakle, za upućivanje na novi naziv lista, mogu koristiti Sh.Name.

Koncept argumenata bit će koristan kada prođete kroz primjere VBA događaja u sljedećim odjeljcima.

Događaji na razini radne knjige (objašnjeno s primjerima)

Slijede najčešće korišteni događaji u radnoj knjizi.

IME DOGAĐAJA	ŠTO TRIGIRA DOGAĐAJ
Aktivirati	Kad je radna knjiga aktivirana
Nakon spremanja	Kad je radna knjiga instalirana kao dodatak
Prije Spremi	Kad se spremi radna knjiga
PrijeZatvorite	Kad je radna knjiga zatvorena
Prije ispisa	Kad se ispiše radna knjiga
Deaktivirajte	Kad je radna knjiga deaktivirana
Novi list	Kad se doda novi list
Otvoren	Kad se otvori radna knjiga
SheetActivate	Kad se aktivira bilo koji list u radnoj knjizi
SheetBeforeDelete	Kad se bilo koji list izbriše
SheetBeforeDoubleClick	Kada dvaput pritisnete bilo koji list
SheetBeforeRightClick	Kad se desni klik na bilo koji list
ListIzračunaj	Kad se bilo koji list izračuna ili ponovno izračuna
SheetDeactivate	Kad je radna knjiga deaktivirana
SheetPivotTableUpdate	Kad se radna knjiga ažurira
SheetSelectionChange	Kad se promijeni radna bilježnica
WindowActivate	Kad je radna knjiga aktivirana
WindowDeactivate	Kad je radna knjiga deaktivirana

Imajte na umu da ovo nije potpuni popis. Kompletan popis možete pronaći ovdje.

Upamtite da je kôd za radnu knjigu pohranjen u prozoru koda objekata ThisWorkbook.

Pogledajmo sada neke korisne događaje iz radne knjige i vidimo kako se oni mogu koristiti u vašem svakodnevnom poslu.

Otvoreni događaj radne knjige

Recimo da želite korisniku pokazati prijateljski podsjetnik da ispuni njihove rasporede vremena kad god otvore određenu radnu knjigu.

Za to možete upotrijebiti donji kod:

Private Sub Workbook_Open () MsgBox "Ne zaboravite ispuniti vremenski list" End Sub

Čim otvorite radnu knjigu s ovim kodom, prikazat će vam se okvir s naznačenom porukom.

Prilikom rada s ovim kodom (ili kodovima događaja u radnoj knjizi općenito) morate znati nekoliko stvari:

• Ako radna knjiga ima makronaredbu i želite je spremiti, morate je spremiti u .XLSM formatu. Inače bi se makro kôd izgubio.
• U gornjem primjeru kôd događaja izvršit će se samo kad su omogućene makronaredbe. Možda ćete vidjeti žutu traku koja traži dopuštenje za omogućavanje makronaredbi. Dok se to ne omogući, kôd događaja se ne izvršava.
• Kôd događaja radne knjige smješten je u kôdni prozor objekta ThisWorkbook.

Možete dodatno poboljšati ovaj kôd i prikazati poruku samo u petak.

Kôd u nastavku bi ovo učinio:

Privatna pod -radna knjiga_Open () wkday = Radni dan (datum) Ako je wkday = 6 Zatim MsgBox "Ne zaboravite ispuniti vremenski list" Kraj pod

Imajte na umu da je u funkciji Radni dan nedjelji dodijeljena vrijednost 1, ponedjeljku 2 i tako dalje.

Stoga sam za petak iskoristio 6.

Događaj Otvorena radna knjiga može biti koristan u mnogim situacijama, kao što su:

• Kada želite prikazati poruku dobrodošlice osobi kada se otvori radna knjiga.
• Kada želite prikazati podsjetnik kada se radna knjiga otvori.
• Kada želite uvijek aktivirati jedan određeni radni list u radnoj knjizi kada se otvori.
• Kad želite zajedno s radnom knjigom otvoriti povezane datoteke.
• Kada želite zabilježiti datum i vrijeme pri svakom otvaranju radne knjige.

Događaj NewSheet radne sveske

Događaj NewSheet pokreće se kada umetnete novi list u radnu knjigu.

Recimo da želite unijeti datum i vrijeme u ćeliju A1 novo umetnutog lista. Za to možete upotrijebiti donji kod:

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

Gornji kôd koristi "On Error Resume Next" za rješavanje slučajeva gdje netko umetne list grafikona, a ne radni list. Budući da list grafikona nema ćeliju A1, prikazala bi se pogreška ako se ne koristi ‘On Error Resume Next’.

Drugi primjer može biti kada želite primijeniti neke osnovne postavke ili oblikovanje na novi list čim se doda. Na primjer, ako želite dodati novi list i želite da automatski dobije serijski broj (do 100), tada možete upotrijebiti donji kod.

Private Sub Workbook_NewSheet (ByVal Sh As Object) On Error Sljedeći nastavak sa Sh.Range ("A1") .Value = "S. No." .Interijer.Boja = vbBlue. Font.Boja = vbBijeli završetak sa Za i = 1 do 100 Sh.Range ("A1"). Odstupanje (i, 0) .Vrijednost = i Slijedeći i Sh.Range ("A1", Raspon ("A1"). Kraj (xlDown)). Borders.LineStyle = xlContinuous End Sub

Gornji kod također pomalo formatira. On ćeliji zaglavlja daje plavu boju, a font čini bijelim. Također primjenjuje obrub na sve ispunjene ćelije.

Gornji kôd je primjer kako vam kratki VBA kôd može pomoći da ukradete nekoliko sekundi svaki put kada umetnete novi radni list (u slučaju da je to nešto što morate učiniti svaki put).

Radna knjiga Prije događaja SaveSave

Prije spremanja događaja spremanja prilikom spremanja radne knjige. Imajte na umu da se događaj prvo pokreće, a zatim sprema radna knjiga.

Prilikom spremanja radne knjige programa Excel moguća su dva scenarija:

1. Spremate ga prvi put i prikazat će se dijaloški okvir Spremi kao.
2. Već ste ga spremili i jednostavno će spremiti i prebrisati promjene u već spremljenoj verziji.

Pogledajmo sada nekoliko primjera gdje možete koristiti događaj BeforeSave.

Pretpostavimo da imate novu radnu knjigu koju spremate po prvi put i želite podsjetiti korisnika da je spremi u pogon K, tada možete upotrijebiti donji kod:

Private Sub Workbook_BeforeSave (ByVal SaveAsUI kao Boolean, Odustani kao Boolean) Ako SaveAsUI Zatim MsgBox "Spremi ovu datoteku na K pogonu" End Sub

U gornjem kodu, ako datoteka nikada nije spremljena, SaveAsUI je True i prikazuje dijaloški okvir Spremi kao. Gornji kôd prikazao bi poruku prije nego što se pojavi dijaloški okvir Spremi kao.

Drugi primjer može biti ažuriranje datuma i vremena kada se datoteka sprema u određenu ćeliju.

Kôd u nastavku umetnuo bi datum i vrijeme u ćeliju A1 lista 1 kad god se datoteka spremi.

Private Sub Workbook_BeforeSave (ByVal SaveAsUI Kao Boolean, Otkaži kao Boolean) Radni listovi ("Sheet1"). Raspon ("A1") = Format (Sada, "dd-mmm-gggg hh: mm: ss")

Imajte na umu da se ovaj kôd izvršava čim korisnik spremi radnu knjigu. Ako se radna knjiga sprema prvi put, prikazat će se dijaloški okvir Spremi kao. No kôd je već izvršen do trenutka kada vidite dijaloški okvir Spremi kao. U ovom trenutku, ako odlučite otkazati i ne spremiti radnu knjigu, datum i vrijeme već će biti unijeti u ćeliju.

Radna bilježnica Prije zatvaranja događaja

Prije nego što se događaj Zatvori dogodi neposredno prije zatvaranja radne knjige.

Kôd u nastavku štiti sve radne listove prije zatvaranja radne knjige.

Private Sub Workbook_BeforeClose (Cancel As Boolean) Dim sh As Radni list Za svako sh u ovoj radnoj knjizi. Radni listovi sh.Zaštita sljedećeg sh End Sub

Upamtite da se kôd događaja aktivira čim zatvorite radnu knjigu.

Jedna važna stvar koju treba znati o ovom događaju je da ga nije briga je li radna knjiga doista zatvorena ili nije.

U slučaju da radna knjiga nije spremljena i prikazuje vam se upit s pitanjem želite li spremiti radnu knjigu ili ne, a zatim pritisnete Odustani, ona neće spremiti vašu radnu knjigu.Međutim, kôd događaja do tada bi već bio izveden.

Radna bilježnica Prije događaja ispisa

Kada date naredbu ispisa (ili naredbu Pregled ispisa), aktivira se događaj Prije ispisa.

Kôd u nastavku preračunao bi sve radne listove prije ispisa vaše radne knjige.

Private Sub Workbook_BeforePrint (Cancel As Boolean) Za svaki ws u radnim listovima ws.Izračunaj sljedeći ws End Sub

Kada korisnik ispisuje radnu knjigu, događaj će se pokrenuti bez obzira na to ispisuje li cijelu radnu knjigu ili samo dio.

Drugi primjer u nastavku je kôd koji bi dodao podatak i vrijeme u podnožje prilikom ispisa radne knjige.

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

Događaji na razini radnog lista (objašnjeno s primjerima)

Događaji na radnom listu odvijaju se na temelju okidača na radnom listu.

Slijede najčešće korišteni događaji na radnom listu.

Naziv događaja	Što pokreće događaj
Aktivirati	Kad je radni list aktiviran
Prije brisanja	Prije brisanja radnog lista
PrijeDoubleClick	Prije nego što radni list dvaput kliknete
Prije Desnog klika	Prije nego što radni list kliknete desnom tipkom miša
Izračunati	Prije nego se radni list izračuna ili ponovno izračuna
Promijeniti	Kad se promijene ćelije na radnom listu
Deaktivirajte	Kad je radni list deaktiviran
Ažuriranje zaokretne tablice	Kada se zaokretna tablica na radnom listu ažurira
SelectionChange	Kad se promijeni odabir na radnom listu

Imajte na umu da ovo nije potpuni popis. Kompletan popis možete pronaći ovdje.

Upamtite da je kôd za radni list pohranjen u prozoru koda objekta radnog lista (u onom u kojem želite da se događaj pokrene). U radnoj knjizi može biti više radnih listova, a vaš bi se kôd aktivirao samo kada se događaj dogodi na radnom listu u koji je postavljen.

Pogledajmo sada neke korisne događaje na radnom listu i vidimo kako se oni mogu koristiti u vašem svakodnevnom poslu.

Radni list Aktiviraj događaj

Ovaj se događaj pokreće kada aktivirate radni list.

Kod u nastavku uklanja zaštitu s lista čim se aktivira.

Privatni pod radni list_Activate () ActiveSheet.Unprotect End Sub

Ovaj događaj možete koristiti i za provjeru da li je odabrana određena ćelija ili raspon ćelija (ili imenovani raspon) čim aktivirate radni list. Kod u nastavku odabrao bi ćeliju D1 čim aktivirate list.

Privatni pomoćni radni list_Activate () ActiveSheet.Range ("D1"). Odaberite End Sub

Događaj promjene radnog lista

Događaj promjene pokreće se kad god promijenite radni list.

Pa … ne uvijek.

Postoje neke promjene koje pokreću događaj, a neke ne. Evo popisa nekih promjena koje neće pokrenuti događaj:

• Kada promijenite oblikovanje ćelije (veličinu fonta, boju, obrub itd.).
• Kad spojite ćelije. To je iznenađujuće jer ponekad spajanjem ćelija također uklanjate sadržaj iz svih ćelija osim gornje lijeve.
• Kada dodajete, brišete ili uređujete komentar ćelije.
• Kada sortirate raspon ćelija.
• Kada koristite Traženje cilja.

Sljedeće promjene pokrenule bi događaj (iako mislite da ne bi trebao biti):

• Formatiranje kopiranja i lijepljenja pokrenulo bi događaj.
• Brisanje oblikovanja pokrenulo bi događaj.
• Pokretanje provjere pravopisa pokrenulo bi događaj.

Ispod je kôd koji bi prikazao okvir za poruku s adresom ćelije koja je promijenjena.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "Upravo ste promijenili" & Target.Address End Sub

Iako je ovo beskorisna makronaredba, prikazuje vam kako pomoću argumenta Target saznati koje su ćelije promijenjene.

Pogledajmo sada još nekoliko korisnih primjera.

Pretpostavimo da imate niz ćelija (recimo A1: D10) i želite prikazati upit i upitati korisnika želi li zaista promijeniti ćeliju u ovom rasponu, možete upotrijebiti donji kod.

Prikazuje upit s dva gumba - Da i Ne. Ako korisnik odabere "Da", promjena je učinjena, u suprotnom je obrnuta.

Privatni podradni list_Promjena (ByVal cilj kao raspon) Ako je Target.Row <= 10 i Target.Column <= 4 Zatim Ans = MsgBox ("Unosite promjene u ćelije u A1: D10. Jeste li sigurni da to želite?", vbYesNo) End If If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

U gornjem kodu provjeravamo je li ciljna ćelija u prva 4 stupca i prvih 10 redaka. U tom slučaju prikazuje se okvir s porukom. Također, ako je korisnik u okviru za poruku odabrao Ne, promjena se poništava (naredbom Application.Undo).

Imajte na umu da sam koristio Application.EnableEvents = False prije retka Application.Undo. A onda sam to promijenio koristeći Application.EnableEvent = True u sljedećem retku.

To je potrebno jer kada se poništi, također pokreće događaj promjene. Ako EnableEvent ne postavim na False, nastavit će s pokretanjem događaja promjene.

Također možete pratiti promjene imenovanog raspona pomoću događaja promjene. Na primjer, ako imate imenovani raspon pod nazivom "DataRange" i želite prikazati upit u slučaju da korisnik izvrši promjenu u tom imenovanom rasponu, možete upotrijebiti donji kod:

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 "Upravo ste napravili promjenu u rasponu podataka" End If End Sub

Gornji kôd provjerava ima li ćelija/raspon u kojem ste izvršili izmjene neke ćelije zajedničke rasponu podataka. U tom slučaju prikazuje okvir s porukom.

Odabir radne knjige Promijeni događaj

Događaj promjene odabira pokreće se kad god dođe do promjene odabira na radnom listu.

Kôd u nastavku preračunao bi list čim promijenite odabir.

Privatna podradna tablica_SelectionChange (ByVal Target As Range) Aplikacija. Izračunaj krajnju pod

Drugi primjer ovog događaja je kada želite istaknuti aktivni redak i stupac odabrane ćelije.

Nešto kao što je prikazano ispod:

Sljedeći kod može to učiniti:

Privatni pod -radni list_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) Završi s Kraj podm

Kôd prvo uklanja boju pozadine iz svih ćelija, a zatim primjenjuje onu koja se spominje u kodu na aktivni redak i stupac.

I to je problem s ovim kodom. Da uklanja boju sa svih stanica.

Ako želite istaknuti aktivni redak/stupac, a da boja u drugim ćelijama ostane netaknuta, upotrijebite tehniku ​​prikazanu u ovom vodiču.

Događaj DoubleClick radne knjige

Ovo je jedan od mojih omiljenih događaja na radnom listu i vidjet ćete mnogo tutoriala u kojima sam ovo koristio (poput ovog ili ovog).

Ovaj se događaj aktivira kada dvaput kliknete na ćeliju.

Dopustite mi da vam pokažem kako je ovo sjajno.

Pomoću donjeg koda možete dvaput kliknuti na ćeliju i ona će primijeniti boju pozadine, promijeniti boju fonta i učiniti tekst u ćeliji podebljanim;

Privatni pod -radni list_BeforeDoubleClick (ByVal cilj kao raspon, otkaži kao logičko) Odustani = Tačno s ciljem. Unutrašnja.boja = vbBlue .Font.Color = vbWhite .Font.Bold = True End s End Sub

To može biti korisno kada prolazite kroz popis ćelija i želite istaknuti nekoliko odabranih. Iako možete koristiti tipku F4 za ponavljanje posljednjeg koraka, ona će moći primijeniti samo jednu vrstu oblikovanja. S ovim događajem dvostrukog klika možete primijeniti sva tri samo dvostrukim klikom.

Imajte na umu da sam u gornjem kodu napravio vrijednost Cancel = True.

To je učinjeno tako da je zadana radnja dvostrukog klika onemogućena - a to je prelazak u način uređivanja. Ako je Cancel = True, Excel vas neće dovesti u način uređivanja kada dvaput pritisnete ćeliju.

Evo još jednog primjera.

Ako imate popis obaveza u Excelu, možete upotrijebiti događaj dvostrukog klika da biste primijenili precrtani format za označavanje zadatka kao dovršenog.

Nešto kao što je prikazano ispod:

Evo koda koji će to učiniti:

Privatni pod -radni list_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Cancel = True CurrFormat = Target.Font.Strikethrough If CurrFormat Zatim Target.Font.Strikethrough = Netačno Ostalo Target.Font.Strikethrough = True End Ako završi Sub

Imajte na umu da sam u ovom kodu dvaput kliknuo kao događaj prebacivanja. Kada dvaput kliknete na ćeliju, ona provjerava je li precrtani format već primijenjen. Ako jeste, dvostrukim klikom uklanja se precrtani format, a ako nije, tada se primjenjuje precrtani format.

Excel VBA OnTime događaj

Događaji koje smo do sada vidjeli u ovom članku povezani su s jednim od Excel objekata, bilo da se radi o radnoj knjizi, radnom listu, grafikonu ili korisničkim obrascima itd.

Događaj OnTime razlikuje se od ostalih događaja jer se može pohraniti u uobičajeni VBA modul (dok su ostali trebali biti postavljeni u kôdni prozor objekata kao što su ThisWorkbook ili Worksheets ili UserForms).

Unutar običnog VBA modula koristi se kao metoda aplikacijskog objekta.

Razlog zašto se ovo smatra događajem je taj što se može pokrenuti na temelju vremena koje navedete. Na primjer, ako želim da se list preračunava svakih 5 minuta, za to mogu koristiti događaj OnTime.

Ili, ako želim prikazati poruku/podsjetnik u određeno doba dana, mogu koristiti događaj OnTime.

Ispod je kôd koji će svaki dan prikazivati ​​poruku u 14 sati.

Sub MessageTime () Application.OnTime TimeValue ("14:00:00"), "ShowMessage" Završi sub Sub ShowMessage () MsgBox "Vrijeme je za ručak" End Sub

Upamtite da morate postaviti ovaj kôd u običan VBA modul,

Također, iako bi se događaj OnTime pokrenuo u određeno vrijeme, morate makro pokrenuti ručno u bilo kojem trenutku. Nakon što pokrenete makro, on će pričekati do 14 sati, a zatim pozvati makronaredbu 'ShowMessage'.

Makro ShowMessage tada bi prikazao poruku.

Događaj OnTime ima četiri argumenta:

Application.OnTime (Najranije vrijeme, Postupak, Najnovije vrijeme, Raspored)

• Najranije vrijeme: Vrijeme kada želite pokrenuti postupak.
• Postupak: Naziv postupka koji treba pokrenuti.
• Najnovije vrijeme (izborno): U slučaju da je pokrenut drugi kôd, a navedeni kôd se ne može pokrenuti u određeno vrijeme, možete navesti Najnovije vrijeme za koje bi trebao čekati. Na primjer, to može biti EarliestTime + 45 (što znači da će čekati 45 sekundi da se drugi postupak dovrši). Ako se ni nakon 45 sekundi postupak ne može pokrenuti, napušta se. Ako to ne navedete, Excel bi pričekao dok se kôd ne može pokrenuti, a zatim ga pokrenuo.
• Raspored (izborno): Ako je postavljeno na True, zakazuje novi vremenski postupak. Ako je False, tada se poništava prethodno postavljeni postupak. Prema zadanim postavkama, ovo je Tačno.

U gornjem primjeru koristili smo samo prva dva argumenta.

Pogledajmo još jedan primjer.

Kod u nastavku osvježavat će radni list svakih 5 minuta.

Zatamni NextRefresh kao datum 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

Gornji kôd bi osvježavao radni list svakih 5 minuta.

Koristi funkciju Now za određivanje trenutnog vremena, a zatim dodaje 5 minuta trenutnom vremenu.

Događaj OnTime nastavio bi se izvoditi sve dok ga ne zaustavite. Ako zatvorite radnu knjigu i Excel aplikacija još uvijek radi (druge radne knjige su otvorene), radna knjiga u kojoj je pokrenut događaj OnTime ponovno bi se otvorila.

To se bolje rješava izravnim zaustavljanjem događaja OnTime.

U gornjem kodu imam kod StopRefresh, ali morate ga izvršiti da biste zaustavili događaj OnTime. To možete učiniti ručno, dodijeliti ga gumbu i učiniti to pritiskom na gumb ili ga pozvati iz događaja Zatvaranje radne knjige.

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

Gornji kôd događaja "BeforeClose" ide u prozor koda ThisWorkbook.

Excel VBA OnKey događaj

Kada radite s Excelom, on neprestano prati pritiske tipki koje koristite. To nam omogućuje da pritisnemo tipke kao okidač za događaj.

S događajem OnKey možete odrediti pritisak tipke (ili kombinaciju tipki) i kôd koji bi trebao biti izveden kada se taj pritisak tipke koristi. Kada se pritisnu ti pritisci tipke, izvršit će kôd za nju.

Baš kao i događaj OnTime, morate imati način da otkažete događaj OnKey. Također, kada događaj OnKey postavite za određeni pritisak na tipku, on postaje dostupan u svim otvorenim radnim knjigama.

Prije nego vam pokažem primjer korištenja događaja OnKey, dopustite mi prvo da podijelim ključne kodove koji su vam dostupni u VBA -i.

KLJUČ	KODIRATI
Backspace	{BACKSPACE} ili {BS}
Pauza	{PAUZA}
Caps Lock	{CAPSLOCK}
Izbrisati	{DELETE} ili {DEL}
Strelica dolje	{DOLJE}
Kraj	{KRAJ}
Unesi	~
Enter (na numeričkoj tipkovnici)	{UNESI}
Pobjeći	{ESCAPE} ili {ESC}
Dom	{DOM}
Ins	{UMETNUTI}
Lijeva strelica	{LIJEVO}
NumLock	{NUMLOCK}
Stranica niže	{PGDN}
PageUp	{PGUP}
Desna strijela	{PRAVO}
Scroll Lock	{SCROLLOCK}
Tab	{TAB}
Strelica prema gore	{GORE}
F1 do F15	{F1} do {F15}

Kad trebate upotrijebiti bilo koji događaj s ključem, morate upotrijebiti kôd za njega.

Gornja tablica sadrži kodove za pojedinačne pritiske tipki.

Također ih možete kombinirati sa sljedećim kodovima:

• Pomak: + (Znak plus)
• Kontrolirati: ^ (Znak za umetanje)
• Alt: % (Postotak)

Na primjer, za Alt F4 morate koristiti kôd: "%{F4}” - gdje je % za tipku ALT, a {F4} za tipku F4.

Pogledajmo sada primjer (sjetite se da se kod za OnKey događaje nalazi u redovnom VBA modulu).

Kad pritisnete tipku PageUp ili PageDown, ona preskače 29 redaka iznad/ispod aktivne ćelije (barem to radi na mom prijenosnom računalu).

Ako želite da preskače samo 5 redaka odjednom, možete upotrijebiti donji kod:

Podstranica PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" Završi podstranu Sub PageUpMod () Uključeno Pogreška Nastavi sljedeći ActiveCell.Offset (-5, 0). Aktivirajte kraj Sub Sub PageDownMod () On Error Resume Next ActiveCell.Offset (5, 0) .Aktivirajte End Sub

Kada pokrenete prvi dio koda, on će pokrenuti OnKey događaje. Kad se to izvrši, upotrebom PageUp -a i PageDown tipke pokazivač će skočiti samo na 5 redaka odjednom.

Imajte na umu da smo upotrijebili 'On Error Resume Next' kako bismo bili sigurni da se greške zanemaruju. Do ovih pogrešaka može doći ako pritisnete tipku PageUp, čak i kad ste na vrhu radnog lista. Budući da nema više redova za preskakanje, kôd bi pokazao pogrešku. No budući da smo upotrijebili 'On Error Resume Next', to će se zanemariti.

Da biste bili sigurni da su ti događaji OnKey dostupni, morate pokrenuti prvi dio koda. U slučaju da želite da ovo bude dostupno čim otvorite radnu knjigu, možete je postaviti u prozor koda ThisWorkbook.

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

Kod u nastavku vraća ključeve na njihovu normalnu funkcionalnost.

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

Ako ne navedete drugi argument u metodi OnKey, pritiskom na tipku vratit će se njegova uobičajena funkcionalnost.

U slučaju da želite otkazati funkcionalnost pritiska na tipku, tako da Excel ne učini ništa kada se koristi taj pritisak tipke, morate koristiti prazan niz kao drugi argument.

U donjem kodu Excel ne bi učinio ništa kada koristimo tipke PageUp ili PageDown.

Pod Ignore_PageUpDownKeys () Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" Završi pod

Onemogućavanje događaja u VBA -i

Ponekad ćete morati onemogućiti događaje kako bi vaš kôd ispravno funkcionirao.

Na primjer, pretpostavimo da imam raspon (A1: D10) i želim prikazati poruku kad god se ćelija promijeni u ovom rasponu. Pokažem okvir s porukom i pitam korisnika je li siguran da želi promijeniti. Ako je odgovor da, promjena se vrši, a ako je odgovor ne, VBA bi to poništio.

Možete koristiti donji kod:

Privatni podradni list_Promjena (ByVal cilj kao raspon) Ako je Target.Row <= 10 i Target.Column <= 4 Zatim Ans = MsgBox ("Unosite promjene u ćelije u A1: D10. Jeste li sigurni da to želite?", vbYesNo) End If If Ans = vbNo Then Application.Undo End If End Sub

Problem s ovim kodom je taj što kada korisnik odabere Ne u okviru za poruku, radnja se obrće (kao što sam koristio Application.Undo).

Kad se poništavanje dogodi i vrijednost se vrati na izvornu, ponovno se pokreće VBA događaj promjene, a korisniku se ponovno prikazuje isti okvir s porukom.

To znači da možete nastaviti pritiskati NE u okviru za poruku i on će se i dalje pojavljivati. To se događa kada ste u ovom slučaju zaglavili u beskonačnoj petlji.

Da biste izbjegli takve slučajeve, morate onemogućiti događaje kako se događaj promjene (ili bilo koji drugi događaj) ne bi pokrenuo.

Sljedeći kod bi dobro funkcionirao u ovom slučaju:

Privatni pod -radni list_Promjena (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Zatim Ans = MsgBox ("Unosite promjene u ćelije u A1: D10. Jeste li sigurni da to želite?", vbYesNo) End If If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

U gornjem kodu, točno iznad retka Application.Undo, koristili smo - Application.EnableEvents = False.

Postavljanje EnableEvents na False ne bi pokrenulo nijedan događaj (u trenutnoj ili bilo kojoj otvorenoj radnoj knjizi).

Nakon što dovršimo operaciju poništavanja, svojstvo EnableEvents možemo vratiti na True.

Imajte na umu da onemogućavanje događaja utječe na sve radne knjige koje su trenutno otvorene (ili otvorene dok je EnableEvents postavljeno na Netačno). Na primjer, kao dio koda, ako otvorite novu radnu knjigu, događaj Otvorena radna knjiga neće funkcionirati.

Utjecaj događaja Poništi hrpu

Dopustite mi da vam prvo kažem što je Undo Stack.

Kada radite u Excelu, on neprestano nadzire vaše radnje. Kad pogriješite, uvijek možete koristiti tipke Control + Z za povratak na prethodni korak (tj. Poništiti trenutnu radnju).

Ako dvaput pritisnete Control + Z, vratit će vas se dva koraka unatrag. Ovi koraci koje ste izvršili pohranjeni su kao dio Poništi hrpu.

Svaki događaj koji promijeni radni list uništava ovaj Undo stog.To znači da ako sam učinio 5 stvari prije nego što sam pokrenuo događaj, neću moći koristiti Control + Z za povratak na prethodne korake. Pokretanje događaja uništilo mi je taj hrpu.

U donjem kodu koristim VBA za unos vremenske oznake u ćeliju A1 kad god dođe do promjene na radnom listu.

Privatni pod-radni list_Promijeni (ByVal cilj kao raspon) Application.EnableEvents = False Range ("A1"). Value = Format (Now, "dd-mmm-yyyy hh: mm: ss") Application.EnableEvents = True End Sub

Budući da unosim izmjene na radnom listu, to će uništiti poništavajući hrpu.

Također imajte na umu da ovo nije ograničeno samo na događaje.

Ako imate kôd koji je pohranjen u običnom VBA modulu, a vi promijenite radni list, to bi također uništilo stog za poništavanje u Excelu.

Na primjer, donji kôd jednostavno unesite tekst "Hello" u ćeliju A1, ali čak i pokretanje bi uništilo stog za poništavanje.

Podvrsta Podrška Zdravo () Raspon ("A1"). Vrijednost = "Pozdrav" Kraj podv

Možda će vam se svidjeti i sljedeći Excel VBA vodiči:

• Rad s ćelijama i rasponima u Excelu VBA.
• Rad s radnim listovima u Excelu VBA.
• Rad s radnim bilježnicama u Excelu VBA.
• Excel VBA petlje - Ultimativni vodič.
• Upotreba IF Zatim Else Statment u Excelu VBA.
• Za sljedeću petlju u Excelu.
• Stvaranje korisnički definiranih funkcija u Excelu VBA.
• Kako stvoriti i koristiti dodatke u Excelu.
• Stvorite i ponovno upotrijebite makronaredbe spremanjem u osobnu radnu knjigu makronaredbi.