Eventi VBA di Excel: una guida semplice (e completa)

Quando crei o registri una macro in Excel, devi eseguire la macro per eseguire i passaggi nel codice.

Alcuni modi per eseguire una macro includono l'utilizzo della finestra di dialogo della macro, l'assegnazione della macro a un pulsante, l'utilizzo di una scorciatoia, ecc.

Oltre a queste esecuzioni di macro avviate dall'utente, puoi anche utilizzare gli eventi VBA per eseguire la macro.

Eventi VBA di Excel - Introduzione

Lasciami prima spiegare cos'è un evento in VBA.

Un evento è un'azione che può attivare l'esecuzione della macro specificata.

Ad esempio, quando apri una nuova cartella di lavoro, è un evento. Quando inserisci un nuovo foglio di lavoro, è un evento. Quando fai doppio clic su una cella, è un evento.

Esistono molti di questi eventi in VBA ed è possibile creare codici per questi eventi. Ciò significa che non appena si verifica un evento e se hai specificato un codice per quell'evento, quel codice verrà eseguito immediatamente.

Excel lo fa automaticamente non appena si accorge che si è verificato un evento. Quindi devi solo scrivere il codice e inserirlo nella subroutine dell'evento corretta (questo è trattato più avanti in questo articolo).

Ad esempio, se si inserisce un nuovo foglio di lavoro e si desidera che abbia un prefisso dell'anno, è possibile scriverne il codice.

Ora, ogni volta che qualcuno inserisce un nuovo foglio di lavoro, questo codice verrà eseguito automaticamente e aggiungerà il prefisso dell'anno al nome del foglio di lavoro.

Un altro esempio potrebbe essere quello di voler cambiare il colore della cella quando qualcuno fa doppio clic su di essa. È possibile utilizzare l'evento doppio clic per questo.

Allo stesso modo, puoi creare codici VBA per molti di questi eventi (come vedremo più avanti in questo articolo).

Di seguito è riportato un breve elemento visivo che mostra l'evento del doppio clic in azione. Non appena faccio doppio clic sulla cella A1. Excel apre immediatamente una finestra di messaggio che mostra l'indirizzo della cella.

Il doppio clic è un evento e la visualizzazione della finestra di messaggio è ciò che ho specificato nel codice ogni volta che si verifica l'evento del doppio clic.

Sebbene l'esempio sopra sia un evento inutile, spero che ti aiuti a capire cosa sono realmente gli eventi.

Diversi tipi di eventi VBA di Excel

Esistono diversi oggetti in Excel, come Excel stesso (a cui spesso ci riferiamo come applicazione), cartelle di lavoro, fogli di lavoro, grafici, ecc.

A ciascuno di questi oggetti possono essere associati vari eventi. Per esempio:

• Se crei una nuova cartella di lavoro, si tratta di un evento a livello di applicazione.
• Se aggiungi un nuovo foglio di lavoro, si tratta di un evento a livello di cartella di lavoro.
• Se modifichi il valore in una cella di un foglio, si tratta di un evento a livello di foglio di lavoro.

Di seguito sono riportati i diversi tipi di eventi che esistono in Excel:

1. Eventi a livello di foglio di lavoro: Questi sono i tipi di eventi che si attiverebbero in base alle azioni intraprese nel foglio di lavoro. Esempi di questi eventi includono la modifica di una cella nel foglio di lavoro, la modifica della selezione, il doppio clic su una cella, il clic destro su una cella, ecc.
2. Eventi a livello di cartella di lavoro: Questi eventi verrebbero attivati ​​in base alle azioni a livello di cartella di lavoro. Esempi di questi eventi includono l'aggiunta di un nuovo foglio di lavoro, il salvataggio della cartella di lavoro, l'apertura della cartella di lavoro, la stampa di una parte o dell'intera cartella di lavoro, ecc.
3. Eventi a livello di applicazione: Questi sono gli eventi che si verificano nell'applicazione Excel. Un esempio di questi include la chiusura di una delle cartelle di lavoro aperte o l'apertura di una nuova cartella di lavoro.
4. Eventi a livello di modulo utente: Questi eventi verrebbero attivati ​​in base alle azioni nel "Form utente". Esempi di questi includono l'inizializzazione di un modulo utente o il clic su un pulsante nel modulo utente.
5. Eventi del grafico: Questi sono eventi relativi al foglio grafico. Un foglio grafico è diverso da un foglio di lavoro (che è dove la maggior parte di noi è abituata a lavorare in Excel). Uno scopo dei fogli grafici è di contenere un grafico. Esempi di tali eventi includono la modifica della serie del grafico o il ridimensionamento del grafico.
6. Eventi OnTime e OnKey: Questi sono due eventi che non rientrano in nessuna delle categorie precedenti. Quindi li ho elencati separatamente. L'evento "OnTime" consente di eseguire un codice in un momento specifico o dopo che è trascorso un tempo specifico. L'evento "OnKey" consente di eseguire un codice quando viene utilizzata una specifica sequenza di tasti (o una combinazione di tasti).

Dove inserire il codice relativo all'evento

Nella sezione precedente, ho trattato i diversi tipi di eventi.

In base al tipo di evento, è necessario inserire il codice nell'oggetto pertinente.

Ad esempio, se si tratta di un evento correlato al foglio di lavoro, dovrebbe andare nella finestra del codice dell'oggetto del foglio di lavoro. Se è correlato alla cartella di lavoro, dovrebbe andare nella finestra del codice per un oggetto cartella di lavoro.

In VBA, diversi oggetti, come fogli di lavoro, cartelle di lavoro, fogli grafici, moduli utente, ecc., Hanno le proprie finestre di codice. È necessario inserire il codice dell'evento nella finestra del codice dell'oggetto pertinente. Ad esempio, se si tratta di un evento a livello di cartella di lavoro, è necessario disporre del codice dell'evento nella finestra del codice cartella di lavoro.

Le sezioni seguenti riguardano i luoghi in cui è possibile inserire il codice dell'evento:

Nella finestra del codice del foglio di lavoro

Quando apri VB Editor (usando la scorciatoia da tastiera ALT + F11), noterai l'oggetto dei fogli di lavoro in Esplora progetti. Per ogni foglio di lavoro nella cartella di lavoro, vedrai un oggetto.

Quando si fa doppio clic sull'oggetto del foglio di lavoro in cui si desidera inserire il codice, si aprirà la finestra del codice per quel foglio di lavoro.

Sebbene tu possa iniziare a scrivere il codice da zero, è molto meglio selezionare l'evento da un elenco di opzioni e lasciare che VBA inserisca automaticamente il codice pertinente per l'evento selezionato.

Per fare ciò, devi prima selezionare il foglio di lavoro dal menu a discesa in alto a sinistra della finestra del codice.

Dopo aver selezionato il foglio di lavoro dal menu a discesa, viene visualizzato un elenco di tutti gli eventi relativi al foglio di lavoro. Puoi selezionare quello che desideri utilizzare dal menu a discesa in alto a destra nella finestra del codice.

Non appena si seleziona l'evento, inserirà automaticamente la prima e l'ultima riga del codice per l'evento selezionato. Ora puoi aggiungere il tuo codice tra le due righe.

Note: Non appena selezioni Foglio di lavoro dal menu a discesa, noterai due righe di codice visualizzate nella finestra del codice. Una volta selezionato l'evento per il quale si desidera il codice, è possibile eliminare le righe che apparivano di default.

Nota che ogni foglio di lavoro ha una propria finestra di codice. Quando inserisci il codice per Sheet1, funzionerà solo se l'evento si verifica in Sheet1.

Nella finestra del codice di questa cartella di lavoro

Proprio come i fogli di lavoro, se hai un codice evento a livello di cartella di lavoro, puoi inserirlo nella finestra del codice ThisWorkbook.

Quando fai doppio clic su ThisWorkbook, si aprirà la finestra del codice per esso.

Devi selezionare Cartella di lavoro dal menu a discesa in alto a sinistra della finestra del codice.

Dopo aver selezionato Cartella di lavoro dal menu a discesa, viene visualizzato un elenco di tutti gli eventi relativi alla cartella di lavoro. Puoi selezionare quello che desideri utilizzare dal menu a discesa in alto a destra nella finestra del codice.

Non appena si seleziona l'evento, inserirà automaticamente la prima e l'ultima riga del codice per l'evento selezionato. Ora puoi aggiungere il tuo codice tra le due righe.

Nota: non appena selezioni Cartella di lavoro dal menu a discesa, nella finestra del codice vengono visualizzate due righe di codice. Una volta selezionato l'evento per il quale si desidera il codice, è possibile eliminare le righe che apparivano di default.

Nella finestra del codice del form utente

Quando crei UserForm in Excel, puoi anche utilizzare gli eventi UserForm per eseguire codici basati su azioni specifiche. Ad esempio, è possibile specificare un codice che viene eseguito quando si fa clic sul pulsante.

Mentre gli oggetti Sheet e ThisWorkbook sono già disponibili quando apri VB Editor, UserForm è qualcosa che devi creare prima.

Per creare un modulo utente, fai clic con il pulsante destro del mouse su uno degli oggetti, vai su Inserisci e fai clic su Modulo utente.

Ciò inserirebbe un oggetto UserForm nella cartella di lavoro.

Quando si fa doppio clic sul Form utente (o su qualsiasi oggetto che si aggiunge al Form utente), si aprirà la finestra del codice per il Form utente.

Ora, proprio come i fogli di lavoro o ThisWorkbook, puoi selezionare l'evento e inserirà la prima e l'ultima riga per quell'evento. E poi puoi aggiungere il codice nel mezzo.

Nella finestra del codice grafico

In Excel, puoi anche inserire fogli Grafico (che sono diversi dai fogli di lavoro). Un foglio grafico deve contenere solo grafici.

Dopo aver inserito un foglio grafico, sarai in grado di vedere l'oggetto foglio Grafico nell'editor VB.

Puoi aggiungere il codice dell'evento alla finestra del codice del foglio grafico proprio come abbiamo fatto nel foglio di lavoro.

Fare doppio clic sull'oggetto del foglio Grafico in Esplora progetti. Questo aprirà la finestra del codice per il foglio grafico.

Ora devi selezionare Grafico dal menu a discesa in alto a sinistra della finestra del codice.

Dopo aver selezionato Grafico dal menu a discesa, si ottiene un elenco di tutti gli eventi relativi al foglio Grafico. Puoi selezionare quello che desideri utilizzare dal menu a discesa in alto a destra nella finestra del codice.

Nota: non appena selezioni Grafico dal menu a discesa, noterai due righe di codice visualizzate nella finestra del codice. Una volta selezionato l'evento per il quale si desidera il codice, è possibile eliminare le righe che apparivano di default.

Modulo in classe

I moduli di classe devono essere inseriti proprio come i moduli utente.

Un modulo di classe può contenere codice relativo all'applicazione, che sarebbe Excel stesso e i grafici incorporati.

Tratterò il modulo di classe come un tutorial separato nelle prossime settimane.

Si noti che, a parte gli eventi OnTime e OnKey, nessuno degli eventi di cui sopra può essere archiviato nel normale modulo VBA.

Comprensione della sequenza di eventi

Quando attivi un evento, non accade isolatamente. Può anche portare a una sequenza di più trigger.

Ad esempio, quando inserisci un nuovo foglio di lavoro, accadono le seguenti cose:

1. È stato aggiunto un nuovo foglio di lavoro
2. Il foglio di lavoro precedente viene disattivato
3. Il nuovo foglio di lavoro viene attivato

Sebbene nella maggior parte dei casi, potresti non doverti preoccupare della sequenza, se stai creando codici complessi che si basano su eventi, è meglio conoscere la sequenza per evitare risultati imprevisti.

Comprensione del ruolo degli argomenti negli eventi VBA

Prima di passare agli esempi di eventi e alle fantastiche cose che puoi fare con esso, c'è un concetto importante che devo trattare.

Negli eventi VBA, ci sarebbero due tipi di codici:

• Senza argomenti
• Con argomenti

E in questa sezione, voglio coprire rapidamente il ruolo degli argomenti.

Di seguito è riportato un codice che non contiene argomenti (le parentesi sono vuote):

Private Sub Workbook_Open() MsgBox "Ricorda di riempire la scheda attività" End Sub

Con il codice sopra, quando apri una cartella di lavoro, mostra semplicemente una finestra di messaggio con il messaggio "Ricorda di riempire la scheda attività".

Ora diamo un'occhiata a un codice che ha un argomento.

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

Il codice sopra usa l'argomento Sh che è definito come un tipo di oggetto. L'argomento Sh potrebbe essere un foglio di lavoro o un foglio grafico, poiché l'evento precedente viene attivato quando viene aggiunto un nuovo foglio.

Assegnando il nuovo foglio che viene aggiunto alla cartella di lavoro alla variabile oggetto Sh, VBA ci ha consentito di utilizzarlo nel codice. Quindi, per fare riferimento al nuovo nome del foglio, posso usare Sh.Name.

Il concetto di argomenti sarà utile quando esaminerai gli esempi di eventi VBA nelle sezioni successive.

Eventi a livello di cartella di lavoro (spiegati con esempi)

Di seguito sono riportati gli eventi più comunemente utilizzati in una cartella di lavoro.

NOME DELL'EVENTO	COSA SCARICA L'EVENTO
Attivare	Quando una cartella di lavoro è attivata
Dopo il salvataggio	Quando una cartella di lavoro è installata come componente aggiuntivo
Prima di salvare	Quando viene salvata una cartella di lavoro
PrimaChiudi	Quando una cartella di lavoro è chiusa
PrimaStampa	Quando viene stampata una cartella di lavoro
Disattivare	Quando una cartella di lavoro è disattivata
NuovoFoglio	Quando viene aggiunto un nuovo foglio
Aprire	Quando si apre una cartella di lavoro
FoglioAttiva	Quando un foglio nella cartella di lavoro è attivato
FoglioPrima dell'eliminazione	Quando un foglio viene eliminato
FoglioPrima di DoubleClick	Quando si fa doppio clic su qualsiasi foglio
Foglio prima del clic destro	Quando si fa clic con il pulsante destro del mouse su qualsiasi foglio
FoglioCalcola	Quando un foglio viene calcolato o ricalcolato
FoglioDisattiva	Quando una cartella di lavoro è disattivata
Aggiornamento tabella pivot del foglio	Quando una cartella di lavoro viene aggiornata
FoglioSelezioneModifica	Quando una cartella di lavoro viene modificata
WindowActivate	Quando una cartella di lavoro è attivata
Disattiva finestra	Quando una cartella di lavoro è disattivata

Nota che questo non è un elenco completo. Puoi trovare l'elenco completo qui.

Ricorda che il codice per l'evento Workbook è memorizzato nella finestra del codice degli oggetti ThisWorkbook.

Ora diamo un'occhiata ad alcuni utili eventi della cartella di lavoro e vediamo come possono essere utilizzati nel tuo lavoro quotidiano.

Evento aperto cartella di lavoro

Supponiamo che tu voglia mostrare all'utente un promemoria amichevole per riempire le loro schede attività ogni volta che aprono una cartella di lavoro specifica.

Puoi usare il codice seguente per farlo:

Private Sub Workbook_Open() MsgBox "Ricorda di riempire la scheda attività" End Sub

Ora non appena apri la cartella di lavoro con questo codice, ti mostrerà una finestra di messaggio con il messaggio specificato.

Ci sono alcune cose da sapere quando si lavora con questo codice (o con i codici degli eventi della cartella di lavoro in generale):

• Se una cartella di lavoro ha una macro e vuoi salvarla, devi salvarla nel formato .XLSM. Altrimenti il ​​codice macro andrebbe perso.
• Nell'esempio sopra, il codice dell'evento verrebbe eseguito solo quando le macro sono abilitate. Potresti vedere una barra gialla che chiede l'autorizzazione per abilitare le macro. Fino a quando non è abilitato, il codice dell'evento non viene eseguito.
• Il codice dell'evento Workbook viene inserito nella finestra del codice dell'oggetto ThisWorkbook.

Puoi perfezionare ulteriormente questo codice e mostrare il messaggio solo di venerdì.

Il codice seguente farebbe questo:

Private Sub Workbook_Open() wkday = Weekday(Date) If wkday = 6 Then MsgBox "Ricorda di riempire la scheda attività" End Sub

Si noti che nella funzione Weekday, a domenica viene assegnato il valore 1, lunedì è 2 e così via.

Quindi per venerdì ho usato 6.

L'evento Workbook Open può essere utile in molte situazioni, ad esempio:

• Quando vuoi mostrare un messaggio di benvenuto alla persona quando viene aperta una cartella di lavoro.
• Quando si desidera visualizzare un promemoria all'apertura della cartella di lavoro.
• Quando vuoi attivare sempre un foglio di lavoro specifico nella cartella di lavoro quando viene aperto.
• Quando si desidera aprire file correlati insieme alla cartella di lavoro.
• Quando si desidera acquisire la data e l'ora ogni volta che si apre la cartella di lavoro.

Evento NewSheet della cartella di lavoro

L'evento NewSheet viene attivato quando si inserisce un nuovo foglio nella cartella di lavoro.

Supponiamo che tu voglia inserire il valore di data e ora nella cella A1 del foglio appena inserito. Puoi usare il codice seguente per farlo:

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

Il codice sopra utilizza "On Error Resume Next" per gestire i casi in cui qualcuno inserisce un foglio grafico e non un foglio di lavoro. Poiché il foglio grafico non ha la cella A1, mostrerebbe un errore se "On Error Resume Next" non viene utilizzato.

Un altro esempio potrebbe essere quando si desidera applicare alcune impostazioni di base o formattazione a un nuovo foglio non appena viene aggiunto. Ad esempio, se desideri aggiungere un nuovo foglio e desideri che ottenga automaticamente un numero di serie (fino a 100), puoi utilizzare il codice seguente.

Private Sub Workbook_NewSheet(ByVal Sh As Object) In caso di errore Riprendi successivo con Sh.Range("A1") .Value = "S. No." .Interior.Color = vbBlue .Font.Color = vbWhite End With For i = da 1 a 100 Sh.Range("A1").Offset(i, 0).Value = i Next i Sh.Range("A1", Range ("A1").End(xlDown)).Borders.LineStyle = xlContinuous End Sub

Il codice sopra fa anche un po' di formattazione. Dà alla cella di intestazione un colore blu e rende il carattere bianco. Applica anche un bordo a tutte le celle piene.

Il codice sopra è un esempio di come un breve codice VBA può aiutarti a rubare alcuni secondi ogni volta che inserisci un nuovo foglio di lavoro (nel caso in cui sia qualcosa che devi fare ogni volta).

Cartella di lavoro prima di salvare l'evento

L'evento Before Save viene attivato quando si salva una cartella di lavoro. Si noti che prima viene attivato l'evento e quindi viene salvata la cartella di lavoro.

Quando si salva una cartella di lavoro di Excel, potrebbero esserci due possibili scenari:

1. Lo stai salvando per la prima volta e mostrerà la finestra di dialogo Salva con nome.
2. Lo hai già salvato in precedenza e semplicemente salverà e sovrascriverà le modifiche nella versione già salvata.

Ora diamo un'occhiata ad alcuni esempi in cui è possibile utilizzare l'evento BeforeSave.

Supponiamo di avere una nuova cartella di lavoro che stai salvando per la prima volta e di voler ricordare all'utente di salvarla nell'unità K, quindi puoi utilizzare il codice seguente:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then MsgBox "Salva questo file nell'unità K" End Sub

Nel codice precedente, se il file non è mai stato salvato, SaveAsUI è True e visualizza la finestra di dialogo Salva con nome. Il codice precedente visualizzerà il messaggio prima che venga visualizzata la finestra di dialogo Salva con nome.

Un altro esempio potrebbe essere quello di aggiornare la data e l'ora in cui il file viene salvato in una cella specifica.

Il codice seguente inserirà la data e l'ora nella cella A1 di Sheet1 ogni volta che il file viene salvato.

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

Si noti che questo codice viene eseguito non appena l'utente salva la cartella di lavoro. Se la cartella di lavoro viene salvata per la prima volta, mostrerà una finestra di dialogo Salva con nome. Ma il codice è già stato eseguito quando viene visualizzata la finestra di dialogo Salva con nome. A questo punto, se decidessi di cancellare e non salvare la cartella di lavoro, la data e l'ora sarebbero già state inserite nella cella.

Cartella di lavoro prima della chiusura dell'evento

L'evento Before Close si verifica subito prima della chiusura della cartella di lavoro.

Il codice seguente protegge tutti i fogli di lavoro prima che la cartella di lavoro venga chiusa.

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

Ricorda che il codice dell'evento viene attivato non appena chiudi la cartella di lavoro.

Una cosa importante da sapere su questo evento è che non importa se la cartella di lavoro è effettivamente chiusa o meno.

Nel caso in cui la cartella di lavoro non sia stata salvata e ti venga mostrato il messaggio che chiede se salvare o meno la cartella di lavoro e fai clic su Annulla, la cartella di lavoro non verrà salvata.Tuttavia, a quel punto il codice dell'evento sarebbe già stato eseguito.

Cartella di lavoro prima dell'evento di stampa

Quando si impartisce il comando di stampa (o il comando Anteprima di stampa), viene attivato l'evento Prima della stampa.

Il codice seguente ricalcolerebbe tutti i fogli di lavoro prima che la cartella di lavoro venga stampata.

Private Sub Workbook_BeforePrint(Cancel As Boolean) Per ogni ws in Worksheets ws.Calculate Next ws End Sub

Quando l'utente sta stampando la cartella di lavoro, l'evento viene generato indipendentemente dal fatto che stia stampando l'intera cartella di lavoro o solo una parte di essa.

Un altro esempio di seguito è il codice che aggiungerà la data e l'ora al piè di pagina quando viene stampata la cartella di lavoro.

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

Eventi a livello di foglio di lavoro (spiegati con esempi)

Gli eventi del foglio di lavoro si verificano in base ai trigger nel foglio di lavoro.

Di seguito sono riportati gli eventi più comunemente utilizzati in un foglio di lavoro.

Nome dell'evento	Cosa fa scattare l'evento
Attivare	Quando il foglio di lavoro è attivato
Prima dell'eliminazione	Prima che il foglio di lavoro venga eliminato
Prima di DoubleClick	Prima di fare doppio clic sul foglio di lavoro
Prima di RightClick	Prima di fare clic con il pulsante destro del mouse sul foglio di lavoro
Calcolare	Prima che il foglio di lavoro venga calcolato o ricalcolato
Modificare	Quando le celle nel foglio di lavoro vengono modificate
Disattivare	Quando il foglio di lavoro è disattivato
Aggiornamento tabella pivot	Quando la tabella pivot nel foglio di lavoro viene aggiornata
SelezioneModifica	Quando la selezione sul foglio di lavoro viene modificata

Nota che questo non è un elenco completo. Puoi trovare l'elenco completo qui.

Ricordare che il codice per l'evento Foglio di lavoro è memorizzato nella finestra del codice oggetto del foglio di lavoro (in quella in cui si desidera che l'evento venga attivato). Possono esserci più fogli di lavoro in una cartella di lavoro e il tuo codice verrebbe attivato solo quando l'evento si verifica nel foglio di lavoro in cui è inserito.

Ora diamo un'occhiata ad alcuni utili eventi del foglio di lavoro e vediamo come possono essere utilizzati nel tuo lavoro quotidiano.

Foglio di lavoro Attiva evento

Questo evento viene generato quando si attiva un foglio di lavoro.

Il codice seguente rimuove la protezione di un foglio non appena viene attivato.

Private Sub Worksheet_Activate() ActiveSheet.Unprotect End Sub

Puoi anche usare questo evento per assicurarti che una cella specifica o un intervallo di celle (o un intervallo denominato) sia selezionato non appena attivi il foglio di lavoro. Il codice seguente selezionerà la cella D1 non appena attivi il foglio.

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

Evento di modifica del foglio di lavoro

Un evento di modifica viene generato ogni volta che si apporta una modifica nel foglio di lavoro.

Beh… non sempre.

Ci sono alcune modifiche che attivano l'evento e altre no. Di seguito è riportato un elenco di alcune modifiche che non attiveranno l'evento:

• Quando modifichi la formattazione della cella (dimensione del carattere, colore, bordo, ecc.).
• Quando unisci le celle. Ciò è sorprendente poiché a volte l'unione di celle rimuove anche il contenuto da tutte le celle tranne quella in alto a sinistra.
• Quando aggiungi, elimini o modifichi un commento di cella.
• Quando ordini un intervallo di celle.
• Quando usi la ricerca obiettivo.

Le seguenti modifiche attiverebbero l'evento (anche se potresti pensare che non dovrebbe):

• Copiare e incollare la formattazione attiverebbe l'evento.
• La cancellazione della formattazione attiverebbe l'evento.
• L'esecuzione di un controllo ortografico attiverebbe l'evento.

Di seguito è riportato un codice che mostrerebbe una finestra di messaggio con l'indirizzo della cella che è stata modificata.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "Hai appena cambiato " & Target.Address End Sub

Sebbene questa sia una macro inutile, mostra come utilizzare l'argomento Target per scoprire quali celle sono state modificate.

Ora vediamo un paio di esempi più utili.

Supponiamo che tu abbia un intervallo di celle (diciamo A1: D10) e vuoi mostrare un prompt e chiedere all'utente se voleva davvero cambiare una cella in questo intervallo o meno, puoi usare il codice seguente.

Mostra un prompt con due pulsanti: Sì e No. Se l'utente seleziona "Sì", la modifica viene eseguita, altrimenti viene annullata.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox("Stai apportando una modifica alle celle in A1: D10. Sei sicuro di volerlo?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

Nel codice sopra, controlliamo se la cella Target si trova nelle prime 4 colonne e nelle prime 10 righe. In tal caso, viene visualizzata la finestra di messaggio. Inoltre, se l'utente ha selezionato No nella finestra del messaggio, la modifica viene annullata (dal comando Application.Undo).

Nota che ho usato Application.EnableEvents = False prima della riga Application.Undo. E poi l'ho invertito usando Application.EnableEvent = True nella riga successiva.

Ciò è necessario poiché quando si verifica l'annullamento, attiva anche l'evento di modifica. Se non imposto EnableEvent su False, continuerà ad attivare l'evento di modifica.

È inoltre possibile monitorare le modifiche a un intervallo denominato utilizzando l'evento di modifica. Ad esempio, se si dispone di un intervallo denominato denominato "DataRange" e si desidera visualizzare un prompt nel caso in cui l'utente apporti una modifica in tale intervallo denominato, è possibile utilizzare il codice seguente:

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 "Hai appena apportato una modifica all'intervallo di dati" End If End Sub

Il codice sopra controlla se la cella/l'intervallo in cui sono state apportate le modifiche ha celle comuni all'intervallo di dati. Se lo fa, mostra la finestra di messaggio.

Evento di modifica della selezione della cartella di lavoro

L'evento di modifica della selezione viene attivato ogni volta che si verifica una modifica della selezione nel foglio di lavoro.

Il codice seguente ricalcolerebbe il foglio non appena si modifica la selezione.

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

Un altro esempio di questo evento è quando si desidera evidenziare la riga e la colonna attive della cella selezionata.

Qualcosa come mostrato di seguito:

Il seguente codice può farlo:

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) End With Fine sotto

Il codice rimuove prima il colore di sfondo da tutte le celle e quindi applica quello menzionato nel codice alla riga e alla colonna attive.

E questo è il problema con questo codice. Che rimuove il colore da tutte le cellule.

Se vuoi evidenziare la riga/colonna attiva mantenendo intatto il colore in altre celle, usa la tecnica mostrata in questo tutorial.

Evento DoubleClick della cartella di lavoro

Questo è uno dei miei eventi preferiti del foglio di lavoro e vedrai molti tutorial in cui l'ho usato (come questo o questo).

Questo evento viene attivato quando fai doppio clic su una cella.

Lascia che ti mostri quanto è fantastico.

Con il codice seguente, puoi fare doppio clic su una cella e applicherà un colore di sfondo, cambierà il colore del carattere e renderà il testo nella cella in grassetto;

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

Questo può essere utile quando si scorre un elenco di celle e si desidera evidenziare alcune di quelle selezionate. Mentre puoi usare il tasto F4 per ripetere l'ultimo passaggio, sarebbe in grado di applicare solo un tipo di formattazione. Con questo evento di doppio clic, puoi applicarli tutti e tre con un semplice doppio clic.

Nota che nel codice sopra, ho creato il valore di Cancel = True.

Questo viene fatto in modo che l'azione predefinita del doppio clic sia disabilitata, ovvero entrare nella modalità di modifica. Con Cancel = True, Excel non ti porterà in modalità Modifica quando fai doppio clic sulla cella.

Ecco un altro esempio.

Se disponi di un elenco di attività in Excel, puoi utilizzare l'evento doppio clic per applicare il formato barrato per contrassegnare l'attività come completata.

Qualcosa come mostrato di seguito:

Ecco il codice che lo farà:

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

Nota che in questo codice ho fatto doppio clic come evento di commutazione. Quando fai doppio clic su una cella, controlla se il formato barrato è già stato applicato. Se lo è stato, il doppio clic rimuove il formato barrato e, in caso contrario, viene applicato il formato barrato.

Evento OnTime VBA di Excel

Gli eventi che abbiamo visto finora in questo articolo sono stati associati a uno degli oggetti di Excel, che si tratti della cartella di lavoro, del foglio di lavoro, del foglio grafico o dei moduli utente, ecc.

L'evento OnTime è diverso dagli altri eventi in quanto può essere memorizzato nel normale modulo VBA (mentre gli altri dovevano essere inseriti nella finestra del codice di oggetti come ThisWorkbook o Worksheets o UserForms).

All'interno del normale modulo VBA, viene utilizzato come metodo dell'oggetto applicazione.

Il motivo per cui questo è considerato un evento è che può essere attivato in base all'ora specificata. Ad esempio, se voglio che il foglio venga ricalcolato ogni 5 minuti, posso utilizzare l'evento OnTime.

Oppure, se voglio mostrare un messaggio/promemoria in un momento specifico della giornata, posso utilizzare l'evento OnTime.

Di seguito è riportato un codice che mostrerà un messaggio alle 14:00 ogni giorno.

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

Ricorda che devi inserire questo codice nel normale modulo VBA,

Inoltre, mentre l'evento OnTime viene attivato all'ora specificata, è necessario eseguire la macro manualmente in qualsiasi momento. Una volta eseguita la macro, aspetterà fino alle 14:00 e quindi chiamerà la macro "ShowMessage".

La macro ShowMessage visualizzerà quindi il messaggio.

L'evento OnTime accetta quattro argomenti:

Applicazione.OnTime(PrimaOra, Procedura, UltimeOra, Programma)

• Prima Ora: L'ora in cui si desidera eseguire la procedura.
• Procedura: Il nome della procedura da eseguire.
• Ultimo Ora (Facoltativo): Nel caso in cui sia in esecuzione un altro codice e il codice specificato non può essere eseguito all'ora specificata, è possibile specificare l'ultima ora per la quale deve attendere. Ad esempio, potrebbe essere EarlyTime + 45 (il che significa che attenderà 45 secondi per il completamento dell'altra procedura). Se anche dopo 45 secondi la procedura non può essere eseguita, viene abbandonata. Se non lo specifichi, Excel attenderà l'esecuzione del codice, quindi lo eseguirà.
• Programma (opzionale): Se impostato su True, pianifica una nuova procedura temporale. Se False annulla la procedura precedentemente impostata. Per impostazione predefinita, questo è True.

Nell'esempio sopra, abbiamo usato solo i primi due argomenti.

Diamo un'occhiata a un altro esempio.

Il codice seguente aggiornerebbe il foglio di lavoro ogni 5 min.

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

Il codice sopra aggiornerebbe il foglio di lavoro ogni 5 minuti.

Utilizza la funzione Now per determinare l'ora corrente e quindi aggiunge 5 minuti all'ora corrente.

L'evento OnTime continuerà a essere eseguito finché non lo interrompi. Se chiudi la cartella di lavoro e l'applicazione Excel è ancora in esecuzione (altre cartelle di lavoro sono aperte), la cartella di lavoro in cui è in esecuzione l'evento OnTime si riaprirà.

Questo viene gestito meglio arrestando in modo specifico l'evento OnTime.

Nel codice sopra, ho il codice StopRefresh, ma devi eseguirlo per fermare l'evento OnTime. Puoi farlo manualmente, assegnarlo a un pulsante e farlo premendo il pulsante o chiamandolo dall'evento Chiusura cartella di lavoro.

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

Il codice dell'evento "BeforeClose" sopra va nella finestra del codice ThisWorkbook.

Evento OnKey VBA di Excel

Quando lavori con Excel, continua a monitorare le sequenze di tasti che usi. Questo ci consente di utilizzare le sequenze di tasti come trigger per un evento.

Con l'evento OnKey, puoi specificare una sequenza di tasti (o una combinazione di tasti) e il codice che deve essere eseguito quando viene utilizzato quel tasto. Quando vengono premuti questi tasti, eseguirà il codice per esso.

Proprio come l'evento OnTime, devi avere un modo per annullare l'evento OnKey. Inoltre, quando si imposta l'evento OnKey per una sequenza di tasti specifica, diventa disponibile in tutte le cartelle di lavoro aperte.

Prima di mostrarti un esempio dell'utilizzo dell'evento OnKey, permettimi di condividere i codici chiave disponibili in VBA.

CHIAVE	CODICE
Backspace	{BACKSPACE} o {BS}
Rompere	{ROMPERE}
Blocco maiuscole	{BLOCCO MAIUSCOLE}
Elimina	{CANCELLA} o {CANCELLA}
Freccia in giù	{FUORI USO}
Fine	{FINE}
accedere	~
Invio (sulla tastiera numerica)	{ACCEDERE}
Fuga	{ESC} o {ESC}
Casa	{CASA}
Ins	{INSERIRE}
Freccia sinistra	{SINISTRA}
BlocNum	{BLOC NUM}
Pagina giù	{PGDN}
Pagina su	{PGUP}
Freccia destra	{GIUSTO}
Blocco scorrimento	{BLOCCO SCORR.}
tab	{TAB}
Freccia su	{SU}
da F1 a F15	Da {F1} a {F15}

Quando è necessario utilizzare un evento onkey, è necessario utilizzare il relativo codice.

La tabella sopra ha i codici per le singole battute.

Puoi anche combinarli con i seguenti codici:

• Spostare: + (Segno più)
• Controllo: ^ (accento circonflesso)
• Alt: % (Percentuale)

Ad esempio, per Alt F4, è necessario utilizzare il codice: "%{F4}” - dove % è per il tasto ALT e {F4} è per il tasto F4.

Ora diamo un'occhiata a un esempio (ricorda che il codice per gli eventi OnKey è inserito nel normale modulo VBA).

Quando premi il tasto PageUp o PageDown, salta 29 righe sopra/sotto la cella attiva (almeno è quello che sta facendo sul mio laptop).

Se vuoi che salti solo 5 righe alla volta, puoi utilizzare il codice seguente:

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

Quando esegui la prima parte del codice, verranno eseguiti gli eventi OnKey. Una volta eseguito, l'uso dei tasti PagSu e PagGiù fa sì che il cursore salti solo 5 righe alla volta.

Nota che abbiamo usato "On Error Resume Next" per assicurarci che gli errori vengano ignorati. Questi errori possono verificarsi quando premi il tasto PagSu anche quando sei nella parte superiore del foglio di lavoro. Poiché non ci sono più righe da saltare, il codice mostrerebbe un errore. Ma dal momento che abbiamo usato "On Error Resume Next", verrà ignorato.

Per assicurarti che questi eventi OnKey siano disponibili, devi eseguire la prima parte del codice. Nel caso in cui desideri che questo sia disponibile non appena apri la cartella di lavoro, puoi inserirlo nella finestra del codice ThisWorkbook.

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

Il codice sottostante riporterà le chiavi alla loro normale funzionalità.

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

Quando non specifichi il secondo argomento nel metodo OnKey, restituirà la sequenza di tasti alla sua normale funzionalità.

Nel caso in cui si desideri annullare la funzionalità di una sequenza di tasti, in modo che Excel non faccia nulla quando viene utilizzata quella sequenza di tasti, è necessario utilizzare una stringa vuota come secondo argomento.

Nel codice seguente, Excel non farebbe nulla quando usiamo i tasti PageUp o PageDown.

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

Disabilitare gli eventi in VBA

A volte potrebbe essere necessario disabilitare gli eventi per far funzionare correttamente il codice.

Ad esempio, supponiamo di avere un intervallo (A1: D10) e di voler mostrare un messaggio ogni volta che una cella viene modificata in questo intervallo. Quindi mostro una finestra di messaggio e chiedo all'utente se è sicuro di voler apportare la modifica. Se la risposta è Sì, la modifica viene apportata e se la risposta è No, VBA la annullerà.

Puoi usare il seguente codice:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox("Stai apportando una modifica alle celle in A1: D10. Sei sicuro di volerlo?", vbYesNo) End If If Ans = vbNo Then Application.Undo End If End Sub

Il problema con questo codice è che quando l'utente seleziona No nella finestra del messaggio, l'azione viene invertita (come ho usato Application.Undo).

Quando si verifica l'annullamento e il valore viene riportato a quello originale, l'evento di modifica VBA viene nuovamente attivato e all'utente viene nuovamente mostrata la stessa finestra di messaggio.

Ciò significa che puoi continuare a fare clic su NO nella finestra del messaggio e continuerà a essere visualizzato. Questo accade perché in questo caso sei bloccato nel ciclo infinito.

Per evitare tali casi, è necessario disabilitare gli eventi in modo che l'evento di modifica (o qualsiasi altro evento) non venga attivato.

Il seguente codice funzionerebbe bene in questo caso:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox("Stai apportando una modifica alle celle in A1: D10. Sei sicuro di volerlo?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

Nel codice sopra, proprio sopra la riga Application.Undo, abbiamo usato - Application.EnableEvents = False.

L'impostazione di EnableEvents su False non attiverebbe alcun evento (nella cartella di lavoro corrente o aperta).

Una volta completata l'operazione di annullamento, possiamo riportare la proprietà EnableEvents a True.

Tieni presente che la disattivazione degli eventi influisce su tutte le cartelle di lavoro attualmente aperte (o aperte mentre EnableEvents è impostato su False). Ad esempio, come parte del codice, se apri una nuova cartella di lavoro, l'evento Workbook Open non funzionerà.

Impatto degli eventi Annulla stack

Lascia che ti dica prima che cos'è uno stack di annullamento.

Quando lavori in Excel, continua a monitorare le tue azioni. Quando commetti un errore, puoi sempre utilizzare Ctrl + Z per tornare al passaggio precedente (ad esempio, annullare l'azione corrente).

Se premi Control + Z due volte, tornerai indietro di due passaggi. Questi passaggi che hai eseguito vengono memorizzati come parte dello stack di annullamento.

Qualsiasi evento che modifica il foglio di lavoro distrugge questo stack di annullamento.Ciò significa che se ho fatto 5 cose prima di attivare un evento, non sarò in grado di utilizzare Control + Z per tornare ai passaggi precedenti. L'attivazione dell'evento ha distrutto quello stack per me.

Nel codice seguente, utilizzo VBA per inserire il timestamp nella cella A1 ogni volta che si verifica una modifica nel foglio di lavoro.

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

Poiché sto apportando una modifica al foglio di lavoro, ciò distruggerà lo stack di annullamento.

Inoltre, tieni presente che questo non è limitato solo agli eventi.

Se si dispone di un codice memorizzato nel normale modulo VBA e si apporta una modifica nel foglio di lavoro, si distruggerebbe anche lo stack di annullamento in Excel.

Ad esempio, il codice seguente immette semplicemente il testo "Ciao" nella cella A1, ma anche l'esecuzione distruggerebbe lo stack di annullamento.

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

Potrebbero piacerti anche i seguenti tutorial VBA di Excel:

• Lavorare con celle e intervalli in Excel VBA.
• Lavorare con i fogli di lavoro in Excel VBA.
• Lavorare con le cartelle di lavoro in Excel VBA.
• Cicli VBA di Excel: la guida definitiva.
• Utilizzo dell'istruzione IF Then Else in Excel VBA.
• Per il ciclo successivo in Excel.
• Creazione di funzioni definite dall'utente in Excel VBA.
• Come creare e utilizzare componenti aggiuntivi in ​​Excel.
• Crea e riutilizza le macro salvandole nella cartella di lavoro delle macro personali.