STOP aux listes déroulantes limitées ! Sélectionnez PLUSIEURS valeurs dans UNE SEULE cellule Excel

Dans ce tutoriel, je vais vous montrer comment transformer vos listes déroulantes classiques en listes multi-sélection qui permettent de choisir plusieurs éléments dans une même cellule.

Cette technique va complètement changer votre façon de gérer les données dans Excel, notamment pour les situations où un élément peut avoir plusieurs attributs ou appartenir à plusieurs catégories simultanément.

Téléchargement

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

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous gérons les inscriptions d'une salle de sport.

Ce tableau contient une liste de membres avec leur nom et les cours auxquels ils souhaitent participer.

Nous allons également une liste de cours disponibles :

Le problème avec les listes déroulantes classiques d'Excel est qu'elles ne permettent de sélectionner qu'un seul élément à la fois.

Lorsque nous sélectionnons un nouveau cours, l'ancien est automatiquement remplacé.

Or, dans notre cas, chaque membre peut s'inscrire à plusieurs cours différents et nous voulons que tous ces cours apparaissent dans la même cellule, séparés par des virgules.

C'est exactement ce que nous allons réaliser grâce à un petit code VBA qui va intercepter chaque sélection dans la liste déroulante et l'ajouter aux sélections précédentes au lieu de les remplacer.

Le résultat final nous permettra d'avoir par exemple « Yoga, Musculation, Cardio » dans une seule cellule pour Sophie Martin.

Avant de commencer, nous devons comprendre que notre solution repose sur ce qu'on appelle un « événement de feuille de calcul ».

Il s’agit d’une action qui se produit dans Excel, comme la modification d'une cellule, et nous pouvons écrire du code VBA qui se déclenche automatiquement quand cet événement se produit.

Dans notre cas, nous allons utiliser l'événement qui se déclenche à chaque fois qu'une cellule est modifiée.

2. Création des plages nommées et de la liste déroulante

Commençons par préparer notre feuille Excel avec les plages nommées.

Les plages nommées sont des zones de cellules auxquelles nous donnons un nom personnalisé pour pouvoir les référencer facilement dans nos formules et notre code VBA.

C'est beaucoup plus pratique que d'utiliser des références comme « E7:E12 » qui peuvent être difficiles à mémoriser.

Nous sélectionnons d'abord notre liste de cours disponibles, c'est-à-dire les cellules E7 à E12 en maintenant le bouton gauche de la souris enfoncé.

Une fois cette sélection effectuée, nous regardons en haut à gauche de notre écran Excel, juste au-dessus de la colonne A.

Nous voyons une petite zone blanche appelée « Zone de nom » qui affiche actuellement « F2:F7 ».

Nous cliquons dans cette zone de nom pour la sélectionner, puis nous tapons « Liste_Cours » sans espace ni accent.

Les noms de plages dans Excel ne peuvent pas contenir d'espaces, c'est pourquoi nous utilisons un trait de soulignement.

Après avoir tapé ce nom, nous appuyons sur la touche [Entrée] pour valider.

Notre plage est maintenant nommée et nous pourrons l'utiliser facilement dans notre validation de données.

Nous allons maintenant nommer la zone qui contiendra nos listes déroulantes multi-sélection.

Nous sélectionnons les cellules B2 à B6, qui correspondent à la colonne « Cours Souscrits » pour nos cinq membres.

Nous utilisons exactement la même technique : nous cliquons dans la zone de nom en haut à gauche, nous tapons « Zone_Saisie » et nous validons avec [Entrée].

Cette plage nommée sera utilisée dans notre code VBA pour détecter quand une modification a lieu dans une des cellules concernées par notre liste déroulante multi-sélection.

Passons maintenant à la création de la liste déroulante elle-même.

Nous nous assurons que les cellules B2 à B6 sont toujours sélectionnées, sinon nous les resélectionnons.

Nous nous rendons ensuite dans l'onglet « Données » du ruban Excel, situé en haut de notre écran.

Dans cet onglet, nous cherchons le groupe « Outils de données » et nous cliquons sur le bouton « Validation des données ».

Une fenêtre de dialogue s'ouvre.

Dans cette fenêtre, nous voyons plusieurs onglets, mais celui qui nous intéresse est « Options ».

Sous « Autoriser », nous cliquons sur le menu déroulant et nous sélectionnons « Liste ».

Cette option indique à Excel que nous voulons créer une liste déroulante. Juste en dessous, un nouveau champ apparaît intitulé « Source ». C'est ici que nous allons indiquer à Excel quelles valeurs doivent apparaître dans notre liste déroulante.

Nous cliquons dans le champ « Source » et nous tapons le signe égal suivi du nom de notre plage :

=Liste_Cours

Le signe égal est nécessaire pour indiquer à Excel qu'il s'agit d'une référence à une plage nommée. Nous pourrions également cliquer sur le petit bouton avec une flèche à droite du champ « Source » pour sélectionner manuellement notre plage F2:F7, mais utiliser le nom de plage est plus propre et plus facile à maintenir.

Nous validons en cliquant sur le bouton « OK » en bas de la fenêtre.

Maintenant, si nous cliquons sur n'importe quelle cellule de B2 à B6, nous voyons apparaître une petite flèche sur le côté droit de la cellule.

Quand nous cliquons sur cette flèche, notre liste de cours s'affiche et nous pouvons en sélectionner un.

Pour l'instant, cette liste se comporte de manière classique : si nous sélectionnons « Yoga » puis que nous rouvrons la liste pour sélectionner « Musculation », le « Yoga » disparaît et est remplacé par « Musculation ».

C'est exactement ce comportement que nous allons modifier dans la partie suivante avec notre code VBA.

3. Création du code VBA pour la multi-sélection

Le VBA est un langage de programmation intégré directement dans Excel qui permet d'automatiser des tâches répétitives, de créer des fonctions personnalisées et d'aller bien au-delà des limites des formules classiques. VBA signifie Visual Basic for Applications, et c'est ce qui transforme Excel d'un simple tableur en un véritable outil de développement d'applications professionnelles.

Il permet de créer des interfaces utilisateur avec des boutons et des formulaires, d'automatiser des processus complexes qui prendraient des heures à réaliser manuellement, de manipuler des données de manière très précise, et même d'interagir avec d'autres applications Microsoft comme Word ou Outlook. Par exemple, nous pouvons créer un bouton qui génère automatiquement des rapports, envoie des emails personnalisés à une liste de clients, ou formate des milliers de lignes de données en quelques secondes selon des règles métier spécifiques.

Dans le cadre de cette vidéo, nous n'aurons pas le temps de nous étendre longuement sur le VBA car c'est un sujet vaste qui mériterait des heures d'explication. Nous nous concentrons aujourd'hui sur cette application pratique de liste déroulante multi-sélection, mais sachez que ce que nous venons de voir n'est qu'un petit aperçu des possibilités incroyables offertes par ce langage. Si vous souhaitez en savoir plus sur le VBA et découvrir comment décupler les possibilités offertes par Excel pour créer vos propres outils sur mesure, vous trouverez mon livre « Apprendre le VBA pour les grands débutants » en suivant ce lien.

Ce livre vous guidera pas à pas dans l'apprentissage du VBA, même si vous n'avez jamais programmé de votre vie.

Pour créer la macro VBA pour transformer notre liste déroulante classique en liste multi-sélection, nous allons avoir besoin d’accéder à l'éditeur VBA.

Pour cela, nous utilisons le raccourci clavier [Alt]+[F11].

Cela ouvre une nouvelle fenêtre qui est l'environnement de développement VBA d'Excel.

Dans cette fenêtre, nous voyons sur la gauche un panneau appelé « Explorateur de projet » qui affiche la structure de notre classeur Excel. Nous cherchons l'élément qui correspond à notre feuille de calcul.

Par défaut, Excel nomme ses feuilles « Feuil1 », « Feuil2 », etc., mais si nous avons renommé notre feuille, nous verrons ce nom personnalisé entre parenthèses.

Nous double-cliquons sur cet élément pour ouvrir la fenêtre de code correspondante.

Une grande zone blanche s'ouvre sur la partie droite de l'écran. C'est ici que nous allons saisir notre code VBA.

Private Sub Worksheet_Change(ByVal Target  As Range)
    Dim AncienneValeur As String, NouvelleValeur  As String
    
    If Target.CountLarge > 1 Then Exit  Sub
    
    If Not Intersect(Target,  Range("Zone_Saisie")) Is Nothing Then
        
        Application.EnableEvents =  False
        
        NouvelleValeur = Target.Value
        
        Application.Undo
        
        AncienneValeur = Target.Value
        
        If AncienneValeur = "" Then
            Target.Value = NouvelleValeur
        ElseIf NouvelleValeur = "" Then
            Target.Value = ""
        Else
            Target.Value = AncienneValeur & ",  " & NouvelleValeur
        End If
        
        Application.EnableEvents = True
        
    End If
End Sub

Revenons maintenant sur ce code.

La première ligne définit une procédure qui va se déclencher automatiquement chaque fois qu'une cellule de notre feuille est modifiée :

Private Sub Worksheet_Change(ByVal Target As  Range)

Le mot « Target » représente la cellule qui vient d'être modifiée.

La ligne suivante permet de déclarer deux variables de type texte que nous appelons « AncienneValeur » et « NouvelleValeur ».

    Dim AncienneValeur As String, NouvelleValeur As  String

Une variable est comme une boîte dans laquelle nous allons temporairement stocker des informations pendant l'exécution de notre code.

Nous en aurons besoin pour capturer à la fois la valeur que l'utilisateur vient de sélectionner dans la liste déroulante et la valeur qui était présente dans la cellule avant cette sélection.

La ligne suivante vérifie si la cellule modifiée fait partie de notre plage nommée « Zone_Saisie ».

If Not Intersect(Target,  Range("Zone_Saisie")) Is Nothing Then

En effet, la fonction VBA Intersect regarde s'il y a une intersection entre la cellule modifiée et notre zone de saisie.

Si cette intersection existe, cela signifie que l'utilisateur a bien modifié une de nos cellules avec liste déroulante, et nous pouvons donc exécuter le reste de notre code.

Si la modification a eu lieu ailleurs dans la feuille, cette condition sera fausse et tout le code à l'intérieur du « If » sera ignoré.

Maintenant vient une partie technique mais très importante :

Application.EnableEvents = False

Cette ligne désactive temporairement tous les événements dans Excel.

En effet étant donné que notre code va lui-même modifier des cellules, si les événements restaient activés, ces modifications déclencheraient à nouveau notre événement Worksheet_Change, qui modifierait à nouveau des cellules, ce qui déclencherait à nouveau l'événement, et ainsi de suite à l'infini.

C'est ce qu'on appelle une boucle infinie, et cela ferait planter Excel. En désactivant les événements temporairement, nous évitons ce problème.

Ensuite, nous allons capturer la valeur que l'utilisateur vient de sélectionner dans la liste déroulante et la stocke dans notre variable « NouvelleValeur ». Par exemple, si l'utilisateur a sélectionné « Yoga », alors « NouvelleValeur » contient maintenant le texte « Yoga ».

NouvelleValeur = Target.Value

Vient ensuite une astuce peu connue :

Application.Undo

Cette instruction annule la dernière action de l'utilisateur, exactement comme si nous avions appuyé sur [Ctrl]+[Z]. Pourquoi annuler ce que l'utilisateur vient de faire ? Parce que nous voulons récupérer la valeur qui était dans la cellule avant la modification.

En effet, après avoir exécuté cette ligne, la cellule revient à son état précédent.

Nous pouvons alors capturer cette valeur précédente avec :

AncienneValeur = Target.Value

Maintenant nous avons les deux informations dont nous avons besoin : la nouvelle sélection dans « NouvelleValeur » et l'ancien contenu dans « AncienneValeur ».

Nous allons maintenant combiner ces deux valeurs de manière intelligente.

Pour cela, nous allons mettre en place une structure conditionnelle avec trois cas possibles.

Le premier cas vérifie si la cellule était vide avant la sélection. Si c'est le cas, nous mettons simplement la nouvelle valeur dans la cellule. C'est la situation où Sophie Martin sélectionne son premier cours, par exemple « Yoga ». La cellule était vide, donc nous mettons juste « Yoga » dedans.

        If AncienneValeur = "" Then
            Target.Value = NouvelleValeur

Le deuxième cas gère la situation où l'utilisateur sélectionne une ligne vide dans la liste déroulante. Dans ce cas, nous vidons complètement la cellul. C'est notre mécanisme pour permettre à l'utilisateur de tout effacer d'un coup s'il le souhaite.

        ElseIf NouvelleValeur = "" Then
            Target.Value = ""

Le troisième cas est celui qui nous intéresse le plus.

Il se produit quand la cellule contenait déjà quelque chose et que l'utilisateur sélectionne une nouvelle valeur.

Dans ce cas, nous combinons l'ancienne et la nouvelle valeur avec l'opérateur « & », qui permet de coller deux textes ensemble. Nous ajoutons également « , » entre les deux valeurs pour les séparer proprement. Ainsi, si la cellule contenait « Yoga » et que l'utilisateur sélectionne « Musculation », la cellule affichera maintenant « Yoga, Musculation ».

        Else
            Target.Value = AncienneValeur & ",  " & NouvelleValeur
        End If

Finalement, et c'est très important, nous réactivons les événements :

Application.EnableEvents = True

Sans cette ligne, plus aucun événement ne fonctionnerait dans notre feuille Excel, ce qui causerait de nombreux problèmes.

Nous devons toujours nous assurer que chaque :

EnableEvents = False

est effectivement suivi d'un :

EnableEvents = True

Pour utiliser notre nouvelle liste déroulante multi-sélection, nous fermons l'éditeur VBA en appuyant sur [Alt]+[Q] ou en cliquant simplement sur la croix rouge en haut à droite de la fenêtre VBA.

Nous revenons alors à notre feuille Excel. Avant de tester, nous devons enregistrer notre fichier au format qui prend en charge les macros.

Nous cliquons sur « Fichier » puis « Enregistrer sous », et dans le menu déroulant « Type », nous sélectionnons « Classeur Excel (prenant en charge les macros) ». L'extension du fichier sera .xlsm au lieu de .xlsx.

Testons maintenant notre travail. Nous cliquons sur la cellule B2 correspondant à Sophie Martin, nous ouvrons la liste déroulante et nous sélectionnons « Yoga ».

La cellule affiche « Yoga », ce qui est normal puisque c'était vide au départ.

Maintenant, sans changer de cellule, nous rouvrons la liste déroulante et nous sélectionnons « Pilates ».

La magie opère : la cellule affiche maintenant « Yoga, Pilates ». Nous pouvons continuer à ajouter des cours : nous sélectionnons « Cardio » et la cellule affiche « Yoga, Pilates, Cardio ».

Pour effacer tout le contenu d'une cellule, nous ouvrons la liste déroulante et nous faisons défiler tout en haut pour sélectionner la ligne vide au-dessus de « Yoga ».

La cellule se vide complètement, nous permettant de recommencer à zéro si nécessaire. Nous pouvons maintenant remplir toutes les inscriptions de nos membres en utilisant cette technique, créant ainsi un tableau complet et facile à lire où chaque membre peut avoir plusieurs cours dans une seule cellule.