Annuler une action ou une modification en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Annuler ou empêcher une modification

VBA Excel offre de nombreux événements. Certains permettent même de réagir en fonction des actions de l'utilisateur sur la feuille. Nous allons en profiter dans ce nouveau sujet pour intercepter une saisie lorsqu'elle est jugée non-conforme.



Sur l'exemple illustré par la capture, l'utilisateur intervient sur une petite facture simplifiée. Son rôle est de saisir les quantités des produits achetés. Mais dès lors qu'il entre une valeur qui dépasse la quantité en stock, un code VBA se déclenche et intervient avec une boîte de dialogue. Si l'utilisateur abandonne en cliquant sur le bouton Non, la saisie est annulée et la case est réinitialisée. S'il force le passage en cliquant sur le bouton Oui, l'inscription est tout de même autorisée.

Classeur Excel à télécharger
Pour le développement de ce code VBA, nous suggérons d'appuyer l'étude sur un classeur offrant cette petite facture à implémenter.

• Télécharger le classeur empecher-modification.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons la petite facture de la présentation. L'utilisateur doit saisir les quantités achetées en colonne D tandis que les stocks disponibles sont inscrits en colonne E.

Détecter la saisie
Nous l'avons dit, VBA Excel offre des gestionnaires d'événements attachés aux feuilles d'un classeur. Celui qui nous intéresse se nomme Change. Il se déclenche dès qu'une modification est entreprise sur n'importe quelle cellule de la feuille. Mais nous allons limiter son champ d'action pour concentrer l'attention uniquement sur les cellules de la colonne D. Pour débuter, nous proposons donc de créer cette procédure événementielle qui doit accueillir ce code VBA de surveillance.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil1 (Feuille),

De cette manière, nous affichons la feuille de code associée à la feuille du classeur.

• En haut de cette feuille de code, déployer la liste déroulante de gauche,
• Dans les propositions, choisir l'objet Worksheet,

Cet objet VBA Excel désigne une feuille plus précisément ici, la feuille active. Cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange. Mais ce n'est pas celle que nous souhaitons. Nous voulons intervenir lors d'une modification et non pas lors d'un changement de sélection.

• En haut de la feuille de code, déployer maintenant la seconde liste déroulante,
• Dans les propositions, choisir l'événement Change,

C'est ainsi que nous créons la procédure événementielle Worksheet_Change :

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Vous notez la présence du paramètre qui lui est transmis. Il se nomme Target et il s'agit d'un objet de type Range. Il désigne tout simplement la cellule qui vient d'être modifiée. Grâce à lui, nous allons pouvoir exploiter certaines propriétés renseignant notamment sur la ligne et la colonne de cette cellule pour la localiser. De fait, la procédure Worksheet_SelectionChange n'étant pas utile, elle peut être supprimée.

Les variables VBA
Pour débuter l'implémentation de cette procédure et comme il s'agira de localiser la cellule modifiée, nous avons tout d'abord besoin de déclarer trois variables. Le rôle des deux premières sera de prélever les indices de ligne et de colonne de la cellule ciblée.

• Dans les bornes de la procédure, ajouter les trois déclarations suivantes :

Dim lig As Byte: Dim col As Byte
Dim rep As Byte

Nous les typons toutes trois comme des entiers courts (Byte), ce qui est largement suffisant pour le cas que nous traitons. Si vous deviez appliquer ce code sur un tableau de plus de 255 lignes, vous devriez typer la variable lig comme un entier classique (Integer). La dernière variable (rep) doit stocker la réponse de l'utilisateur par le biais d'une boîte de dialogue pour définir si la saisie peut être forcée ou non. Nous le comprendrons au fil de la progression de ce développement.

Cibler l'action
Nous l'avons dit, dans un but d'optimisation des ressources, nous ne souhaitons pas intervenir dès qu'une cellule est modifiée. Nous devons simplement focaliser l'attention sur les modifications intervenant dans la colonne des quantités. Il s'agit de la colonne D, soit de la quatrième colonne de la feuille. Nous proposons donc d'engager un test sur la colonne de la cellule modifiée pour décider de la suite dutraitement.

• Après la déclaration des variables, ajouter l'instruction conditionnelle suivante :

...
If Target.Column = 4 Then
lig = Target.Row: col = 4

End If
...

S'il s'agit bien de la colonne D, nous choisissons d'engager l'analyse. Nous commençons par prélever l'indice de ligne de la cellule modifiée grâce à la propriété Row de l'objet Target. Puis, nous fixons naturellement l'indice de colonne sur la valeur 4 pour la colonne D. Implicitement et dans le cas contraire vous l'avez compris, le traitement sera avorté.

Comparer la quantité au stock
Maintenant, pour déclencher l'alerte, c'est une nouvelle condition qui doit être vérifiée. Si le stock est inférieur à la quantité saisie, un message d'incitation d'abandon doit être adressé à l'utilisateur. La comparaison est très simple à exercer dans la mesure où ces deux valeurs sont placées sur la même ligne (lig) et en colonnes voisines (col + 1).

• A la suite de l'instruction conditionnelle, ajouter le nouveau test suivant :

...
If (Cells(lig, col + 1).Value < Cells(lig, col).Value) Then
rep = MsgBox("Le stock n'est pas suffisant." & Chr(13) & Chr(10) & "Voulez-vous quand même poursuivre ?", vbYesNo)

End If
...

Sur la ligne de la cellule modifiée, nous cherchons à savoir si le stock (Cells(lig, col + 1).Value) est inférieur (<) à la quantité achetée (Cells(lig,col).Value). Si tel est le cas, nous adressons un message d'alerte à l'utilisateur par le biais de la fonction MsgBox. Sur cette boîte de message, nous lui offrons deux boutons, Oui et Non (vbYesNo). Un clic sur l'un ou l'autre renvoie une valeur numérique que nous stockons dans la variable rep. Le bouton Oui retourne le chiffre 6. Un clic sur le bouton Non retourne le chiffre 7.

Interrompre la modification
Ainsi, si la variable rep vaut 7, nous devons annuler la modification puisque l'utilisateur a compris et a choisi d'abandonner la saisie, sans doute pour l'ajuster. C'est donc un nouveau critère que nous devons vérifier sur la variable rep. Les instructions conditionnelles sont décidément à l'honneur dans ce développement.

• A la suite de la seconde instruction conditionnelle, ajouter le nouveau critère suivant :

...
If rep = 7 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
...

Vous l'avez compris, si l'utilisateur clique sur le bouton Oui, comme le critère n'est pas vérifié, c'est implicitement une fois encore que la suite du traitement est avortée. En d'autres termes, cela signifie que sa saisie forcée est acceptée malgré la pénurie en stock et donc que l'inscription de la quantité a bien lieu.

En revanche, s'il clique sur le bouton Non, nous dégainons une succulente astuce. Nous neutralisons tout d'abord les gestionnaires d'événements du VBA Excel en réglant la propriété EnableEvents de l'objet Application à False. C'est ainsi que nous pouvons annuler la saisie de l'utilisateur, grâce à la méthode Undo du même objet. Sans cette neutralisation, cette annulation aurait été considérée comme une modification dans une cellule de la quatrième colonne. Certes, elle n'aurait engendré aucun message mais un déclenchement d'événement tout de même. Et bien sûr avant de terminer, nous n'oublions pas de réenclencher ces gestionnaires pour contrôler les prochaines saisies.

Nous en avons déjà terminé et nous proposons de tester la fonctionnalité de ce code VBA Excel.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Dans la colonne des quantités, réaliser quelques saisies inférieures aux stocks,

Comme vous pouvez le voir, VBA Excel ne rumine pas. Les saisies sont certes contrôlées puisqu'il s'agit du champ d'action, mais elles sont acceptées sans sourciller.

• Dans une nouvelle ligne, taper alors une quantité supérieure au stock de l'article,

Cette fois, le gestionnaire d'événements déclenche le code VBA qui ne voit pas cette inscription d'un bon oeil. L'incitation à l'abandon est claire. Cependant, si l'utilisateur clique sur le bouton Oui, le passage en force est accepté et la saisie est honorée. S'il clique sur le bouton Non, la procédure est abandonnée et la cellule de la quantité est réinitialisée.

Voilà donc un moyen très intéressant pour contrôler toutes les actions et interventions des utilisateurs dans les cellules des feuilles Excel.