L'article montre comment créer une liste déroulante Excel permettant aux utilisateurs de sélectionner plusieurs éléments avec ou sans doublons.
Excel a parcouru un long chemin depuis sa création et introduit de plus en plus de fonctionnalités utiles à chaque nouvelle version. Dans Excel 365, ils ont ajouté la possibilité de rechercher dans les listes de validation des données, ce qui représente un gain de temps considérable lorsque vous travaillez avec de grands ensembles de données. Cependant, même avec cette nouvelle option, Excel prêt à l'emploi ne permet toujours de sélectionner qu'un seul élément dans une liste d'options prédéfinies. Mais n’ayez crainte, il existe une solution. En utilisant VBA, vous pouvez créer des listes déroulantes avec plusieurs sélections. Avec la possibilité d'éviter les doublons et de supprimer les éléments incorrects, cette fonctionnalité peut rationaliser la saisie des données et améliorer la précision de vos feuilles de calcul Excel.
Comment créer un menu déroulant Excel avec plusieurs sélections
La création d'une liste déroulante à sélection multiple dans Excel est un processus en deux parties :
- Tout d’abord, vous créez une liste régulière de validation des données dans une ou plusieurs cellules.
- Et puis, insérez le code VBA à l’arrière de la feuille de calcul cible.
Cela fonctionne également dans l'ordre inverse :)
Créer une liste déroulante normale
Pour insérer une liste déroulante dans Excel, vous utilisez leLa validation des donnéesfonctionnalité. Les étapes varient légèrement selon que les éléments source se trouvent dans une plage normale, une plage nommée ou un tableau Excel.
D'après mon expérience, la meilleure option consiste à créer une liste de validation des données à partir d'une table. CommeTableaux Excelsont dynamiques par nature, une liste déroulante associée se développera ou se contractera automatiquement à mesure que vous ajouterez ou supprimerez des éléments du tableau.
Pour cet exemple, nous allons utiliser la table avec le nom simpleTableau 1qui réside dans A2:A25 dans la capture d'écran ci-dessous. Pour créer une liste de sélection à partir de ce tableau, les étapes sont les suivantes :
- Sélectionnez une ou plusieurs cellules pour votre liste déroulante (D3 : D7 dans notre cas).
- Sur leDonnéesonglet, dans l'ongletOutils de donnéesgroupe, cliquez surLa validation des données.
- Dans lePermettredans la liste déroulante, sélectionnezListe.
- Dans leSourceDans la zone , entrez la formule qui fait indirectement référence à la colonne du tableau 1 nomméeArticles.
=INDIRECT("Table1[Articles]")
- Une fois terminé, cliquez sur OK.
Le résultat sera une liste déroulante extensible et automatiquement mise à jour qui permet uniquementsélectionner un élément.
Pour plus d'informations, veuillez consulterComment créer une liste de validation des données Excel.
Insérer du code VBA pour autoriser plusieurs sélections
C’est la partie centrale du processus qui fait la magie. Pour transformer une liste de sélection à sélection unique classique en une liste déroulante à sélection multiple, vous devez insérer l'un de ces codes dans le back-end de votre feuille de calcul cible :
- Code VBA pour la liste déroulante à sélection multiple avec des doublons
- Code VBA pour la liste déroulante à sélection multiple sans doublons
- Code VBA pour la liste déroulante multi-sélection avec suppression d'élément
Pour ajouter du code VBA à votre feuille de calcul, procédez comme suit :
- Ouvrez Visual Basic Editor en appuyant surAlt+F11ou en cliquant sur leDéveloppeuronglet >Visual Basic. Si vous n'avez pas cet onglet sur votre ruban Excel, voircomment ajouter l'onglet Développeur.
- Dans le volet Explorateur de projet à gauche, double-cliquez sur le nom de la feuille de calcul qui contient votre liste déroulante. Cela ouvrira la fenêtre Code pour cette feuille.
Ou vous pouvez cliquer avec le bouton droit sur l'onglet de la feuille et choisirAfficher le codedans le menu contextuel. Cela ouvrira immédiatement la fenêtre Code pour une feuille donnée.
- Dans la fenêtre Code, collez le code VBA.
- Fermez l'éditeur VB et enregistrez votre fichier en tant que classeur prenant en charge les macros (.xlsm).
C'est ça! Lorsque vous revenez à la feuille de calcul, votre liste déroulante vous permettra de sélectionner plusieurs éléments :
Code VBA pour sélectionner plusieurs éléments dans la liste déroulante
Vous trouverez ci-dessous le code permettant de créer une liste de validation des données permettant de sélectionner plusieurs éléments, y compris des sélections répétées :
Option ExplicitePrivé SousFeuille de travail_Change(ParValDestinationCommeGamme)FaibleType de délimiteurComme ChaîneFaiblerngDropdownCommeGammeFaibleancienneValeurComme ChaîneFaiblenouvelle valeurComme ChaîneType de délimiteur =", "SiDestination.Count > 1Alors Sortie SousSur Erreur CV SuivantEnsemblerngDropdown = Cells.SpecialCells (xlCellTypeAllValidation)Sur Erreur Aller àerreur de sortieSirngDropdownEst Rien Alors Aller àerreur de sortieSiIntersection (Destination, rngDropdown)Est Rien Alors 'ne fais rienAutreApplication.EnableEvents =FAUXnewValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValueSiancienneValeur ="" Alors 'ne fais rien Autre SinouvelleValeur ="" Alors 'ne fais rien AutreDestination.Value = oldValue & DelimiterType & newValue'ajouter une nouvelle valeur avec un délimiteur Fin Si Fin SiFin SiexitError : Application.EnableEvents =VraiFin SousPrivé SousFeuille de travail_SelectionChange(ParValCibleCommeGamme)Fin Sous
Comment fonctionne ce code :
- Le code permet plusieurs sélections danstoutes les listes déroulantessur une feuille particulière. Vous n'avez pas besoin de spécifier la cellule cible ou la référence de plage dans le code.
- Le code estspécifique à une feuille de calcul, assurez-vous donc de l'ajouter à chaque feuille où vous souhaitez autoriser plusieurs sélections dans les listes déroulantes.
- Ce code permetrépétition, c'est-à-dire sélectionner le même élément plusieurs fois.
- Les éléments sélectionnés sont séparés parune virgule et un espace. Pour changer le délimiteur, remplacez ", " par le caractère souhaité dans DelimiterType = ", " (ligne 7 dans le code ci-dessus).
Liste déroulante Excel à sélection multiple sans doublons
Lors d’une sélection dans une grande liste, les utilisateurs peuvent parfois choisir le même élément plusieurs fois sans s’en apercevoir. Le code ci-dessous résout le problème des doublons dans une liste déroulante de validation de données à sélection multiple. Il permet aux utilisateurs de choisir un élément particulier une seule fois. Si vous essayez de sélectionner à nouveau le même élément, rien ne se passera. Plutôt cool, non ?
Option expliciteSous-privéFeuille de travail_Change(ParValDestinationCommeGamme)FaiblerngDropdownCommeGammeFaibleancienneValeurComme ChaîneFaiblenouvelle valeurComme ChaîneFaibleType de délimiteurComme ChaîneType de délimiteur =", "SiDestination.Count > 1Alors Sortie SousSur Erreur CV SuivantEnsemblerngDropdown = Cells.SpecialCells (xlCellTypeAllValidation)Sur Erreur Aller àerreur de sortieSirngDropdownEst Rien Alors Aller àerreur de sortieSiIntersection (Destination, rngDropdown)Est Rien Alors 'ne fais rienAutreApplication.EnableEvents =FAUXnewValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValueSiancienneValeur <>"" Alors SinouvelleValeur <>"" Alors SiancienneValeur = nouvelleValeurOu_ InStr(1, oldValue, DelimiterType & newValue)Ou_ InStr(1, oldValue, newValue & Replace(DelimiterType," ",""))AlorsDestination.Value = ancienneValueAutreDestination.Value = oldValue & DelimiterType & newValueFin Si Fin Si Fin SiFin SiexitError : Application.EnableEvents =VraiFin SousPrivé SousFeuille de travail_SelectionChange(ParValCibleCommeGamme)Fin Sous
Liste déroulante multi-sélection avec suppression d'éléments
Lorsque les utilisateurs doivent sélectionner plusieurs options mais peuvent commettre des erreurs ou changer d'avis au cours du processus de sélection, une liste déroulante à sélection multiple qui permet de supprimer les éléments incorrects peut leur sauver la vie.
Imaginez un scénario dans lequel vous devez attribuer plusieurs tâches aux membres de l'équipe à l'aide d'une liste déroulante. Avec la fonctionnalité par défaut d'Excel, le seul moyen de supprimer une tâche mal assignée consiste à effacer tout le contenu de la cellule et à recommencer. Avec la possibilité de supprimer des éléments individuels de la sélection, l'équipe peut facilement modifier les affectations de tâches, sans confusion ni erreur.
Le code ci-dessous implémente la fonctionnalité de suppression d'élément de manière simple et intuitive : le premier clic sur un élément l'ajoute à la sélection, et un deuxième clic sur le même élément le supprime de la sélection.
Option ExplicitePrivé SousFeuille de travail_Change(ParValDestinationCommeGamme)FaiblerngDropdownCommeGammeFaibleancienneValeurComme ChaîneFaiblenouvelle valeurComme ChaîneFaibleType de délimiteurComme ChaîneType de délimiteur =", "FaibleNombre de délimiteursComme EntierFaibleType de cibleComme EntierFaiblejeComme EntierFaiblearr()Comme ChaîneSiDestination.Count > 1Alors Sortie SousSur Erreur CV SuivantEnsemblerngDropdown = Cells.SpecialCells (xlCellTypeAllValidation)Sur Erreur Aller àerreur de sortieSirngDropdownEst Rien Alors Aller àexitErrorTargetType = 0 TargetType = Destination.Validation.Taper SiTypeCible = 3Alors 'le type de validation est "liste"Application.ScreenUpdating =FAUXApplication.EnableEvents =FAUXnewValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValueSiancienneValeur <>"" Alors SinouvelleValeur <>"" Alors SiancienneValeur = nouvelleValeurOuoldValue = newValue & Remplacer (DelimiterType," ","")OuoldValue = newValue & DelimiterTypeAlors 'laissez la valeur s'il n'y en a qu'une dans la listeoldValue = Remplacer (oldValue, DelimiterType,"") oldValue = Remplacer(oldValue, Remplacer(DelimiterType," ",""),"") Destination.Value = ancienneValueSinonSiInStr(1, oldValue, DelimiterType et newValue)OuInStr(1, ancienneValue, " " & nouvelleValue & DelimiterType)Alorsarr = Split (ancienne valeur, type délimiteur)Si PasIsError(Application.Match(newValue, arr, 0)) = 0AlorsDestination.Value = oldValue & DelimiterType & newValueAutre: Destination.Valeur ="" Pourje = 0À ULié(arr.)Siarr(i) <> nouvelleValeurAlorsDestination.Value = Destination.Value & arr(i) & DelimiterTypeFin Si Suivanti Destination.Value = Gauche (Destination.Value, Len (Destination.Value) - Len (DelimiterType))Fin Si SinonSiInStr(1, oldValue, newValue & Replace(DelimiterType," ",""))AlorsoldValue = Remplacer (oldValue, newValue,"") Destination.Value = ancienneValueAutreDestination.Value = oldValue & DelimiterType & newValueFin SiDestination.Value = Remplacer(Destination.Value, Remplacer(DelimiterType," ","") & Remplacer (DelimiterType," ",""), Remplacer(DelimiterType," ",""))' Supprimer les virgules et les espaces supplémentairesDestination.Value = Remplacer (Destination.Value, DelimiterType & Remplacer (DelimiterType," ",""), Remplacer(DelimiterType," ",""))SiDestination.Valeur <>"" Alors SiDroite (Destination.Value, 2) = DelimiterTypeAlors ' Supprimer le délimiteur à la finDestination.Value = Gauche (Destination.Value, Len (Destination.Value) - 2)Fin Si Fin Si SiInStr(1, Destination.Value, DelimiterType) = 1Alors ' Supprime le délimiteur comme premier caractèreDestination.Value = Remplacer (Destination.Value, DelimiterType,"", 1, 1)Fin Si SiInStr(1, Destination.Value, Remplacer(DelimiterType," ","")) = 1AlorsDestination.Value = Remplacer(Destination.Value, Remplacer(DelimiterType," ",""),"", 1, 1)Fin SiDélimiteurCount = 0Pourje = 1ÀLen(Destination.Valeur)SiInStr(i, Destination.Value, Remplacer(DelimiterType," ",""))AlorsDélimiteurCount = DélimiteurCount + 1Fin Si SuivantjeSiDélimiteurCount = 1Alors ' Supprimer le délimiteur du dernier caractèreDestination.Value = Remplacer (Destination.Value, DelimiterType,"") Destination.Value = Remplacer(Destination.Value, Remplacer(DelimiterType," ",""),"")Fin Si Fin Si Fin SiApplication.EnableEvents =VraiApplication.ScreenUpdating =Vrai Fin SiexitError : Application.EnableEvents =VraiFin SousPrivé SousFeuille de travail_SelectionChange(ParValCibleCommeGamme)Fin Sous
La démo ci-dessous montre comment fonctionne la liste déroulante à sélection multiple avec fonctionnalité de suppression dans Excel. Les utilisateurs peuvent sélectionner plusieurs options dans la liste de validation des données et effectuer des ajustements à la volée. Une approche rationalisée et efficace pour gérer les sélections !
Comment créer une liste déroulante à sélection multiple avec un délimiteur personnalisé
Le caractère qui sépare les éléments de la sélection est défini dans leType de délimiteurparamètre. Dans tous les codes, la valeur par défaut de ce paramètre est ", " (une virgule et un espace) et il se situe à la ligne 7. Pour utiliser un séparateur différent, vous pouvez remplacer " " par le caractère souhaité. Par exemple:
- Pour séparer les éléments sélectionnés par un espace, utilisez DelimiterType = " ".
- Pour séparer par un point-virgule, utilisez DelimiterType = "; " ou DelimiterType = ";" (avec ou sans espace, respectivement).
- Pour séparer avec une barre verticale, utilisez DelimiterType = " | ".
Par exemple, si vous remplacez le délimiteur par une barre oblique verticale, votre liste de sélection à sélection multiple ressemblera à ceci :
Comment créer une liste déroulante avec plusieurs sélections sur des lignes distinctes
Pour obtenir chaque sélection sur une ligne distincte dans la même cellule, définissezType de délimiteuràVbcrlf. Dans VBA, il s'agit d'une constante pour les caractères de retour chariot et de saut de ligne.
Plus précisément, vous modifiez cette ligne de code :
Type de délimiteur = ","
à celui-ci :
Type de délimiteur = vbCrLf
Par conséquent, chaque élément que vous sélectionnez dans la liste déroulante apparaîtra dans une nouvelle ligne :
Comment créer une liste déroulante à sélection multiple pour des colonnes, des lignes et des cellules spécifiques
Tous les codes décrits dans ce didacticiel fonctionnent sur une feuille entière. Cependant, vous pouvez facilement modifier n'importe lequel des codes, de sorte qu'il ne s'applique qu'à des cellules, lignes ou colonnes spécifiques selon vos besoins. Pour cela, retrouvez cette ligne de code :
Si rngDropdown n'est rien, alors GoTo exitError
Ajouter immédiatementaprèsdessus, ajoutez une nouvelle ligne spécifiant où autoriser les sélections multiples, comme expliqué dans les exemples ci-dessous.
Liste déroulante à sélection multiple pour des colonnes spécifiques
Pour permettre la sélection de plusieurs éléments dans uncertaine colonne, ajoutez ce code :
Si ce n'est pas Destination.Column = 4, alors GoTo exitError
Où "4" est le numéro de la colonne cible. Dans ce cas, la liste déroulante à sélection multiple ne sera activée que dans la colonne D. Dans toutes les autres colonnes, les listes déroulantes seront limitées à une seule sélection.
Ciblerplusieurs colonnes, précisez leurs numéros à l'aide de ce code :
Si Destination.Column <> 4 et Destination.Column <> 6 alors GoTo exitError
Dans ce cas, la liste déroulante à sélection multiple sera disponible dans les colonnes D (4) et F (6).
Liste déroulante multi-sélection pour certaines lignes
Pour insérer plusieurs listes déroulantes dans unligne spécifique, utilisez ce code :
Si ce n'est pas Destination.Row = 3, alors GoTo exitError
Dans cet exemple, remplacez « 3 » par le numéro de ligne dans lequel vous souhaitez activer les listes déroulantes à sélection multiple.
Ciblerplusieurs lignes, Le code est comme suit:
Si Destination.Row <> 3 et Destination.Row <> 5 alors GoTo exitError
Où « 3 » et « 5 » sont les lignes dans lesquelles la sélection de plusieurs éléments est autorisée.
Sélections multiples dans des cellules spécifiques
Pour activer plusieurs sélections danscellules particulières, ajoutez l'une des lignes de code ci-dessous.
Pour ununicellulaire:
Si ce n'est pas Destination.Address = "$D$3" Then GoTo exitError
Pourplusieurs cellules:
Si Destination.Address <> "$D$3" et Destination.Address <> "$F$6" Alors GoTo exitError
N'oubliez pas de remplacer "$D$3" et "$F$6" par les adresses de vos cellules cibles.
Comment activer la fonctionnalité de sélection multiple dans une feuille protégée
Pour faire fonctionner une liste déroulante à sélection multiple dans une feuille protégée, vous devez effectuer deux actions.
1. Ajoutez du code pour supprimer et restaurer la protection
Avant la ligne de code suivante :
Définir rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
Insérez ce code :
ActiveSheet.Unprotect Mot de passe :="mot de passe"
Cela supprime la protection de la feuille avant d'exécuter le code principal.
Pour rétablir la protection, en fin de code, avant cette ligne :
erreur de sortie :
Ajoutez ce code :
Mot de passe ActiveSheet.Protect :="mot de passe"
N'oubliez pas de remplacer « mot de passe » par le mot de passe que vous avez utilisé pour protéger la feuille.
2. Déverrouillez les cellules déroulantes
Assurez-vous que les cellules dans lesquelles vous souhaitez la fonctionnalité de sélection multiple sontNon verrouillé. Pour les instructions détaillées, voirComment déverrouiller certaines cellules sur une feuille protégée.
Note.Veuillez noter que l'inclusion de votre vrai mot de passe dans le code peut entraîner un risque de sécurité. Pour garantir la sécurité de votre classeur, stockez-le dans un emplacement sécurisé protégé contre tout accès ou utilisation non autorisé.
Et voilà : une liste déroulante Excel avec plusieurs sélections. L'ajout de cette fonctionnalité géniale à vos feuilles de calcul augmentera la précision de votre saisie de données et vous aidera à accomplir votre travail plus rapidement !
Cahier d'exercices à télécharger
Liste déroulante multi-sélection - exemples(fichier .xlsm)
Vous pourriez également être intéressé par
- Comment créer une liste déroulante avec de la couleur dans Excel
- Validation de données Excel personnalisée avec des formules
- Comment modifier, copier et supprimer la liste déroulante
- Créez facilement une liste déroulante dépendante dynamique dans Excel
- Liste déroulante dépendante pour plusieurs lignes dans Excel
