Événements Excel VBA - Un guide simple (et complet)

Lorsque vous créez ou enregistrez une macro dans Excel, vous devez exécuter la macro pour exécuter les étapes du code.

Quelques façons d'exécuter une macro incluent l'utilisation de la boîte de dialogue de macro, l'affectation de la macro à un bouton, l'utilisation d'un raccourci, etc.

Outre ces exécutions de macros lancées par l'utilisateur, vous pouvez également utiliser des événements VBA pour exécuter la macro.

Événements Excel VBA - Introduction

Permettez-moi d'abord d'expliquer ce qu'est un événement en VBA.

Un événement est une action qui peut déclencher l'exécution de la macro spécifiée.

Par exemple, lorsque vous ouvrez un nouveau classeur, c'est un événement. Lorsque vous insérez une nouvelle feuille de calcul, c'est un événement. Lorsque vous double-cliquez sur une cellule, c'est un événement.

Il existe de nombreux événements de ce type dans VBA et vous pouvez créer des codes pour ces événements. Cela signifie que dès qu'un événement se produit, et si vous avez spécifié un code pour cet événement, ce code sera exécuté instantanément.

Excel le fait automatiquement dès qu'il remarque qu'un événement a eu lieu. Il vous suffit donc d'écrire le code et de le placer dans le sous-programme d'événement approprié (ceci est traité plus loin dans cet article).

Par exemple, si vous insérez une nouvelle feuille de calcul et que vous souhaitez qu'elle ait un préfixe d'année, vous pouvez écrire le code correspondant.

Désormais, chaque fois que quelqu'un insère une nouvelle feuille de calcul, ce code est automatiquement exécuté et ajoute le préfixe de l'année au nom de la feuille de calcul.

Un autre exemple pourrait être que vous vouliez changer la couleur de la cellule lorsque quelqu'un double-clique dessus. Vous pouvez utiliser l'événement double-clic pour cela.

De même, vous pouvez créer des codes VBA pour de nombreux événements de ce type (comme nous le verrons plus loin dans cet article).

Vous trouverez ci-dessous un court visuel qui montre l'événement de double-clic en action. Dès que je double-clique sur la cellule A1. Excel ouvre instantanément une boîte de message qui affiche l'adresse de la cellule.

Le double-clic est un événement et l'affichage de la boîte de message est ce que j'ai spécifié dans le code chaque fois que l'événement de double-clic a lieu.

Bien que l'exemple ci-dessus soit un événement inutile, j'espère qu'il vous aidera à comprendre ce que sont réellement les événements.

Différents types d'événements Excel VBA

Il existe différents objets dans Excel - tels qu'Excel lui-même (que nous appelons souvent l'application), des classeurs, des feuilles de calcul, des graphiques, etc.

Chacun de ces objets peut être associé à divers événements. Par exemple:

• Si vous créez un nouveau classeur, il s'agit d'un événement au niveau de l'application.
• Si vous ajoutez une nouvelle feuille de calcul, il s'agit d'un événement au niveau du classeur.
• Si vous modifiez la valeur d'une cellule d'une feuille, il s'agit d'un événement au niveau de la feuille de calcul.

Voici les différents types d'événements qui existent dans Excel :

1. Événements au niveau de la feuille de calcul : Ce sont les types d'événements qui se déclencheraient en fonction des actions entreprises dans la feuille de calcul. Des exemples de ces événements incluent la modification d'une cellule dans la feuille de calcul, la modification de la sélection, un double-clic sur une cellule, un clic droit sur une cellule, etc.
2. Événements au niveau du classeur : Ces événements seraient déclenchés en fonction des actions au niveau du classeur. Des exemples de ces événements incluent l'ajout d'une nouvelle feuille de calcul, l'enregistrement du classeur, l'ouverture du classeur, l'impression d'une partie ou de l'intégralité du classeur, etc.
3. Événements au niveau de l'application : Ce sont les événements qui se produisent dans l'application Excel. Par exemple, la fermeture de l'un des classeurs ouverts ou l'ouverture d'un nouveau classeur.
4. Événements au niveau du formulaire utilisateur : Ces événements seraient déclenchés en fonction des actions dans le « UserForm ». Les exemples incluent l'initialisation d'un formulaire utilisateur ou le clic sur un bouton dans le formulaire utilisateur.
5. Événements de graphique : Ce sont des événements liés à la feuille de graphique. Une feuille de graphique est différente d'une feuille de calcul (c'est là que la plupart d'entre nous ont l'habitude de travailler dans Excel). Le but des feuilles de graphique est de tenir un graphique. Des exemples de tels événements incluent la modification de la série du graphique ou le redimensionnement du graphique.
6. Événements OnTime et OnKey: Ce sont deux événements qui ne rentrent dans aucune des catégories ci-dessus. Je les ai donc répertoriés séparément. L'événement « OnTime » vous permet d'exécuter un code à un moment précis ou après un certain temps. L'événement « OnKey » vous permet d'exécuter un code lorsqu'une frappe spécifique (ou une combinaison de frappes) est utilisée.

Où mettre le code lié à l'événement

Dans la section ci-dessus, j'ai couvert les différents types d'événements.

En fonction du type d'événement, vous devez mettre le code dans l'objet correspondant.

Par exemple, s'il s'agit d'un événement lié à une feuille de calcul, il doit aller dans la fenêtre de code de l'objet de feuille de calcul. S'il s'agit d'un classeur, il doit aller dans la fenêtre de code d'un objet classeur.

Dans VBA, différents objets - tels que les feuilles de calcul, les classeurs, les feuilles de graphique, les formulaires utilisateur, etc., ont leurs propres fenêtres de code. Vous devez mettre le code de l'événement dans la fenêtre de code de l'objet concerné. Par exemple, s'il s'agit d'un événement au niveau du classeur, vous devez avoir le code de l'événement dans la fenêtre Code du classeur.

Les sections suivantes couvrent les endroits où vous pouvez mettre le code d'événement :

Dans la fenêtre de code de la feuille de calcul

Lorsque vous ouvrez l'éditeur VB (à l'aide du raccourci clavier ALT + F11), vous remarquerez l'objet feuilles de calcul dans l'explorateur de projet. Pour chaque feuille de calcul du classeur, vous verrez un objet.

Lorsque vous double-cliquez sur l'objet de feuille de calcul dans lequel vous souhaitez placer le code, la fenêtre de code de cette feuille de calcul s'ouvre.

Bien que vous puissiez commencer à écrire le code à partir de zéro, il est préférable de sélectionner l'événement dans une liste d'options et de laisser VBA insérer automatiquement le code pertinent pour l'événement sélectionné.

Pour ce faire, vous devez d'abord sélectionner la feuille de calcul dans la liste déroulante en haut à gauche de la fenêtre de code.

Après avoir sélectionné la feuille de calcul dans la liste déroulante, vous obtenez une liste de tous les événements liés à la feuille de calcul. Vous pouvez sélectionner celui que vous souhaitez utiliser dans la liste déroulante en haut à droite de la fenêtre de code.

Dès que vous sélectionnez l'événement, il entrera automatiquement la première et la dernière ligne du code de l'événement sélectionné. Vous pouvez maintenant ajouter votre code entre les deux lignes.

Remarque : dès que vous sélectionnez Feuille de calcul dans la liste déroulante, vous remarquerez que deux lignes de code apparaissent dans la fenêtre de code. Une fois que vous avez sélectionné l'événement pour lequel vous souhaitez le code, vous pouvez supprimer les lignes qui apparaissaient par défaut.

Notez que chaque feuille de calcul a sa propre fenêtre de code. Lorsque vous mettez le code pour Sheet1, cela ne fonctionnera que si l'événement se produit dans Sheet1.

Dans la fenêtre de code de ce classeur

Tout comme les feuilles de calcul, si vous avez un code d'événement au niveau du classeur, vous pouvez le placer dans la fenêtre de code ThisWorkbook.

Lorsque vous double-cliquez sur ThisWorkbook, la fenêtre de code correspondante s'ouvre.

Vous devez sélectionner le classeur dans le menu déroulant en haut à gauche de la fenêtre de code.

Après avoir sélectionné le classeur dans la liste déroulante, vous obtenez une liste de tous les événements liés au classeur. Vous pouvez sélectionner celui que vous souhaitez utiliser dans la liste déroulante en haut à droite de la fenêtre de code.

Dès que vous sélectionnez l'événement, il entrera automatiquement la première et la dernière ligne du code de l'événement sélectionné. Vous pouvez maintenant ajouter votre code entre les deux lignes.

Remarque : dès que vous sélectionnez Workbook dans la liste déroulante, vous remarquerez que deux lignes de code apparaissent dans la fenêtre de code. Une fois que vous avez sélectionné l'événement pour lequel vous souhaitez le code, vous pouvez supprimer les lignes qui apparaissaient par défaut.

Dans la fenêtre de code du formulaire utilisateur

Lorsque vous créez des UserForms dans Excel, vous pouvez également utiliser des événements UserForm pour exécuter des codes en fonction d'actions spécifiques. Par exemple, vous pouvez spécifier un code qui est exécuté lorsque le bouton est cliqué.

Bien que les objets Sheet et ThisWorkbook soient déjà disponibles lorsque vous ouvrez l'éditeur VB, UserForm est quelque chose que vous devez d'abord créer.

Pour créer un UserForm, cliquez avec le bouton droit sur l'un des objets, allez dans Insérer et cliquez sur UserForm.

Cela insérerait un objet UserForm dans le classeur.

Lorsque vous double-cliquez sur l'UserForm (ou l'un des objets que vous ajoutez à l'UserForm), il ouvrirait la fenêtre de code pour l'UserForm.

Maintenant, tout comme les feuilles de calcul ou ThisWorkbook, vous pouvez sélectionner l'événement et il insérera la première et la dernière ligne pour cet événement. Et puis vous pouvez ajouter le code au milieu de celui-ci.

Dans la fenêtre de code graphique

Dans Excel, vous pouvez également insérer des feuilles de graphique (qui sont différentes des feuilles de calcul). Une feuille de graphique est censée contenir uniquement des graphiques.

Lorsque vous avez inséré une feuille de graphique, vous pourrez voir l'objet Feuille de graphique dans l'éditeur VB.

Vous pouvez ajouter le code d'événement à la fenêtre de code de la feuille de graphique comme nous l'avons fait dans la feuille de calcul.

Double-cliquez sur l'objet Feuille de graphique dans l'explorateur de projet. Cela ouvrira la fenêtre de code pour la feuille de graphique.

Maintenant, vous devez sélectionner Graphique dans le menu déroulant en haut à gauche de la fenêtre de code.

Après avoir sélectionné Graphique dans la liste déroulante, vous obtenez une liste de tous les événements liés à la feuille Graphique. Vous pouvez sélectionner celui que vous souhaitez utiliser dans la liste déroulante en haut à droite de la fenêtre de code.

Remarque : dès que vous sélectionnez Graphique dans la liste déroulante, vous remarquerez que deux lignes de code apparaissent dans la fenêtre de code. Une fois que vous avez sélectionné l'événement pour lequel vous souhaitez le code, vous pouvez supprimer les lignes qui apparaissaient par défaut.

Module en classe

Les modules de classe doivent être insérés tout comme les UserForms.

Un module de classe peut contenir du code lié à l'application - qui serait Excel lui-même et les graphiques intégrés.

Je couvrirai le module de classe en tant que tutoriel séparé dans les semaines à venir.

Notez qu'à part les événements OnTime et OnKey, aucun des événements ci-dessus ne peut être stocké dans le module VBA standard.

Comprendre la séquence d'événements

Lorsque vous déclenchez un événement, il ne se produit pas isolément. Cela peut également conduire à une séquence de plusieurs déclencheurs.

Par exemple, lorsque vous insérez une nouvelle feuille de calcul, les événements suivants se produisent :

1. Une nouvelle feuille de calcul est ajoutée
2. La feuille de calcul précédente est désactivée
3. La nouvelle feuille de calcul est activée

Bien que dans la plupart des cas, vous n'ayez pas à vous soucier de la séquence, si vous créez des codes complexes qui reposent sur des événements, il est préférable de connaître la séquence pour éviter des résultats inattendus.

Comprendre le rôle des arguments dans les événements VBA

Avant de passer aux exemples d'événements et aux choses impressionnantes que vous pouvez faire avec, il y a un concept important que je dois couvrir.

Dans les événements VBA, il y aurait deux types de codes :

• Sans aucun argument
• Avec des arguments

Et dans cette section, je veux couvrir rapidement le rôle des arguments.

Vous trouverez ci-dessous un code sans argument (les parenthèses sont vides) :

Private Sub Workbook_Open() MsgBox "N'oubliez pas de remplir la feuille de temps" End Sub

Avec le code ci-dessus, lorsque vous ouvrez un classeur, il affiche simplement une boîte de message avec le message - "N'oubliez pas de remplir la feuille de temps".

Voyons maintenant un code qui a un argument.

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

Le code ci-dessus utilise l'argument Sh qui est défini comme un type d'objet. L'argument Sh peut être une feuille de calcul ou une feuille de graphique, car l'événement ci-dessus est déclenché lorsqu'une nouvelle feuille est ajoutée.

En affectant la nouvelle feuille qui est ajoutée au classeur à la variable objet Sh, VBA nous a permis de l'utiliser dans le code. Donc, pour faire référence au nouveau nom de la feuille, je peux utiliser Sh.Name.

Le concept d'arguments sera utile lorsque vous passerez en revue les exemples d'événements VBA dans les sections suivantes.

Événements au niveau du classeur (expliqués avec des exemples)

Voici les événements les plus couramment utilisés dans un classeur.

NOM DE L'ÉVÉNEMENT	CE QUI DÉCLENCHE L'ÉVÉNEMENT
Activer	Lorsqu'un classeur est activé
AprèsEnregistrer	Lorsqu'un classeur est installé en tant que complément
AvantEnregistrer	Lorsqu'un classeur est enregistré
AvantFermer	Lorsqu'un classeur est fermé
AvantImprimer	Lorsqu'un classeur est imprimé
Désactiver	Lorsqu'un classeur est désactivé
NouvelleFeuille	Lorsqu'une nouvelle feuille est ajoutée
Ouvert	Lorsqu'un classeur est ouvert
FeuilleActiver	Lorsqu'une feuille du classeur est activée
FeuilleAvantSupprimer	Lorsqu'une feuille est supprimée
FeuilleAvantDoubleClick	Lorsqu'une feuille est double-cliquée
FeuilleAvantClicDroit	Lorsqu'une feuille est cliqué avec le bouton droit de la souris
FeuilleCalculer	Lorsqu'une feuille est calculée ou recalculée
FeuilleDésactiver	Lorsqu'un classeur est désactivé
SheetPivotTableUpdate	Lorsqu'un classeur est mis à jour
FeuilleSélectionChanger	Lorsqu'un classeur est modifié
FenêtreActiver	Lorsqu'un classeur est activé
FenêtreDésactiver	Lorsqu'un classeur est désactivé

Notez qu'il ne s'agit pas d'une liste complète. Vous pouvez trouver la liste complète ici.

N'oubliez pas que le code de l'événement Workbook est stocké dans la fenêtre de code des objets ThisWorkbook.

Jetons maintenant un coup d'œil à quelques événements de classeur utiles et voyons comment ils peuvent être utilisés dans votre travail quotidien.

Événement d'ouverture de classeur

Disons que vous souhaitez montrer à l'utilisateur un rappel convivial pour remplir ses feuilles de temps chaque fois qu'il ouvre un classeur spécifique.

Vous pouvez utiliser le code ci-dessous pour ce faire :

Private Sub Workbook_Open() MsgBox "N'oubliez pas de remplir la feuille de temps" End Sub

Désormais, dès que vous ouvrez le classeur contenant ce code, une boîte de message contenant le message spécifié s'affiche.

Il y a quelques choses à savoir lorsque vous travaillez avec ce code (ou les codes d'événement de classeur en général) :

• Si un classeur contient une macro et que vous souhaitez l'enregistrer, vous devez l'enregistrer au format .XLSM. Sinon, le code de la macro serait perdu.
• Dans l'exemple ci-dessus, le code d'événement serait exécuté uniquement lorsque les macros sont activées. Vous pouvez voir une barre jaune demandant la permission d'activer les macros. Tant que cela n'est pas activé, le code d'événement n'est pas exécuté.
• Le code d'événement Workbook est placé dans la fenêtre de code de l'objet ThisWorkbook.

Vous pouvez affiner davantage ce code et afficher le message uniquement du vendredi.

Le code ci-dessous ferait ceci:

Private Sub Workbook_Open() wkday = Weekday(Date) If wkday = 6 Then MsgBox "N'oubliez pas de remplir la feuille de temps" End Sub

Notez que dans la fonction Jour de la semaine, dimanche se voit attribuer la valeur 1, lundi est 2 et ainsi de suite.

Donc pour vendredi, j'en ai utilisé 6.

L'événement Workbook Open peut être utile dans de nombreuses situations, telles que :

• Lorsque vous souhaitez afficher un message de bienvenue à la personne lorsqu'un classeur est ouvert.
• Lorsque vous souhaitez afficher un rappel lorsque le classeur est ouvert.
• Lorsque vous souhaitez toujours activer une feuille de calcul spécifique dans le classeur lorsqu'il est ouvert.
• Lorsque vous souhaitez ouvrir des fichiers associés avec le classeur.
• Lorsque vous souhaitez capturer la date et l'heure à chaque ouverture du classeur.

Événement de nouvelle feuille de classeur

L'événement NewSheet est déclenché lorsque vous insérez une nouvelle feuille dans le classeur.

Supposons que vous souhaitiez saisir la valeur de date et d'heure dans la cellule A1 de la feuille nouvellement insérée. Vous pouvez utiliser le code ci-dessous pour ce faire :

Private Sub Workbook_NewSheet(ByVal Sh As Object) En cas d'erreur Resume Next Sh.Range("A1") = Format(Maintenant, "dd-mmm-yyyy hh:mm:ss") End Sub

Le code ci-dessus utilise « On Error Resume Next » pour gérer les cas où quelqu'un insère une feuille de graphique et non une feuille de calcul. Étant donné que la feuille de graphique n'a pas de cellule A1, une erreur s'afficherait si « On Error Resume Next » n'est pas utilisé.

Un autre exemple peut être lorsque vous souhaitez appliquer un paramètre ou un formatage de base à une nouvelle feuille dès qu'elle est ajoutée. Par exemple, si vous souhaitez ajouter une nouvelle feuille et que vous souhaitez qu'elle reçoive automatiquement un numéro de série (jusqu'à 100), vous pouvez utiliser le code ci-dessous.

Private Sub Workbook_NewSheet(ByVal Sh As Object) En cas d'erreur Reprendre ensuite avec 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

Le code ci-dessus fait également un peu de formatage. Il donne à la cellule d'en-tête une couleur bleue et rend la police blanche. Il applique également une bordure à toutes les cellules remplies.

Le code ci-dessus est un exemple de la façon dont un code VBA court peut vous aider à voler quelques secondes chaque fois que vous insérez une nouvelle feuille de calcul (au cas où vous devriez le faire à chaque fois).

Classeur BeforeSave Event

L'événement Before Save est déclenché lorsque vous enregistrez un classeur. Notez que l'événement est déclenché en premier, puis le classeur est enregistré.

Lors de l'enregistrement d'un classeur Excel, il peut y avoir deux scénarios possibles :

1. Vous l'enregistrez pour la première fois et la boîte de dialogue Enregistrer sous s'affichera.
2. Vous l'avez déjà enregistré plus tôt et il enregistrera et écrasera simplement les modifications de la version déjà enregistrée.

Voyons maintenant quelques exemples où vous pouvez utiliser l'événement BeforeSave.

Supposons que vous ayez un nouveau classeur que vous enregistrez pour la première fois et que vous souhaitiez rappeler à l'utilisateur de l'enregistrer dans le lecteur K, vous pouvez alors utiliser le code ci-dessous :

Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then MsgBox "Enregistrer ce fichier dans le lecteur K" End Sub

Dans le code ci-dessus, si le fichier n'a jamais été enregistré, SaveAsUI a la valeur True et affiche la boîte de dialogue Enregistrer sous. Le code ci-dessus afficherait le message avant que la boîte de dialogue Enregistrer sous n'apparaisse.

Un autre exemple pourrait être de mettre à jour la date et l'heure lorsque le fichier est enregistré dans une cellule spécifique.

Le code ci-dessous insère la date et l'heure dans la cellule A1 de Sheet1 chaque fois que le fichier est enregistré.

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

Notez que ce code est exécuté dès que l'utilisateur enregistre le classeur. Si le classeur est enregistré pour la première fois, il affichera une boîte de dialogue Enregistrer sous. Mais le code est déjà exécuté au moment où vous voyez la boîte de dialogue Enregistrer sous. À ce stade, si vous décidez d'annuler et de ne pas enregistrer le classeur, la date et l'heure seront déjà entrées dans la cellule.

Classeur BeforeClose Event

L'événement Before Close se produit juste avant la fermeture du classeur.

Le code ci-dessous protège toutes les feuilles de calcul avant la fermeture du classeur.

Private Sub Workbook_BeforeClose (Annuler en tant que booléen) Dim sh comme feuille de calcul pour chaque sh dans ThisWorkbook.Worksheets sh.Protect Next sh End Sub

N'oubliez pas que le code d'événement est déclenché dès que vous fermez le classeur.

Une chose importante à savoir à propos de cet événement est qu'il ne se soucie pas de savoir si le classeur est réellement fermé ou non.

Si le classeur n'a pas été enregistré et que l'invite vous demande si vous souhaitez enregistrer le classeur ou non, et que vous cliquez sur Annuler, votre classeur ne sera pas enregistré.Cependant, le code de l'événement aurait déjà été exécuté à ce moment-là.

Evénement avant impression du classeur

Lorsque vous donnez la commande d'impression (ou la commande Aperçu avant impression), l'événement Avant impression est déclenché.

Le code ci-dessous recalculerait toutes les feuilles de calcul avant l'impression de votre classeur.

Private Sub Workbook_BeforePrint (Annuler en tant que booléen) pour chaque ws dans les feuilles de calcul ws.Calculer le prochain ws End Sub

Lorsque l'utilisateur imprime le classeur, l'événement est déclenché, qu'il imprime l'intégralité du classeur ou seulement une partie de celui-ci.

Un autre exemple ci-dessous est celui du code qui ajouterait la date et l'heure au pied de page lors de l'impression du classeur.

Private Sub Workbook_BeforePrint (Annuler en tant que booléen) Dim ws As Worksheet pour chaque ws dans ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Printed On - " & Format (Maintenant, "dd-mmm-yyyy hh:mm") Next ws End Sub

Événements au niveau de la feuille de calcul (expliqués avec des exemples)

Les événements de feuille de calcul ont lieu en fonction des déclencheurs de la feuille de calcul.

Voici les événements les plus couramment utilisés dans une feuille de calcul.

Nom de l'événement	Qu'est-ce qui déclenche l'événement
Activer	Lorsque la feuille de calcul est activée
AvantSupprimer	Avant la suppression de la feuille de calcul
Avant DoubleClick	Avant de double-cliquer sur la feuille de calcul
AvantClicDroit	Avant de cliquer avec le bouton droit sur la feuille de calcul
Calculer	Avant que la feuille de calcul ne soit calculée ou recalculée
Changement	Lorsque les cellules de la feuille de calcul sont modifiées
Désactiver	Lorsque la feuille de calcul est désactivée
Mise à jour du tableau croisé dynamique	Lorsque le tableau croisé dynamique de la feuille de calcul est mis à jour
SélectionModifier	Lorsque la sélection sur la feuille de calcul est modifiée

Notez qu'il ne s'agit pas d'une liste complète. Vous pouvez trouver la liste complète ici.

N'oubliez pas que le code de l'événement Worksheet est stocké dans la fenêtre de code objet de la feuille de calcul (dans celle dans laquelle vous souhaitez que l'événement soit déclenché). Il peut y avoir plusieurs feuilles de calcul dans un classeur, et votre code ne serait déclenché que lorsque l'événement a lieu dans la feuille de calcul dans laquelle il est placé.

Jetons maintenant un coup d'œil à quelques événements de feuille de calcul utiles et voyons comment ils peuvent être utilisés dans votre travail quotidien.

Feuille de travail Activer l'événement

Cet événement est déclenché lorsque vous activez une feuille de calcul.

Le code ci-dessous déprotége une feuille dès qu'elle est activée.

Private Sub Worksheet_Activate() ActiveSheet.Unprotect End Sub

Vous pouvez également utiliser cet événement pour vous assurer qu'une cellule spécifique ou une plage de cellules (ou une plage nommée) est sélectionnée dès que vous activez la feuille de calcul. Le code ci-dessous sélectionnerait la cellule D1 dès que vous activez la feuille.

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

Événement de modification de feuille de calcul

Un événement de modification est déclenché chaque fois que vous apportez une modification à la feuille de calcul.

Eh bien… pas toujours.

Certains changements déclenchent l'événement et d'autres non. Voici une liste de certains changements qui ne déclencheront pas l'événement :

• Lorsque vous modifiez la mise en forme de la cellule (taille de police, couleur, bordure, etc.).
• Lorsque vous fusionnez des cellules. C'est surprenant car parfois, la fusion de cellules supprime également le contenu de toutes les cellules, à l'exception de celle en haut à gauche.
• Lorsque vous ajoutez, supprimez ou modifiez un commentaire de cellule.
• Lorsque vous triez une plage de cellules.
• Lorsque vous utilisez la recherche d'objectif.

Les changements suivants déclencheraient l'événement (même si vous pensez peut-être qu'il ne devrait pas) :

• Copier et coller la mise en forme déclencherait l'événement.
• Effacer le formatage déclencherait l'événement.
• L'exécution d'une vérification orthographique déclencherait l'événement.

Ci-dessous, un code afficherait une boîte de message avec l'adresse de la cellule qui a été modifiée.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "Vous venez de changer" & Target.Address End Sub

Bien qu'il s'agisse d'une macro inutile, elle vous montre comment utiliser l'argument Target pour savoir quelles cellules ont été modifiées.

Voyons maintenant quelques exemples plus utiles.

Supposons que vous ayez une plage de cellules (disons A1:D10) et que vous souhaitiez afficher une invite et demander à l'utilisateur s'il souhaitait vraiment modifier une cellule de cette plage ou non, vous pouvez utiliser le code ci-dessous.

Il affiche une invite avec deux boutons - Oui et Non. Si l'utilisateur sélectionne « Oui », le changement est effectué, sinon il est inversé.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox("Vous modifiez les cellules dans A1:D10. Êtes-vous sûr de le vouloir ?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

Dans le code ci-dessus, nous vérifions si la cellule cible se trouve dans les 4 premières colonnes et les 10 premières lignes. Si tel est le cas, la boîte de message s'affiche. De plus, si l'utilisateur a sélectionné Non dans la boîte de message, la modification est annulée (par la commande Application.Undo).

Notez que j'ai utilisé Application.EnableEvents = False avant la ligne Application.Undo. Et puis je l'ai inversé en utilisant Application.EnableEvent = True dans la ligne suivante.

Cela est nécessaire car lorsque l'annulation se produit, cela déclenche également l'événement de modification. Si je ne définis pas EnableEvent sur False, il continuera à déclencher l'événement de changement.

Vous pouvez également surveiller les modifications apportées à une plage nommée à l'aide de l'événement de modification. Par exemple, si vous avez une plage nommée appelée « DataRange » et que vous souhaitez afficher une invite au cas où l'utilisateur apporterait une modification à cette plage nommée, vous pouvez utiliser le code ci-dessous :

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 "Vous venez de modifier la plage de données" End If End Sub

Le code ci-dessus vérifie si la cellule/plage où vous avez apporté les modifications a des cellules communes à la plage de données. Si c'est le cas, il affiche la boîte de message.

Événement de changement de sélection de classeur

L'événement de changement de sélection est déclenché chaque fois qu'il y a un changement de sélection dans la feuille de calcul.

Le code ci-dessous recalculerait la feuille dès que vous modifieriez la sélection.

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

Un autre exemple de cet événement est lorsque vous souhaitez mettre en surbrillance la ligne et la colonne actives de la cellule sélectionnée.

Quelque chose comme indiqué ci-dessous :

Le code suivant peut le faire :

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 Fin du sous-marin

Le code supprime d'abord la couleur d'arrière-plan de toutes les cellules, puis applique celle mentionnée dans le code à la ligne et à la colonne actives.

Et c'est le problème avec ce code. Qu'il enlève la couleur de toutes les cellules.

Si vous souhaitez mettre en surbrillance la ligne/colonne active tout en conservant intacte la couleur des autres cellules, utilisez la technique présentée dans ce didacticiel.

Événement DoubleClick du classeur

C'est l'un de mes événements de feuille de calcul préférés et vous verrez beaucoup de tutoriels où j'ai utilisé cela (comme celui-ci ou celui-ci).

Cet événement est déclenché lorsque vous double-cliquez sur une cellule.

Laissez-moi vous montrer à quel point c'est génial.

Avec le code ci-dessous, vous pouvez double-cliquer sur une cellule et il appliquera une couleur d'arrière-plan, changera la couleur de la police et rendra le texte de la cellule en gras;

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

Cela peut être utile lorsque vous parcourez une liste de cellules et que vous souhaitez en mettre en évidence quelques-unes sélectionnées. Bien que vous puissiez utiliser la touche F4 pour répéter la dernière étape, elle ne pourra appliquer qu'un seul type de formatage. Avec cet événement de double-clic, vous pouvez appliquer les trois avec juste un double-clic.

Notez que dans le code ci-dessus, j'ai défini la valeur Cancel = True.

Ceci est fait pour que l'action par défaut du double-clic soit désactivée - qui consiste à entrer dans le mode d'édition. Avec Annuler = Vrai, Excel ne vous mettrait pas en mode Édition lorsque vous double-cliquez sur la cellule.

Voici un autre exemple.

Si vous avez une liste de tâches dans Excel, vous pouvez utiliser un événement de double-clic pour appliquer le format barré afin de marquer la tâche comme terminée.

Quelque chose comme indiqué ci-dessous :

Voici le code qui fera cela :

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

Notez que dans ce code, j'ai fait un double-clic comme événement bascule. Lorsque vous double-cliquez sur une cellule, il vérifie si le format barré a déjà été appliqué. S'il l'a été, un double-clic supprime le format barré, et s'il ne l'a pas été, le format barré est appliqué.

Événement Excel VBA OnTime

Les événements que nous avons vus jusqu'à présent dans cet article étaient associés à l'un des objets Excel, que ce soit le classeur, la feuille de calcul, la feuille de graphique ou les formulaires utilisateur, etc.

L'événement OnTime est différent des autres événements car il peut être stocké dans le module VBA standard (alors que les autres devaient être placés dans la fenêtre de code d'objets tels que ThisWorkbook ou Worksheets ou UserForms).

Dans le module VBA standard, il est utilisé comme méthode de l'objet d'application.

La raison pour laquelle cela est considéré comme un événement est qu'il peut être déclenché en fonction de l'heure que vous spécifiez. Par exemple, si je veux que la feuille soit recalculée toutes les 5 minutes, je peux utiliser l'événement OnTime pour cela.

Ou, si je veux afficher un message/rappel à une heure précise de la journée, je peux utiliser l'événement OnTime.

Vous trouverez ci-dessous un code qui affichera un message à 14 heures tous les jours.

Sub MessageTime() Application.OnTime TimeValue("14:00:00"), "ShowMessage" End Sub Sub ShowMessage() MsgBox "C'est l'heure du déjeuner" End Sub

N'oubliez pas que vous devez placer ce code dans le module VBA standard,

De plus, bien que l'événement OnTime soit déclenché à l'heure spécifiée, vous devez exécuter la macro manuellement à tout moment. Une fois que vous avez exécuté la macro, elle attendra jusqu'à 14 heures, puis appellera la macro « ShowMessage ».

La macro ShowMessage afficherait alors le message.

L'événement OnTime prend quatre arguments :

Application.OnTime(Le temps le plus tôt, Procédure, Heure la plus récente, Programme)

• Le temps le plus tôt: L'heure à laquelle vous souhaitez exécuter la procédure.
• Procédure: Nom de la procédure à exécuter.
• Heure la plus récente (facultatif): dans le cas où un autre code est en cours d'exécution et que votre code spécifié ne peut pas être exécuté à l'heure spécifiée, vous pouvez spécifier le LatestTime pour lequel il doit attendre. Par exemple, il peut s'agir de EarliestTime + 45 (ce qui signifie qu'il attendra 45 secondes pour que l'autre procédure soit terminée). Si même après 45 secondes la procédure ne peut pas s'exécuter, elle est abandonnée. Si vous ne le spécifiez pas, Excel attendra que le code puisse être exécuté, puis l'exécutera.
• Horaire (facultatif): S'il est défini sur True, il programme une nouvelle procédure horaire. Si False, il annule la procédure précédemment définie. Par défaut, c'est Vrai.

Dans l'exemple ci-dessus, nous n'avons utilisé que les deux premiers arguments.

Regardons un autre exemple.

Le code ci-dessous actualiserait la feuille de calcul toutes les 5 minutes.

Dim NextRefresh as Date Sub RefreshSheet() ThisWorkbook.Worksheets("Sheet1").Calculate NextRefresh = Now + TimeValue("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh() En cas d'erreur Resume Next Application.OnTime NextRefresh, "RefreshSheet", , False End Sub

Le code ci-dessus actualiserait la feuille de calcul toutes les 5 minutes.

Il utilise la fonction Maintenant pour déterminer l'heure actuelle, puis ajoute 5 minutes à l'heure actuelle.

L'événement OnTime continuerait à s'exécuter jusqu'à ce que vous l'arrêtiez. Si vous fermez le classeur et que l'application Excel est toujours en cours d'exécution (d'autres classeurs sont ouverts), le classeur dans lequel l'événement OnTime s'exécute se rouvrira.

Ceci est mieux géré en arrêtant spécifiquement l'événement OnTime.

Dans le code ci-dessus, j'ai le code StopRefresh, mais vous devez l'exécuter pour arrêter l'événement OnTime. Vous pouvez le faire manuellement, l'attribuer à un bouton et le faire en appuyant sur le bouton ou en l'appelant à partir de l'événement Workbook Close.

Private Sub Workbook_BeforeClose (Annuler en tant que booléen) Appeler StopRefresh End Sub

Le code d'événement « BeforeClose » ci-dessus va dans la fenêtre de code ThisWorkbook.

Événement Excel VBA OnKey

Lorsque vous travaillez avec Excel, il continue de surveiller les frappes que vous utilisez. Cela nous permet d'utiliser les frappes comme déclencheur d'un événement.

Avec l'événement OnKey, vous pouvez spécifier une frappe (ou une combinaison de frappes) et le code qui doit être exécuté lorsque cette frappe est utilisée. Lorsque ces touches sont enfoncées, il exécutera le code correspondant.

Tout comme l'événement OnTime, vous devez disposer d'un moyen d'annuler l'événement OnKey. De plus, lorsque vous définissez l'événement OnKey pour une frappe spécifique, il devient disponible dans tous les classeurs ouverts.

Avant de vous montrer un exemple d'utilisation de l'événement OnKey, permettez-moi d'abord de partager les codes clés disponibles dans VBA.

CLÉ	CODE
Retour arrière	{RETOUR ARRIÈRE} ou {BS}
Casser	{PAUSE}
Verrouillage des majuscules	{VERROUILLAGE DES MAJUSCULES}
Supprimer	{DELETE} ou {DEL}
Flèche vers le bas	{VERS LE BAS}
Finir	{FINIR}
Entrer	~
Entrer (sur le clavier numérique)	{ENTRER}
Échapper	{ESCAPE} ou {ESC}
Domicile	{DOMICILE}
Ins	{INSÉRER}
Flèche gauche	{LA GAUCHE}
Verr Num	{NUMLOCK}
Bas de page	{PGDN}
Page précédente	{PGUP}
Flèche droite	{DROITE}
Verrouillage du défilement	{SCROLLOCK}
Languette	{LANGUETTE}
Flèche vers le haut	{EN HAUT}
F1 à F15	{F1} à {F15}

Lorsque vous devez utiliser un événement onkey, vous devez utiliser le code correspondant.

Le tableau ci-dessus contient les codes pour les frappes simples.

Vous pouvez également les combiner avec les codes suivants :

• Changement: + (Signe Plus)
• Contrôler: ^ (Caret)
• Alt : % (Pourcentage)

Par exemple, pour Alt F4, vous devez utiliser le code : "%{F4}” - où % est pour la touche ALT et {F4} est pour la touche F4.

Voyons maintenant un exemple (rappelez-vous que le code des événements OnKey est placé dans le module VBA standard).

Lorsque vous appuyez sur la touche PageUp ou PageDown, il saute 29 lignes au-dessus/au-dessous de la cellule active (du moins c'est ce qu'il fait sur mon ordinateur portable).

Si vous voulez qu'il ne saute que 5 lignes à la fois, vous pouvez utiliser le code ci-dessous :

Sub PageUpDOwnKeys() Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub Sub PageUpMod() On Error Resume Next ActiveCell.Offset(-5, 0).Activate End Sub Sub PageDownMod() En cas d'erreur Resume Next ActiveCell.Offset(5, 0).Activate End Sub

Lorsque vous exécutez la première partie du code, il exécutera les événements OnKey. Une fois cela exécuté, l'utilisation des touches PageUp et PageDown ne ferait que sauter le curseur 5 lignes à la fois.

Notez que nous avons utilisé « On Error Resume Next » pour nous assurer que les erreurs sont ignorées. Ces erreurs peuvent se produire lorsque vous appuyez sur la touche PageUp même lorsque vous êtes en haut de la feuille de calcul. Puisqu'il n'y a plus de lignes à sauter, le code afficherait une erreur. Mais puisque nous avons utilisé « On Error Resume Next », il sera ignoré.

Pour vous assurer que ces événements OnKey sont disponibles, vous devez exécuter la première partie du code. Si vous souhaitez que cela soit disponible dès que vous ouvrez le classeur, vous pouvez le placer dans la fenêtre de code ThisWorkbook.

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

Le code ci-dessous ramènera les touches à leur fonctionnalité normale.

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

Lorsque vous ne spécifiez pas le deuxième argument dans la méthode OnKey, il ramènera la frappe à sa fonctionnalité normale.

Si vous souhaitez annuler la fonctionnalité d'une frappe, afin qu'Excel ne fasse rien lorsque cette frappe est utilisée, vous devez utiliser une chaîne vide comme deuxième argument.

Dans le code ci-dessous, Excel ne ferait rien lorsque nous utilisions les touches PageUp ou PageDown.

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

Désactiver les événements dans VBA

Parfois, vous devrez peut-être désactiver des événements pour que votre code fonctionne correctement.

Par exemple, supposons que j'ai une plage (A1:D10) et que je souhaite afficher un message chaque fois qu'une cellule est modifiée dans cette plage. J'affiche donc une boîte de message et demande à l'utilisateur s'il est sûr de vouloir effectuer le changement. Si la réponse est Oui, le changement est effectué, et si la réponse est Non, alors VBA l'annulera.

Vous pouvez utiliser le code ci-dessous :

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox("Vous modifiez les cellules dans A1:D10. Êtes-vous sûr de le vouloir ?", vbYesNo) End If If Ans = vbNo Then Application.Undo End If End Sub

Le problème avec ce code est que lorsque l'utilisateur sélectionne Non dans la boîte de message, l'action est inversée (comme j'ai utilisé Application.Undo).

Lorsque l'annulation se produit et que la valeur est rétablie à la valeur d'origine, l'événement de modification VBA est à nouveau déclenché et la même boîte de message s'affiche à nouveau.

Cela signifie que vous pouvez continuer à cliquer sur NON dans la boîte de message et qu'il continuera à s'afficher. Cela se produit car vous êtes coincé dans la boucle infinie dans ce cas.

Pour éviter de tels cas, vous devez désactiver les événements afin que l'événement de changement (ou tout autre événement) ne soit pas déclenché.

Le code suivant fonctionnerait bien dans ce cas :

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox("Vous modifiez les cellules dans A1:D10. Êtes-vous sûr de le vouloir ?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

Dans le code ci-dessus, juste au-dessus de la ligne Application.Undo, nous avons utilisé - Application.EnableEvents = False.

La définition de EnableEvents sur False ne déclencherait aucun événement (dans les classeurs actuels ou ouverts).

Une fois que nous avons terminé l'opération d'annulation, nous pouvons rétablir la propriété EnableEvents sur True.

Gardez à l'esprit que la désactivation des événements a un impact sur tous les classeurs actuellement ouverts (ou ouverts alors que EnableEvents est défini sur False). Par exemple, dans le cadre du code, si vous ouvrez un nouveau classeur, l'événement Workbook Open ne fonctionnera pas.

Impact des événements Annuler la pile

Laissez-moi d'abord vous dire ce qu'est une pile d'annulation.

Lorsque vous travaillez dans Excel, il continue de surveiller vos actions. Lorsque vous faites une erreur, vous pouvez toujours utiliser Ctrl + Z pour revenir à l'étape précédente (c'est-à-dire annuler votre action en cours).

Si vous appuyez deux fois sur Ctrl + Z, cela vous ramènera deux étapes en arrière. Ces étapes que vous avez effectuées sont stockées dans le cadre de la pile d'annulation.

Tout événement qui modifie la feuille de calcul détruit cette pile d'annulation.Cela signifie que si j'ai fait 5 choses avant de déclencher un événement, je ne pourrai pas utiliser Contrôle + Z pour revenir à ces étapes précédentes. Le déclenchement de l'événement a détruit cette pile pour moi.

Dans le code ci-dessous, j'utilise VBA pour entrer l'horodatage dans la cellule A1 chaque fois qu'il y a un changement dans la feuille de calcul.

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

Étant donné que je modifie la feuille de calcul, cela détruira la pile d'annulation.

Notez également que cela ne se limite pas aux événements uniquement.

Si vous avez un code stocké dans un module VBA standard et que vous modifiez la feuille de calcul, cela détruirait également la pile d'annulation dans Excel.

Par exemple, le code ci-dessous entre simplement le texte "Bonjour" dans la cellule A1, mais même l'exécuter détruirait la pile d'annulation.

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

Vous aimerez peut-être également les didacticiels Excel VBA suivants :

• Travailler avec des cellules et des plages dans Excel VBA.
• Travailler avec des feuilles de calcul dans Excel VBA.
• Travailler avec des classeurs dans Excel VBA.
• Boucles Excel VBA - Le guide ultime.
• Utilisation de la déclaration IF Then Else dans Excel VBA.
• Pour la prochaine boucle dans Excel.
• Création de fonctions définies par l'utilisateur dans Excel VBA.
• Comment créer et utiliser des compléments dans Excel.
• Créez et réutilisez des macros en les enregistrant dans le classeur de macros personnelles.