Consolider les ventes cochées 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 :


Consolider les ventes cochées

A l'occasion de cette nouvelle astuce VBA Excel, nous allons découvrir comment additionner les montants numériques dont les cases sont cochées par l'utilisateur et ce, avec une facilité déconcertante.



Sur l'exemple illustré par la capture, dès que l'utilisateur clique sur une case à cocher, son montant associé, dans la colonne voisine de gauche, est cumulé dans la synthèse fournie sur la droite du tableau, pour la même ligne.

Classeur Excel à télécharger
Pour développer cette solution, nous suggérons d'appuyer l'étude sur un classeur Excel offrant ce tableau annuel des ventes.

• Télécharger le fichier sommer-si-coche.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 dans le bandeau de sécurité,

Nous retrouvons bien le tableau annuel (en lignes) des ventes par articles (en colonnes). Les synthèses sont automatiquement calculées grâce à la fonction d'addition conditionnelle Somme.Si :

=SOMME.SI(D4:N4; "þ"; C4:M4)

La somme est entreprise sur la ligne du mois en cours (C4:M4) dans la mesure où la cellule adjacente à la plage (D4:N4) porte la valeur þ. Souvenez-vous en effet, toutes les fonctions classiques comme la somme et la moyenne, sont capables d'ignorer dans leurs calculs, toutes les cellules qui ne sont pas numériques. Ce symbole (þ) est issu de la police Wingdings. Toutes les colonnes des cases à cocher (D, F, H, J, L, N) sont ainsi formatées. Ce symbole retranscrit une case cochée tandis que la lettre o retranscrit une case décochée. Ce sont ces lettres ou symboles que nous allons exploiter par le code VBA Excel, au clic de l'utilisateur, pour ajouter ou retirer de la somme le montant correspondant, selon que sa caseadjacente est cochée ou décochée.

Code VBA au clic sur une cellule
Nous devons donc déclencher l'exécution automatique d'un code VBA au clic sur une cellule. Si la cellule cliquée porte le symbole þ, nous devons le remplacer par le symbole o pour décocher la case et inversement par le symbole þ, pour cocher la case. Mais cette exécution doit intervenir sur des cellules spéciales pour ne pas consommer inutilement les ressources. Ces cases à cocher ou à décocher sont situées entre les lignes 4 et 15 pour les colonnes D, F, H, J, L et N, soit des colonnes paires. Vous l'avez compris, la fonction Mod va être précieuse dans ce développement.

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

De cette manière, nous affichons la feuille de code VBA (vierge pour l'instant) associée à la feuille Excel de notre tableau des ventes.

• En haut de l'éditeur, déployer la liste déroulante de gauche,
• Dans les propositions, choisir l'élément Worksheet,

C'est ainsi que nous créons la procédure évenementielle Worksheet_SelectionChange. C'est elle qui va permettre de déclencher un code VBA au clic de l'utilisateur dans une cellule de la feuille, mais rappelons-le, pas n'importe laquelle. Le paramètre en attente (Target) est très important. Il désigne la cellule précisément cliquée par l'utilisateur. Grâce à lui, nous pourrons savoir si la cellule cliquée est celle d'une case à cocher ou à décocher.

Tester la cellule cliquée
Nous devons donc premièrement tester ce paramètre pour savoir si la cellule cliquée est bien située entre les lignes 4 et 15 et entre les colonnes 4 et 14 sachant que l'indice de colonne de cette dernière doit être pair. C'est ainsi que nous ne déclencherons pas un code VBA inutilement, dans l'optique de préserver les ressources de la solution. L'objet Target est un objet de type Range (Plage de cellules ou cellule seule). Il offre donc les propriétés Row et Column pour connaître la ligne et la colonne de la cellule cliquée.

• Dans les bornes de la procédure événementielle, créer l'instruction conditionnelle suivante :

...
If Target.Count = 1 And Target.Column >= 4 And Target.Column <= 14 And Target.Column Mod 2 = 0 And Target.Row >= 4 And Target.Row <= 15 Then

End If
...

Grâce à la propriété Count de l'objet Target, nous nous assurons tout d'abord que seule une cellule a été cliquée et non plusieurs. Dans le même temps (And), grâce aux propriétés Column et Row, nous vérifions que cette cellule cliquée est bien située entre les colonnes 4 et 14 (D et N) et entre les lignes 4 et 15, celles du tableau des ventes. Toujours dans le même temps (And), grâce à l'opérateur Mod (Mod 2 = 0), nous nous assurons que l'indice de colonne de la cellule cliquée est bien pair, en d'autres termes qu'il s'agit d'une case à cocher ou à décocher.

Cocher ou décocher la case cliquée
Lorsque toutes ces conditions sont réunies, étant donné que les cellules concernées sont formatées dans la police Wingdings, pour les cocher ou les décocher au clic de l'utilisateur, il suffit simplement de remplacer le symbole þ par la lettre o pour décocher et inversement de remplacer la lettre o par le symbole þ, pour cocher.

• Dans les bornes de l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
Target = IIf(Target = "þ", "o", "þ")
...

C'est une nouvelle astuce que nous présentons ici. L'instruction IIf est une contraction de l'instruction conditionnelle If pour en simplifier la syntaxe. Si la valeur portée par la cellule cliquée vaut le symbole þ, nous la remplaçons par la lettre o. Le cas échéant, nous inscrivons le symbole þ à la place de la lettre o.

Additionner en fonction des cases cochées
Il ne nous reste plus qu'à tester ce léger code VBA Excel épaulé par la formule exploitant la fonction de somme conditionnelle sur les lignes du tableau.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Puis, cliquer sur des cases cochées ou décochées,

Dans le premier cas, vous constatez que les montants se cumulent pour le mois en cours et dans le second, que la valeur correspondant à la case est soustraite.

Voilà donc un moyen original et ergonomique pour obtenir des synthèses sans équivoque en VBA Excel.