Des événements se produisent tout le temps lorsqu'un utilisateur ouvre un classeur Excel et commence à effectuer diverses actions telles que la saisie de données dans des cellules ou le déplacement entre les feuilles
Dans Visual Basic Editor (ALT+F11), des sous-routines sont déjà configurées et peuvent être déclenchées lorsque l'utilisateur fait quelque chose, par ex. entrer des données dans une cellule. La sous-routine ne fournit aucun code d'action, simplement une instruction 'Sub' et une instruction 'End Sub' sans code entre elles. Ils sont effectivement en veille, donc rien ne se passe tant que vous n'avez pas entré un code.
Voici un exemple basé sur l'événement « Modifier » dans une feuille de calcul :
En tant que programmeur VBA, vous pouvez ajouter du code pour que certaines choses se produisent lorsque l'utilisateur effectue une action spécifique. Cela vous donne la possibilité de contrôler l'utilisateur et de l'empêcher de prendre des mesures que vous ne voulez pas qu'il fasse et qui pourraient endommager votre classeur. Par exemple, vous pouvez souhaiter qu'ils enregistrent leur propre copie individuelle du classeur sous un autre nom, afin qu'ils n'affectent pas l'original, qui peut être utilisé par un certain nombre d'utilisateurs.
S'ils ferment le classeur, ils seront automatiquement invités à enregistrer leurs modifications. Cependant, le classeur a un événement « BeforeClose » et vous pouvez entrer du code pour empêcher la fermeture du classeur et le déclenchement d'un événement « Enregistrer ». Vous pouvez ensuite ajouter un bouton à la feuille de calcul elle-même et y mettre votre propre routine « Enregistrer ». Vous pouvez également désactiver la routine « Save » en utilisant l'événement « BeforeSave »
Une compréhension du fonctionnement des événements est absolument essentielle pour un programmeur VBA.
Types d'événements
Cahier d'exercices Événements - ces événements sont déclenchés en fonction de ce que l'utilisateur fait avec le classeur lui-même. Ils incluent des actions utilisateur telles que l'ouverture du classeur, la fermeture du classeur, l'enregistrement du classeur, l'ajout ou la suppression d'une feuille
Événements de feuille de calcul - ces événements sont déclenchés par un utilisateur effectuant des actions sur une feuille de calcul spécifique. Chaque feuille de calcul du classeur a un module de code individuel, qui contient divers événements spécifiquement pour cette feuille de calcul (pas pour toutes les feuilles de calcul). Celles-ci incluent des actions de l'utilisateur telles que la modification du contenu d'une cellule, un double-clic sur une cellule ou un clic droit sur une cellule.
Événements de contrôle Active X - Les contrôles Active X peuvent être ajoutés à une feuille de calcul à l'aide de l'icône « Insérer » sur l'onglet « Développeur » dans le ruban Excel. Il s'agit souvent de boutons de contrôle permettant à l'utilisateur d'effectuer diverses actions sous le contrôle de votre code, mais il peut également s'agir d'objets tels que des listes déroulantes. L'utilisation de contrôles Active X par opposition aux contrôles de formulaire sur la feuille de calcul donne toute une latitude pour la programmabilité. Les contrôles Active X vous offrent beaucoup plus de flexibilité du point de vue de la programmation par rapport à l'utilisation des contrôles de formulaire dans une feuille de calcul.
Par exemple, vous pouvez avoir deux contrôles déroulants sur votre feuille de calcul. Vous voulez que la liste disponible dans la deuxième liste déroulante soit basée sur ce que l'utilisateur a choisi dans la première liste déroulante. En utilisant l'événement « Modifier » dans la première liste déroulante, vous pouvez créer un code pour lire ce que l'utilisateur a sélectionné, puis mettre à jour la deuxième liste déroulante. Vous pouvez également désactiver la deuxième liste déroulante jusqu'à ce que l'utilisateur ait fait une sélection dans la première liste déroulante
Événements de formulaire utilisateur - Vous pouvez insérer et concevoir un formulaire d'aspect professionnel à utiliser comme pop-up. Tous les contrôles que vous placez sur votre formulaire sont des contrôles Active X et ils ont les mêmes événements que les contrôles Active X que vous pouvez placer sur une feuille de calcul
Événements de graphique - Ces événements sont uniquement liés à une feuille graphique et non à un graphique apparaissant dans le cadre d'une feuille de calcul. Ces événements incluent le redimensionnement du graphique ou la sélection du graphique.
Événements d'application - Ceux-ci utilisent l'objet Application dans VBA. Des exemples permettraient au code d'être déclenché lorsqu'une certaine touche est enfoncée ou lorsqu'un certain temps est atteint. Vous pouvez programmer une situation dans laquelle le classeur reste ouvert 24h/24 et 7j/7 et importe les données d'une source externe pendant la nuit à une heure prédéterminée.
Dangers de l'utilisation du code dans les événements
Lorsque vous écrivez du code pour faire quelque chose lorsque l'utilisateur effectue une certaine action, vous devez garder à l'esprit que votre code pourrait déclencher d'autres événements, ce qui pourrait mettre votre code dans une boucle continue.
Par exemple, supposons que vous utilisiez l'événement « Modifier » sur une feuille de calcul de sorte que lorsque l'utilisateur place une valeur dans une cellule, un calcul basé sur cette cellule est placé dans la cellule immédiatement à sa droite.
Le problème ici est que le placement de la valeur calculée dans la cellule déclenche un autre événement « Change », qui à son tour déclenche un autre événement « Change », et ainsi de suite jusqu'à ce que votre code soit à court de colonnes à utiliser et lance un message d'erreur.
Vous devez bien réfléchir lors de l'écriture du code de l'événement pour vous assurer que d'autres événements ne seront pas déclenchés par inadvertance
Désactiver les événements
Vous pouvez utiliser du code pour désactiver les événements afin de contourner ce problème. Ce que vous devrez faire est d'incorporer du code pour désactiver les événements pendant l'exécution de votre code d'événement, puis de réactiver les événements à la fin du code. Voici un exemple de comment procéder :
| 1234 | Sub DisableEvents()Application.EnableEvents = FalseApplication.EnableEvents = TrueFin du sous-marin |
Gardez à l'esprit que cela désactive tous les événements dans l'ensemble de l'application Excel, ce qui affecterait également les autres fonctions d'Excel. Si vous l'utilisez pour une raison quelconque, assurez-vous que les événements sont réactivés par la suite.
Importance des paramètres dans les événements
Les événements ont généralement des paramètres que vous pouvez utiliser pour en savoir plus sur ce que fait l'utilisateur et l'emplacement de la cellule dans laquelle il se trouve.
Par exemple, l'événement Worksheet Change ressemble à ceci :
| 1 | Private Sub Worksheet_Change (ByVal Target As Range) |
En utilisant l'objet de plage, vous pouvez découvrir les coordonnées de ligne/colonne de cellule dans lesquelles se trouve réellement l'utilisateur.
| 1234 | Private Sub Worksheet_Change (ByVal Target As Range)MsgBox Target.ColumnMsgBox Target.RowFin du sous-marin |
Si vous souhaitez que votre code ne fonctionne que sur un certain numéro de colonne ou de ligne, vous ajoutez une condition qui quitte le sous-programme si la colonne n'est pas celle requise.
| 123 | Private Sub Worksheet_Change (ByVal Target As Range)Si Target.Column 2, alors quittez SubFin du sous-marin |
Cela contourne le problème de votre code déclenchant plusieurs événements, car cela ne fonctionnera que si l'utilisateur a modifié une cellule dans la colonne 2 (colonne B)
Exemples d'événements de classeur (non exhaustif)
Les événements du classeur se trouvent sous l'objet « ThisWorkbook » dans l'explorateur de projet VBE. Vous devrez sélectionner « Classeur » dans la première liste déroulante de la fenêtre de code, puis la deuxième liste déroulante vous montrera tous les événements disponibles
Événement d'ouverture de classeur
Cet événement est déclenché chaque fois que le classeur est ouvert par un utilisateur. Vous pouvez l'utiliser pour envoyer un message de bienvenue à un utilisateur en capturant son nom d'utilisateur
| 123 | Sous-classeur privé_Open()MsgBox "Bienvenue" & Application.UserNameFin du sous-marin |
Vous pouvez également vérifier leur nom d'utilisateur par rapport à une liste contenue sur une feuille masquée pour voir s'ils sont autorisés à accéder au classeur. S'il ne s'agit pas d'un utilisateur autorisé, vous pouvez afficher un message et fermer le classeur afin qu'il ne puisse pas l'utiliser.
Événement de nouvelle feuille de classeur
Cet événement est déclenché lorsqu'un utilisateur ajoute une nouvelle feuille au classeur
Vous pouvez utiliser ce code pour vous permettre uniquement d'ajouter une nouvelle feuille, plutôt que d'avoir des utilisateurs différents qui ajoutent tous des feuilles et gâchent le classeur
| 1234567 | Private Sub Workbook_NewSheet (ByVal Sh comme objet)Application.DisplayAlerts = FauxSi Application.Nom d'utilisateur "Richard" AlorsSh.SupprimerFin siApplication.DisplayAlerts = TrueFin du sous-marin |
Notez que vous devez désactiver les alertes car un avertissement utilisateur apparaîtra lorsque la feuille est supprimée, ce qui permet à l'utilisateur de contourner votre code. Assurez-vous de réactiver les alertes par la suite !
Fatigué de rechercher des exemples de code VBA ? Essayez AutoMacro !
Classeur avant l'événement d'enregistrement
Cet événement est déclenché lorsque l'utilisateur clique sur l'icône « Enregistrer », mais avant que « Enregistrer » n'ait réellement lieu
Comme décrit précédemment, vous souhaiterez peut-être empêcher les utilisateurs d'enregistrer leurs modifications dans le classeur d'origine et les forcer à créer une nouvelle version à l'aide d'un bouton sur la feuille de calcul. Tout ce que vous avez à faire est de modifier le paramètre « Annuler » sur True, et le classeur ne peut jamais être enregistré par la méthode conventionnelle.
| 123 | Private Sub Workbook_BeforeSave (ByVal SaveAsUI en tant que booléen, Annuler en tant que booléen)Annuler = VraiFin du sous-marin |
Cahier d'exercices avant la clôture de l'événement
Vous pouvez utiliser cet événement pour empêcher les utilisateurs de fermer le classeur et les forcer à nouveau à quitter via un bouton de feuille de calcul. Encore une fois, vous définissez le paramètre « Annuler » sur « True ». Le X rouge en haut à droite de la fenêtre Excel ne fonctionne plus.
| 123 | Private Sub Workbook_BeforeClose (Annuler en tant que booléen)Annuler = VraiFin du sous-marin |
Exemples d'événements de feuille de travail (non exhaustif)
Les événements de feuille de calcul se trouvent sous l'objet de nom de feuille spécifique dans l'explorateur de projet VBE. Vous devrez sélectionner « Feuille de travail » dans la première liste déroulante de la fenêtre de code, puis la deuxième liste déroulante vous montrera tous les événements disponibles
Événement de modification de feuille de calcul
Cet événement est déclenché lorsqu'un utilisateur apporte une modification à une feuille de calcul, telle que la saisie d'une nouvelle valeur dans une cellule
Vous pouvez utiliser cet événement pour ajouter une valeur ou un commentaire supplémentaire à côté de la cellule modifiée, mais comme indiqué précédemment, vous ne voulez pas commencer à déclencher une boucle d'événements.
| 12345 | Private Sub Worksheet_Change (ByVal Target As Range)Si Target.Column 2, alors quittez SubActiveSheet.Cells(Target.Row, Target.Column + 1). Valeur = _ActiveSheet.Cells(Target.Row, Target.Column). Valeur * 1.1Fin du sous-marin |
Dans cet exemple, le code ne fonctionnera que si la valeur est saisie dans la colonne B (colonne 2). Si cela est vrai, il ajoutera 10% au nombre et le placera dans la prochaine cellule disponible
Feuille de travail avant l'événement Double-Click
Cet événement déclenchera le code si un utilisateur double-clique sur une cellule. Cela peut être extrêmement utile pour les rapports financiers tels qu'un bilan ou un compte de résultat où les chiffres sont susceptibles d'être contestés par les gestionnaires, surtout si le résultat est négatif !
Vous pouvez l'utiliser pour fournir une fonction d'exploration, de sorte que lorsque le responsable conteste un numéro particulier, il lui suffit de double-cliquer sur le numéro et la répartition apparaît dans le rapport.
C'est très impressionnant du point de vue d'un utilisateur, et cela lui évite de demander constamment « pourquoi ce nombre est-il si élevé ? »
Vous devrez écrire du code pour connaître l'en-tête/les critères du numéro (à l'aide des propriétés de l'objet cible), puis filtrer les données tabulaires, puis les copier dans le rapport.
Programmation VBA | Le générateur de code fonctionne pour vous !
Feuille de travail Activer l'événement
Cet événement se produit lorsque l'utilisateur passe d'une feuille à une autre. Il s'applique à la nouvelle feuille vers laquelle l'utilisateur se déplace.
Il peut être utilisé pour s'assurer que la nouvelle feuille est complètement calculée avant que l'utilisateur ne commence à faire quoi que ce soit dessus. Il peut également être utilisé pour recalculer uniquement cette feuille particulière sans recalculer le classeur entier. Si le classeur est volumineux et contient une formule compliquée, le recalcul d'une feuille permet de gagner beaucoup de temps
| 123 | Sous-feuille de travail privée_Activer()ActiveSheet.CalculerFin du sous-marin |
Événements de contrôle Active X (non exhaustif)
Comme indiqué précédemment, vous pouvez ajouter des contrôles Active X directement sur une feuille de calcul. Il peut s'agir de boutons de commande, de listes déroulantes et de zones de liste
Les événements Active X se trouvent sous l'objet de nom de feuille spécifique (où vous avez ajouté le contrôle) dans l'explorateur de projet VBE. Vous devrez sélectionner le nom du contrôle Active X dans la première liste déroulante de la fenêtre de code, puis la deuxième liste déroulante vous montrera tous les événements disponibles
Événement de clic sur le bouton de commande
Lorsque vous avez placé un bouton de commande sur une feuille de calcul, vous souhaiterez qu'il agisse. Pour ce faire, placez du code sur l'événement Click.
Vous pouvez facilement mettre un message « Êtes-vous sûr ? » sur celui-ci afin qu'une vérification soit effectuée avant l'exécution de votre code.
| 12345 | Private Sub CommandButton1_Click ()Dim ButtonRet As VariantButtonRet = MsgBox("Êtes-vous sûr de vouloir faire cela ?", vbQuestion Ou vbYesNo)Si ButtonRet = vbNo alors quittez SubFin du sous-marin |
Événement de changement de liste déroulante (zone de liste déroulante)
Une liste déroulante Active X a un événement de modification, de sorte que si un utilisateur sélectionne un élément particulier dans la liste déroulante, vous pouvez capturer son choix à l'aide de cet événement, puis écrire du code pour adapter d'autres parties de la feuille ou du classeur en conséquence.
| 123 | Private Sub ComboBox1_Change ()MsgBox "Vous avez sélectionné " & ComboBox1.TextFin du sous-marin |
Programmation VBA | Le générateur de code fonctionne pour vous !
Cochez la case (case à cocher) Cliquez sur l'événement
Vous pouvez ajouter une coche ou une case à cocher à une feuille de calcul afin de fournir des choix d'options à l'utilisateur. Vous pouvez utiliser l'événement click dessus pour voir si l'utilisateur a changé quelque chose à ce sujet. Les valeurs retournées sont True ou False selon qu'elle a été cochée ou non.
| 123 | Private Sub CheckBox1_Click ()MsgBox CheckBox1.ValueFin du sous-marin |
Événements UserForm (non exhaustif)
Excel vous permet de créer vos propres formulaires. Ceux-ci peuvent être très utiles à utiliser en tant que fenêtres contextuelles pour collecter des informations ou pour proposer plusieurs choix à l'utilisateur. Ils utilisent des contrôles Active X comme décrit précédemment et ont exactement les mêmes événements, bien que les événements dépendent beaucoup du type de contrôle.
Voici un exemple de formulaire simple :
Quand il est affiché, voici à quoi cela ressemble à l'écran
Vous utiliseriez des événements sur le formulaire pour faire des choses comme entrer un nom de société par défaut lorsque le formulaire est ouvert, pour vérifier que le nom de la société saisi correspond à celui déjà présent dans la feuille de calcul et n'a pas été mal orthographié, et pour ajouter du code au clic événements sur les boutons 'OK' et 'Annuler'
Le code et les événements derrière le formulaire peuvent être consultés en double-cliquant n'importe où sur le formulaire
La première liste déroulante donne accès à tous les contrôles du formulaire. Le deuxième menu déroulant donnera accès aux événements
Événement d'activation du formulaire utilisateur
Cet événement est déclenché lors de l'activation du formulaire, normalement lors de son affichage. Cet événement peut être utilisé pour définir des valeurs par défaut, par ex. un nom de société par défaut dans la zone de texte du nom de la société
| 123 | Private Sub UserForm_Activate()TextBox1.Text = "Mon nom d'entreprise"Fin du sous-marin |
Programmation VBA | Le générateur de code fonctionne pour vous !
Modifier l'événement
La plupart des contrôles du formulaire ont un événement de modification, mais dans cet exemple, la zone de texte du nom de l'entreprise peut utiliser l'événement pour restreindre la longueur du nom de l'entreprise saisi.
| 123456 | Private Sub TextBox1_Change ()Si Len (TextBox1.Text) > 20 AlorsMsgBox "Le nom est limité à 20 caractères", vbCriticalTextBox1.Text = ""Fin siFin du sous-marin |
Cliquez sur l'événement
Vous pouvez utiliser cet événement pour prendre des mesures à partir de l'utilisateur en cliquant sur les contrôles du formulaire, ou même le formulaire lui-même
Sur ce formulaire, il y a un bouton "OK", et après avoir collecté le nom d'une entreprise, nous voudrions le placer dans une cellule de la feuille de calcul pour référence future
| 1234 | Private Sub CommandButton1_Click ()ActiveSheet.Range("A1"). Valeur = TextBox1.TextMoi.CacherFin du sous-marin |
Ce code agit lorsque l'utilisateur clique sur le bouton « OK ». Il place la valeur dans la zone de saisie du nom de l'entreprise dans la cellule A1 de la feuille active, puis masque le formulaire afin que le contrôle utilisateur soit renvoyé à la feuille de calcul.
Événements de graphique
Les événements de graphique ne fonctionnent que sur les graphiques qui se trouvent sur une feuille de graphique distincte, et non sur un graphique intégré à une feuille de calcul standard
Les événements de graphique sont quelque peu limités et ne peuvent pas être utilisés sur une feuille de calcul où vous pouvez très bien avoir plusieurs graphiques. De plus, les utilisateurs ne souhaitent pas nécessairement passer d'une feuille de calcul contenant des nombres à une feuille de graphique - il n'y a pas d'impact visuel immédiat ici
L'événement le plus utile serait de découvrir le composant d'un graphique sur lequel un utilisateur a cliqué, par ex. un segment dans un graphique à secteurs ou une barre dans un graphique à barres, mais ce n'est pas un événement disponible sur la plage standard d'événements.
Ce problème peut être résolu en utilisant un module de classe pour ajouter un événement « Mouse Down » qui renverra les détails du composant graphique sur lequel l'utilisateur a cliqué. Ceci est utilisé sur un graphique dans une feuille de calcul.
Cela implique un codage très compliqué, mais les résultats sont spectaculaires. Vous pouvez créer des drill downs, par ex. l'utilisateur clique sur un segment de camembert et instantanément ce graphique est masqué et un deuxième graphique apparaît à sa place montrant un camembert détaillé pour le segment d'origine, ou vous pouvez produire les données tabulaires prenant en charge ce segment du camembert.
Événements d'application
Vous pouvez utiliser l'objet Application dans VBA pour déclencher le code en fonction d'un événement particulier
Programmation VBA | Le générateur de code fonctionne pour vous !
Application.OnTime
Cela peut vous permettre de lancer un morceau de code à intervalles réguliers tant que le classeur est chargé dans Excel. Vous souhaiterez peut-être enregistrer automatiquement votre classeur dans un dossier différent toutes les 10 minutes ou laisser la feuille de calcul s'exécuter pendant la nuit afin d'importer les dernières données d'une source externe.
Dans cet exemple, une sous-routine est entrée dans un module. Il affiche une boîte de message toutes les 5 minutes, bien que cela puisse facilement être une autre procédure codée. En même temps, il réinitialise la minuterie à l'heure actuelle plus 5 minutes supplémentaires.
Chaque fois qu'il s'exécute, la minuterie se réinitialise pour exécuter la même sous-routine dans un autre temps de 5 minutes.
| 1234 | Sous-TestOnTime()MsgBox "Test OnTime"Application.OnTime (Now () + TimeValue("00:05:00")), "TestOnTime"Fin du sous-marin |
Application.OnKey
Cette fonction vous permet de concevoir vos propres raccourcis clavier. Vous pouvez faire de n'importe quelle combinaison de touches une sous-routine de votre création.
Dans cet exemple, la lettre « a » est redirigée de sorte qu'au lieu de placer un « a » dans une cellule, elle affiche une boîte de message. Ce code doit être placé dans un module inséré.
| 123456 | Sous TestKeyPress()Application.OnKey "a", "TestKeyPress"Fin du sous-marinSous TestKeyPress()MsgBox "Vous avez appuyé sur 'a'"Fin du sous-marin |
Vous exécutez tout d'abord la sous-routine « TestKeyPress ». Vous n'avez besoin de l'exécuter qu'une seule fois. Il indique à Excel que chaque fois que la lettre « a » est enfoncée, il appellera la sous-routine « TestKeyPress ». La sous-routine « TestKeyPress » affiche simplement une boîte de message pour vous dire que vous avez appuyé sur la touche « a ». Il peut bien sûr charger un formulaire ou faire toutes sortes d'autres choses.
Vous pouvez utiliser n'importe quelle combinaison de touches que vous pouvez utiliser avec la fonction « SendKeys »
Pour annuler cette fonctionnalité, vous exécutez l'instruction « OnKey » sans le paramètre « Procedure ».
| 123 | Sous CancelOnKey()Application.OnKey "a"Fin du sous-marin |
Tout est maintenant revenu à la normale.
