Comment lancer des macros automatiquement avec les évènements VBA : Les objets Workbook [#31 FORMATION EXCEL VBA COMPLETE]

Pour ce nouveau chapitre de notre formation dédiée à l’apprentissage du développement en VBA pour les débutants, je vais vous montrer automatiser le lancement de macro-commandes VBA en utilisant les évènements.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Partie 1 : Les evenements de l'objet Classeur (Workbook)

Partie 2 : Les evenements de l'objet Feuille de calcul (Worksheet)

Partie 3 : Les evenements Personnalisés

1. Qu’est-ce qu’un évènement ?

La notion que nous allons découvrir dans ce chapitre porte particulièrement bien son nom : en effet, un évènement consiste à déclencher une action automatiquement lorsque quelque chose de particulier (un évènement) se déroule sur un classeur ou une feuille de calcul Excel.

Cela sera par exemple le cas lorsque nous ouvrons un fichier Excel, lorsque nous sélectionnons une cellule, lorsque nous modifions une cellule, lorsque nous enregistrons un classeur, …

Il existe trois grands types d’évènements :

• Les évènements propres à un classeur,
• Les évènements propres à une feuille de calcul,
• Les évènements propres à une classe personnalisée

2. Les évènements propres à un classeurs

Pour commencer, découvrons les évènements propres à un classeur Excel.

Ces derniers dépendent directement de l’objet VBA qui le défini (à savoir l’objet Workbook) et seront donc à placer dans le module Workbook.

Attention, si l’évènement est saisi à un autre endroit, celui-ci ne fonctionnera tout simplement pas…

La première chose à faire consiste donc à double-cliquer sur le module correspondant pour entrer à l’intérieur de celui-ci :

Pour saisir un évènement dans le module du classeur, nous disposons de deux solutions.

Soit, nous connaissons EXACTEMENT le nom de la procédure évènementielle (j’insiste sur le terme « exactement », il faut en effet connaitre son nom précisément, mais également les arguments qui composent cette procédure… ce qui même après des années de pratique ne sera que très rarement le cas !) et dans ce cas, il suffit de saisir la procédure comme nous le ferions pour n’importe quelle autre procédure :

Private Sub Workbook_SheetChange(ByVal Sh  As Object, ByVal Target As Range)
 
End Sub

Soit nous ne connaissons pas exactement le nom de la procédure, ou alors que nous souhaitons économiser un petit peu de temps, et dans ce cas nous pouvons demander à VBE de l’insérer automatiquement à notre place.

Il est en effet possible d’obtenir directement la liste des évènements disponibles, et pour cela nous commençons par dérouler le menu de sélection des objets, situé au-dessus de la zone de saisie de code :

La seule option disponible (Workbook) nous permet justement de pouvoir choisir un évènement relié au classeur.

Nous le sélectionnons, ce qui nous permet ensuite dans le second menu (le menu de sélection des procédures) de récupérer directement l’ensemble des évènements disponibles :

Il suffit alors de sélectionner l’un des évènements de cette liste pour que VBE l’insère automatiquement dans le module sélectionné.

Au passage, vous pouvez alors constater que cette liste est assez volumineuse, et le nombre d’évènements que nous allons pouvoir mettre en place très important !

Bien entendu, nous n’allons pas pouvoir étudier chacun d’entre eux, nous allons nous limiter aux principaux évènements.

   2.1. Lancer une commande à l’ouverture du classeur

L’un des évènements les plus utilisés sera l’évènement Workbook_Open, lequel permet en effet de lancer une ou plusieurs actions directement à l’ouverture du classeur !

Nous pouvons ainsi imaginer plusieurs usages de cet évènement tels que :

• Afficher un message souhaitant la bienvenue à l’utilisateur,
• Contrôler si une échéance est dépassée, et si nécessaire afficher un message de rappel directement au lancement du fichier de travail,
• Actualiser automatiquement des données externes (cours de bourse en ligne, météo du jour, …), ou vérifier l’existence de données plus récentes
• Verrouiller toutes les feuilles du classeur
• …

L’utilisation que nous pourrions avoir de cet évènement dépendra évidemment de l’usage de chacun.

Mais dans tous les cas, j’en profite pour vous rappeler qu’il ne faut surtout pas que la sécurité et le bon fonctionnement dépende uniquement des évènements de VBA, car il sera toujours possible de lancer le classeur Excel en désactivant l’utilisation des macro-commandes… C’est pourquoi d’une manière générale, il sera toujours préférable de fonctionner dans l’autre sens : verrouiller les données à la fermeture du classeur (en utilisant l’évènement que nous découvrirons juste après), et que Workbook_Open ne permette au contraire que de rendre ces données accessibles. De cette manière si les macros sont désactivées, l’accès aux données sera déjà rendu plus complexe (garder quand même à l’esprit qu’aucune protection n’est absolue !)

Ici nous allons effectuer deux actions à l’ouverture du classeur :

• Protéger la feuille de calcul,
• Souhaiter la bienvenue à l’utilisateur

En principe, lorsque nous avons sélectionné l’option Workbook depuis le menu de sélection des objets, VBE aurait dû insérer directement l’objet Workbook_Open dans le module :

Si ce n’est pas le cas, il suffit alors de le sélectionner directement depuis le menu de sélection de Procédure pour que VBE l’insère automatiquement :

Ensuite, nous insérons les deux lignes suivantes :

Private Sub Workbook_Open()
    Feuil1.Protect
    MsgBox "Bonjour et bienvenue Alex"
End Sub

Pour tester, il suffit simplement de relancer le classeur, après l’avoir bien évidemment enregistré !

À ce moment-là, Excel nous affichera immédiatement le message voulu :

Et il ne sera pas possible non plus de modifier les cellules de la feuille Feuil1 :

   2.2. Lancer une commande à la fermeture du classeur

À l’inverse de ce que nous venons de voir, il est également possible d’exécuter une commande juste avant que le classeur Excel ne se ferme.

Pour cela, nous utiliserons la procédure événementielle Workbook_BeforeClose :

Private Sub Workbook_BeforeClose(Cancel  As Boolean)
    MsgBox "Au revoir !"
End Sub

Comme vous pouvez le constater, cette procédure événementielle dispose d’une variable Cancel, qui est un booléen qui permet, lorsqu’il est égal à True d’annuler la fermeture du classeur :

Private Sub Workbook_BeforeClose(Cancel  As Boolean)
    MsgBox "Interdit de fermer !"
    Cancel = True
End Sub

Cette procédure affiche un message et empêche le classeur de se fermer !

La valeur par défaut de Cancel est False, ce qui permet au classeur de se fermer correctement.

Bien entendu, avant de fermer le classeur, pensez à repasser la valeur de la variable Cancel à False ;)

   2.3. Lancer une commande lorsque le classeur est activé

Maintenant que nous venons de découvrir les évènements instanciés lorsque le classeur est ouvert ou fermé, nous allons voir comment exécuter une commande lorsque celui-ci est activé.

Comprenez par que nous allons revenir sur le classeur après avoir été en consulter un autre classeur Excel :

Pour créer un exemple très simple, nous allons par exemple afficher un message à chaque fois que le classeur sera activé :

Private Sub Workbook_Activate()
    MsgBox "Rebonjour :)"
End Sub

À l’inverse nous allons également pouvoir lancer une macro lorsque le classeur va être désactivé, c’est-à-dire que nous allons le laisser en tâche de fond pour aller consulter un autre document Excel :

   2.4. Les autres évènements de l’objet Workbook

Parmi les évènements notables de l’objet Workbook, nous pouvons citer :

BeforeSave / AfterSave : Déclenche une action donnée avant ou après l’enregistrement du classeur.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI  As Boolean, Cancel As Boolean)
    MsgBox "Début d'enregistrement..."
End Sub

Private Sub Workbook_AfterSave(ByVal  Success As Boolean)
    MsgBox "... fin d'enregistrement"
End Sub

Notez que la variable SaveAsUI de la procédure Workbook_BeforeSave est un booléen qui permet de savoir si l’utilisateur a demandé d’enregistrer le classeur en utilisant la fonctionnalité Enregistrer sous, afin de créer une nouvelle version du fichier.

BeforePrint : Déclenche une action donnée avant de lancer une impression

Private Sub Workbook_BeforePrint(Cancel  As Boolean)
    If MsgBox("Etes-vous certain de vouloir imprimer le  fichier ?", vbYesNo) = vbNo Then
        Cancel = True
        MsgBox "La page ne sera pas imprimée !"
    End If
End Sub

Cet exemple permet de demander la confirmation avant d’imprimer la feuille.

Ici encore, la variable Cancel permet d’annuler l’édition en cas de besoin.

NewSheet : Déclenche une action donnée lorsqu’une nouvelle feuille de calcul est insérée dans le classeur

Private Sub Workbook_NewSheet(ByVal Sh  As Object)
    Sh.Name = InputBox("Comment voulez-vous  nommer la nouvelle feuille ?", "Nom de la feuille", Sh.Name)
End Sub

Cet exemple permet de renommer automatiquement la feuille lors de sa création.

L’objet représentant la nouvelle feuille est renvoyé au travers de la variable Sh, ce qui permet de manipuler facilement cette dernière.

SheetActivate : Déclenche une action lorsqu’une des feuilles du classeur est activée (c’est-à-dire lors d’un changement de feuille).

Private Sub Workbook_SheetActivate(ByVal  Sh As Object)
    MsgBox "La feuille " & Sh.Name  & " est activée !"
End Sub

Cet argument est à différencier de Worksheet_Activate :

• Workbook_SheetActivate : se déclenche à chaque fois que l’utilisateur change de feuille de calcul,
• Worksheet_Activate : se déclenche à chaque fois qu’une feuille de calcul bien particulière (la feuille dans laquelle l’évènement est déclaré) est activée

Et voilà, maintenant que nous avons eu l’occasion de faire le tour des différents types d’évènements qu’il est possible d’utiliser en tant qu’objet d’un classeur, je vous propose de découvrir  présent les évènements propres aux feuilles de calculs.

3. Les évènements propres aux feuilles de calculs

La semaine dernière, nous avons abordé une notion essentielle à maîtriser dans le cadre du développement d’applications en VBA : la notion d’évènement.

Nous avions alors vu qu’il était tout à fait possible de lancer automatiquement une macro-commande VBA directement en fonction du contexte d’utilisation du fichier de travail et non plus à la demande exclusive de l’utilisateur.

Au travers de nombreux exemple, nous avons automatisé le fonctionnement d’un classeur Excel afin de :

• Automatiser l’exécution de commandes à l’ouverture, ou au contraire, à la fermeture d’un fichier Excel,
• Lors de son enregistrement,
• Lors de son impression, …

Aujourd’hui, nous allons découvrir une nouvelle famille d’évènements, qui vont répondre cette fois-ci en fonction des actions réalisées directement sur la feuille de calcul, c’est-à-dire en fonction des interactions mises-en-place sur les cellules, sur les objets, …

Ce type d’évènement dépendant directement d’une feuille de calcul en particulier, il est à venir saisir dans le module correspondant à cette dernière :

De la même manière que nous l’avons déjà vu pour les évènements liés au classeur, nous retrouvons la liste des évènements de feuilles de calcul depuis le menu déroulant situé au-dessus du module de code (après avoir sélectionné Worksheet depuis le premier menu) :

   3.1. Afficher la feuille de calcul

Pour commencer voyons l’évènement Worksheet_Activate que nous avons déjà abordé dans la partie précédente :

Celui-ci permet de lancer une commande lorsque la feuille de calcul est affichée.

Contrairement à Workbook_Activate nous pouvons ici clairement depuis quelle page la commande doit-être activée, il s’agira en effet uniquement de la feuille active !

Pour l’exemple, nous allons par exemple souhaiter insérer un compteur dénombrant le nombre de fois que la feuille a été affichée :

Private Sub Worksheet_Activate()
    [b6] = [b6] + 1
End Sub

Il suffit alors de changer de feuille, puis de revenir pour que le compteur s’affiche dans la cellule B6 :

Petite précision, Worksheet_Activate ne sera instancié que lorsque nous allons consulter une autre feuille du classeur actif ! Si nous allons consulter un autre classeur, la feuille concernée restera en effet toujours active sur le premier classeur.

À l’inverse, nous retrouvons également l’évènement Worksheet_Deactivate, qui permet d’effectuer l’opération inverse : c’est-à-dire de lancer une série de commande, lorsque nous allons visiter une autre feuille du classeur.

   3.2. Sélectionner une cellule

Ensuite, nous pouvons utiliser l’évènement Worksheet_SelectionChange pour lancer une commande lorsque nous allons sélectionner une cellule

Pour continuer sur notre exemple, nous pouvons par exemple de demander à Excel de compter le nombre de cellules sélectionnées en utilisant la même méthode que celle que nous venons de découvrir dans l’exemple précédent :

Private Sub Worksheet_SelectionChange(ByVal  Target As Range)
    [b7] = [b7] + 1
End Sub

Nous pouvons encore aller un cran plus loin en utilisant la variable Target fournie par l’évènement Worksheet_SelectionChange, au sein de laquelle sont insérées les coordonnées de la cellule sélectionnée, ou de la plage de l’ensemble des cellules sélectionnées.

En effet, dans cet exemple, si nous sélectionnons plusieurs cellules, le compteur n’en dénombrera à chaque fois qu’une seule :

Il suffit alors d’incrémenter le compteur du nombre de cellules sélectionnées en utilisant simplement la propriété Count de Target :

    [b7] = [b7] + Target.Count

   3.3. Modifier le contenu d’une cellule

Pour déclencher une action lors de la modification d’une cellule, nous avons à disposition l’évènement Worksheet_Change, qui fonctionne un peu comme l’évènement que nous venons de voir à l’instant :

Pour cet exemple, nous allons par exemple souhaiter que l’utilisateur ne puisse saisir que des valeurs numériques :

Private Sub Worksheet_Change(ByVal Target  As Range)
    If Not IsNumeric(Target) Then
        MsgBox "Veuillez saisir une valeur  numérique"
        Target.ClearContents
    End If
End Sub

Si l’utilisateur saisie autre chose qu’une valeur numérique, un message va alors l’informer de son erreur et supprimer le contenu saisi :

Bien entendu, il peut être intéressant de limiter l’action de cet évènement à certaines cellules uniquement !

Par exemple les cellules se trouvant aux coordonnées B8 jusqu’à E11

Pour cela, nous modifions le test conditionnel afin que celui vérifie en plus que la cellule renvoyée par Target se trouve bien dans la plage des cellules B8 à E11 :

    If Not IsNumeric(Target) And Not Intersect(Target,  [b8:e11]) Is Nothing Then
        MsgBox "Veuillez saisir une valeur  numérique"
        Target.ClearContents
    End If

Il est ainsi impossible de saisir des valeurs non numériques dans la plage des cellules encadrées, par contre il n’y a aucun problème en dehors de ce cadre.

Comme vous avez pu le constater, pour vérifier que la cellule Target appartient bien à la plage des cellules B8 à E11, nous avons utilisé la fonction Intersect.

Il s’agit d’une fonction VBA qui permet de retourner l’ensemble des cellules communes aux plages saisies en tant qu’argument.

Lorsqu’aucune cellule ne se trouve communes à ces plages, la fonction retourne la valeur Nothing, et nous souhaitons justement contrôler que le résultat est bien différent de Nothing avec le mot clé Not.

   3.4. Cliquer sur une cellule (double clic ou clic-droit)

Pour déclencher une action lorsque l’utilisateur clic sur une feuille de calcul, nous avons deux évènements possibles :

• Worksheet_BeforeDoubleClick : Déclencher une action avant d’effectuer le résultat attendu par le double clic,
• Worksheet_BeforeRightClick : Déclencher une action avant d’effectuer le résultat attendu par le clic droit

Ces deux évènements fonctionnent à peu près de la même manière :

Nous y retrouvons en effet les deux mêmes arguments :

• Target : variable dans laquelle nous retrouvons la cellule sélectionnée par le double clic ou le clic droit,
• Cancel : il s’agit d’une variable booléenne qui permet d’annuler l’action (le double clic ou le clic-droit) lorsque celle-ci est égale à True

Pour l’exemple, nous souhaitons capturer les clics doits afin d’afficher la valeur dans une notification tout en désactivant l’affichage du menu contextuel :

Private Sub Worksheet_BeforeRightClick(ByVal Target  As Range, Cancel As Boolean)
    MsgBox Target
    Cancel = True
End Sub

   3.5. Les autres évènements

Les autres évènements disponibles :

• Worksheet_Calculate : Déclencher une action lors d’un calcul sur la feuille
• Worksheet_BeforeDelete : Déclencher une action avant que la feuille de calcul ne soit supprimée
• Worksheet_FollowHyperlink : Déclencher une action lors d’un clic sur un lien hypertexte
• Worksheet_PivotTableUpdate : Déclencher une action lors de l’actualisation d’un tableau croisé dynamique

4. Découvrons maintenant comment créer nos propres évènements !

Excel nous offre en effet la possibilité de pouvoir créer nos propres évènements afin de pouvoir lancer automatiquement des actions données lorsque quelque se passe au sein de notre code.

Pour cela, nous allons revenir sur la notion de classe personnalisée que nous avons déjà eu l’occasion de découvrir dans le détail dans le chapitre dédié que vous pouvez bien entendu consulter pour en savoir davantage.

Pour rappel, la classe personnalisée permet de créer un type évolué, à partir duquel nous allons pouvoir créer un objet avec des méthodes et des propriétés.

Je vous propose de créer un exemple très simple : nous allons simplement revenir sur l’un des exemples vus un peu plus tôt dans ce chapitre, à savoir le compteur de sélection de cellules dans la feuille de calcul.

Private Sub Worksheet_SelectionChange(ByVal  Target As Range)
    [b7] = [b7] + Target.Count
    MsgBox "Vous avez cliqué sur la cellule "  & Target.Address
End Sub

Nous allons légèrement modifier le code de cette procédure pour utiliser à la place un objet qui va prendre la responsabilité du compte des clics.

Pour créer notre classe personnalisée, nous devons tout d’abord créer un module de classe, qui s’insère automatiquement depuis le menu Insertion :

Ensuite nous le renommons en cCompteurCellules, depuis la fenêtre de propriétés :

À partir de là, nous pouvons créer la classe personnalisée, au sein de laquelle nous n’allons créer qu’une seule propriété pCellule qui va stocker la cellule sélectionnée par l’utilisateur :

Private pCellule As Range
 
Property Get Cellule() As Range
    Set Cellule = pCellule
End Property
 
Property Set Cellule(rCellule As  Range)
    Set pCellule = rCellule
End Property

Comme vous pouvez le constater, ici nous utilisons l’assesseur Set pour affecter la cellule à la propriété Cellule, car cette dernière n’est pas une simple variable comme nous avons pu le voir dans le chapitre dédié à la découverte des classes personnalisées, mais il s’agit ici d’un objet Range.

Ensuite, c’est au sein de cet assesseur Set que nous allons créer la gestion de l’évènement à proprement parler :

• Incrémenter la cellule B7 d’une unité,
• Afficher un message pour préciser l’adresse de la cellule sélectionnée

Property Set Cellule(rCellule As  Range)
    Set pCellule = rCellule
    
    [b7] = [b7] + 1
    MsgBox "Vous avez cliqué sur la cellule "  & pCellule.Address
End Property

De retour dans l’évènement Worksheet_SelectionChange, il ne nous reste plus qu’à remplacer les lignes car la déclaration d’un objet cCompteurCellules, puis par l’affectation de la cellule (ou des cellules si plusieurs ont été sélectionnées) en tant que propriété Cellule :

Private Sub Worksheet_SelectionChange(ByVal  Target As Range)
    Dim compteur As New cCompteurCellules
    Set compteur.Cellule = Target
End Sub

Nous pouvons maintenant tester et constater que l’évènement Worksheet_SelectionChange va réagir exactement de la même manière qu’avant les modifications :

En revanche, maintenant nous allons pouvoir créer des évènements personnalisés directement depuis la classe personnalisée !

À titre d’exemple, imaginons que nous souhaitions créer un évènement qui va se déclencher dès que l’utilisateur sélectionne plusieurs cellules.

Pour cela, nous commençons par déclarer l’évènement en tout début de classe personnalisée (ou en dessous de l’éventuel Option Explicit) en utilisant le mot-clé Event :

Public Event SelectionDePlusieursCellules()

Ne reste plus qu’à insérer le déclencheur dans le code en utilisant l’instruction RaiseEvent :

Property Set Cellule(rCellule As  Range)
    Set pCellule = rCellule
    
    If rCellule.Count > 1 Then RaiseEvent  SelectionDePlusieursCellules
    
    [b7] = [b7] + 1
    MsgBox "Vous avez cliqué sur la cellule "  & pCellule.Address
End Property

Nous utilisons ici la propriété Count de l’objet Range rCellule pour déterminer le nombre de cellules sélectionnées.

Lorsque ce nombre est supérieur à 1, nous pouvons lancer l’évènement SelectionDePluisieursCellules.

Pour cela revenons dans le module de la feuille de calcul pour modifier la manière dont nous avons instancié l’objet compteur, dans l’évènement Worksheet_SelectionChange.

Désormais, nous allons l’instancier tout en haut du module en utilisant l’attribut WithEvents :

Option Explicit
 
Dim WithEvents compteur As cCompteurCellules

Ce qui nous permet d’obtenir l’objet compteur directement dans le menu de sélection des objets :

Ainsi que l’évènement correspondant depuis le menu Procédure :

Nous pouvons alors cliquer sur celui-ci pour gérer les actions à effectuer, comme par exemple interdire la sélection de plusieurs cellules :

Private Sub compteur_SelectionDePlusieursCellules()
    MsgBox "Sélection de plusieurs cellules interdites  !"
    ActiveCell.Select
End Sub

Avant de pouvoir tester, il ne nous reste plus qu’à insérer la ligne suivante dans l’évènement Worksheet_SelectionChange :

Private Sub Worksheet_SelectionChange(ByVal  Target As Range)
    Set compteur = New cCompteurCellules
    Set compteur.Cellule = Target
End Sub

Ce qui permet maintenant d’empêcher la sélection de plusieurs cellules de manière automatisé et indépendante des autres évènements :

À partir de là, nous pouvons créer toutes sortes d’évènements en suivant la même logique.

Il suffit en effet de les instancier dans la classe personnalisée :

Public Event CompteurEstPair()
Public Event CompteurEstImpair()

Puis de définir les modalités de lancement :

Property Set Cellule(rCellule As  Range)
    …
    If rCellule.Count > 1 Then RaiseEvent  SelectionDePlusieursCellules
    If [b7] Mod 2 = 0 Then RaiseEvent CompteurEstPair Else  RaiseEvent CompteurEstImpair
    
End Property

Nous retrouvons alors chacun d’entre eux dans le menu de sélection des procédures :

Bien entendu, les évènements sont maintenant disponibles mais libre à nous de les utiliser ou non en fonction de besoins du code.

Nous pouvons par exemple choisir de colorer le fond de la cellule lorsque le compteur de sélection est pair, mais ne rien faire lorsque celui-ci est impair :

Private Sub compteur_CompteurEstPair()
    ActiveCell.Interior.ColorIndex = 5
End Sub

Sachez qu’il existe encore d’autres types d’évènements gérés par VBA, que nous pouvons utiliser régulièrement sans même nous en rendre compte, comme par exemple les évènements liés à un formulaire :

Mais maintenant que nous avons bien compris le principe, je pense qu’il n’est pas utile de les détailler en profondeur, leurs noms sont déjà suffisamment explicites (Initialize, Click, DblClick, …)

5. Désactiver les évènements

Pour finir, sachez qu’il est possible de désactiver temporairement les évènements en utilisant la propriété EnableEvents de l’objet Application à False :

Application.EnableEvents = False 

Attention de réactiver la gestion des évènements en réaffectant la valeur True à celle-ci, sinon aucun évènement ne sera plus en mesure de fonctionner ! :

Private Sub Worksheet_SelectionChange(ByVal  Target As Range)
    Application.EnableEvents = False
    
    Set compteur = New cCompteurCellules
    Set compteur.Cellule = Target
    
    Application.EnableEvents = True
End Sub

excelformation.fr, formation excel, didacticiel excel, excel, tuto, cours, gratuit, formation, débutant, apprendre, tuto excel, apprendre excel, tutoriel, évènements, classe personnalisée, classe, personnalisée, objet, module de classe, module, set, range, évènements personnalisés, personnalisés, évènement, event, module de la feuille, feuille, procédures, vba, désactiver les évènements, désactiver