Remplir plusieurs cellules avec une seule liste déroulante

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


Sujets que vous pourriez aussi aimer :


Changer plusieurs cellules avec une liste

Dans une précédente formation, nous avons vu comment créer en une seule fois des listes déroulantes conditionnelles, pour affecter des tâches à des salariés, selon que le jour était ouvré ou un jour de Week-End. Mais, bien que ce système se fût avéré fonctionnel, nous avions constaté que le fait de présélectionner une plage était inopérant. Au choix dans la liste, seulement la première cellule de la sélection était affectée.



Grâce au code VBA que nous proposons de développer, le choix dans la liste déroulante sera automatiquement étendu à toute la sélection, pour plus de productivité. Sur l'exemple illustré par la capture, l'opérateur poste un salarié le soir de tous les jours d'une semaine. A validation et comme vous le constatez, toutes les inscriptions sont réalisées à partir d'une seule action. Bien sûr, nous aurions pu engager des jeux de couleurs différents, en fonction des affectations, pour simplifier la lecture de ce planning. Mais ce n'est pas la vocation de ce sujet ici.

Classeur Excel à télécharger
Nous suggérons d'appuyer les travaux sur un planning déjà travaillé et abritant des listes déroulantes déjà remplies.

• Télécharger le classeur remplir-cellules-une-liste.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 retrouvons bien le planning des affectations à remplir. Toutes ses cellules sont dotées d'une liste déroulante permettant de choisir entre le matin, l'après-midi et le soir.

• Sélectionner toutes les cases du salarié Doeuf, entre le lundi 06 et le vendredi 10,

Il s'agit de la plage de cellules E9:E13.

• Déployer alors la liste déroulante qui se suggère sur la première cellule de la sélection,
• Dans les propositions, choisir l'affectation : Après-midi,

Comme vous pouvez le voir et malgré la présélection, ce choix n'est appliqué qu'à la première cellule de la plage. C'est ce défaut que nous souhaitons corriger par le code VBA pour plus de souplesse.



Code VBA au clic
Nous avons tout d'abord besoin de créer une procédure VBA capable de se déclencher dès qu'un changement est observé dans une ou des cellules.

• 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(Planning),

Ainsi, nous affichons la feuille de code VBA associée à la feuille Excel du planning, au centre de l'écran. Naturellement, elle est encore vierge pour l'instant.

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

Par cette action, nous créons la procédure événementielle Worksheet_SelectionChange. Son code sera déclenché dès qu'un changement de sélection sera détecté sur la feuille du planning. Ce n'est donc pas celle qui nous intéresse. Nous souhaitons intervenir au changement de valeur, lorsque l'utilisateur choisit une affectation dans l'une des listes déroulantes.

• En haut de la feuille de code, déployer cette fois la liste déroulante de droite,
• Dans les propositions, choisir l'événement associé nommé Change,

Cette fois, nous obtenons la procédure Worksheet_Change. C'est bien celle que nous voulions. Par conséquent, la précédente peut être supprimée.

Vérifier la sélection
Par esprit d'optimisation néanmoins, nous devons veiller à lancer le traitement si et seulement si les cellules sélectionnées sont bien situées sur le planning et non en dehors. Les cellules de ce planning sont reconnues sous l'intitulé planning. Vous pouvez le vérifier en déployant la zone Nom en haut à gauche de la feuille Excel. La première cellule de la sélection, celle qui reçoit la modification par défaut, est quant à elle représentée par l'objet Target passé en paramètre de notre procédure. Nous devons réaliser l'intersection de ces deux zones pour voir si elle aboutit bien à quelque chose.

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

...
If Not Intersect([planning], Target) Is Nothing Then

End If
...

La fonction VBA Intersect peut recouper plusieurs plages. Ici, nous la faisons simplement agir sur les cellules du planning et la première cellule de la présélection. Si l'intersection de ces deux zones n'est pas nulle (Not ... Is Nothing), nous en concluons (Then) que nous pouvons poursuivre le traitement. En effet, cela signifie que la première cellule de la sélection appartient bien au planning. Le cas échéant, l'exécution est implicitement avortée.

Couper l'écouteur d'événements
Avant de poursuivre, nous devons impérativement couper l'écouteur d'événements. En effet, l'inscription des valeurs dans les cellules sélectionnées va déclencher en boucle notre procédure événementielle pour un traitement qui n'aboutira jamais.

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

...
Application.EnableEvents = False
...

En réglant à False la propriété EnableEvents de l'objet Application, nous désactivons ces gestionnaires, mais temporairement, nous le verrons.

Inscrire la valeur dans la sélection
Maintenant que l'écouteur est inactif, nous pouvons procéder à la modification des cellules sélectionnées. Pour cela, nous devons toutes les affecter sur la valeur de l'objet Target qui rappelons-le, représente la première cellule de la sélection au moment où elle reçoit la valeur de la liste déroulante.

• A la suite dans l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
If Not Intersect([planning], Target) Is Nothing Then
Application.EnableEvents = False
Selection.Value = Target.Value
End If
...

Nous le savons parfaitement, en VBA Excel, c'est l'objet Selection qui représente toutes les cellules d'une plage sélectionnée au moment de l'exécution du code. Nous affectons sa propriété Value sur la donnée réceptionnée (Target.Value) par la première cellule de la sélection. C'est ainsi qu'elles doivent désormais afficher toutes la même donnée, choisie par l'utilisateur avec la liste déroulante.

Relancer l'écouteur d'événements
Avant de finir, il est impératif de réactiver l'écouteur d'événements. En son absence, l'utilisateur ne pourra plus remplir plusieurs cellules à la fois puisque la procédure événementielle ne se déclenchera plus.

• A la fin de l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
If Not Intersect([planning], Target) Is Nothing Then
Application.EnableEvents = False
Selection.Value = Target.Value
Application.EnableEvents = True
End If
...

Nous basculons simplement cette même propriété EnableEvents dans son état opposé.

Il est temps de tester ce trivial code VBA.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille du planning (ALT + Tab),
• Sélectionner plusieurs cellules et choisir une affectation avec la liste déroulante,

Comme vous pouvez l'apprécier, ce sont toutes les cellules pointées qui sont effectivement remplies de la valeur choisie et ce, avec un seul clic. En termes de productivité, cette solution est donc particulièrement intéressante.



Bien sûr et puisque nous avons réactivé l'écouteur d'événements en fin de procédure, si vous récidivez sur une autre plage, la sentence est la même, pour le plus grand plaisir.