Liste déroulante à choix multiples 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 :


Listes déroulantes aux choix multiples

Cumuler plusieurs choix dans une même liste déroulante en VBA Excel est tout à fait possible.



Sur l'exemple illustré par la capture, l'utilisateur doit construire deux équipes à l'aide de deux listes déroulantes. Il ajoute les membres un à un. Bien sûr, le code VBA Excel veille au grain. Il est bien évidemment interdit d'ajouter deux fois un même membre dans une même équipe. De plus et fort logiquement, un membre déjà intégré dans une équipe ne peut être choisi dans l'autre.

Classeur Excel à télécharger
Pour développer cette solution, nous suggérons d'appuyer l'étude sur un classeur Excel offrant déjà ces listes déroulantes.

• Télécharger le classeur 24-liste-deroulante-cumuler-choix.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é,

Les membres à choisir sont identifiés en colonne C. Deux listes déroulantes existent en effet en cellules respectives E4 et E8. Mais à ce stade, elles réagissent classiquement. Elles ne permettent pas encore de cumuler les choix. Elles remplacent un membre par un autre.

Code VBA aux choix dans les listes
Nous devons construire un code VBA capable de se déclencher dès qu'une valeur est choisie dans l'une ou l'autre liste déroulante. Pour cela et nous le savons, nous devons créer la procédure événementielle Worksheet_Change de manière à détecter qu'elles ont été les cellules impactées.

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

Ainsi, nous affichons sa feuille de code, vierge pour l'instant, au centre de l'écran.

• En haut de cette feuille de code, déployer la liste déroulante de gauche,
• Puis, choisir l'élément Worksheet,

Cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange. Ce n'est pas celle qui nous intéresse. Le code VBA ne doit pas se déclencher au changement de sélection, mais au changement de valeur.

• Déployer alors la liste déroulante de droite,
• Dans les propositions, choisir l'événement Change,

Cette fois, nous créons la procédure Worksheet_Change. C'est bien celle qui nous intéresse. Donc, la précédente peut être effacée.

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

Private Sub Worksheet_Change(ByValTarget As Range)
Dim valeurAvt As String: Dim valeurAps As String

End Sub

Il s'agit de deux chaînes de texte (As String). Leur but est de comparer la cellule impactée avant et après modification pour pouvoir réaliser le cumul. Nous allons très vite le comprendre.

Vérifier les cellules modifiées
Pour optimiser les ressources, nous ne devons déclencher ce code VBA que lorsque l'une des deux cellules (E4 ou E8) a été modifiée.

• Pour cela, créer cette instruction conditionnelle à la suite du code VBA :

...
If Target.Address = "$E$4" Or Target.Address = "$E$8" Then

End If
...

L'objet Target passé en paramètre de la procédure représente la cellule modifiée. Grâce à sa propriété Address, nous vérifions bien qu'il s'agit de la cellule E4 ou (Or) de la cellule E8, soit de l'une ou l'autre liste déroulante.

Valeurs avant et après modification
C'est maintenant que nous devons réceptionner les valeurs contenues dans la cellule impactée, avant et après modification.

• Dans l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
valeurAps = Target.Value
Application.EnableEvents = False
Application.Undo
valeurAvt = Target.Value
...

Dans la variable valeurAps, nous prélevons le choix de l'utilisateur. Celui-ci se cumulera aux précédents grâce au code qui va suivre. Ensuite, nous coupons le déclenchement des événements. En effet, maintenant que le nouveau cumul est stocké en variable, nous annulons l'action de l'utilisateur (Undo). Ce changement ne déclenche temporairement plus ce code VBA. Nous retrouvons donc la cellule dans son état (cumul) précédent. Nous prélevons les valeurs qu'elle portait dans la variable valeurAvt cette fois. C'est ainsi que nous allons pouvoir établir les comparaisons.

Tester si le choix avait déjà été fait
Maintenant, nous devons nous assurer que le membre choisi par l'utilisateur n'avait pas déjà été sélectionné dans l'une ou l'autre liste déroulante. Pour cela, il faut rechercher la valeur choisie dans la chaîne cumulée précédente.

• A la suite dans l'instruction conditionnelle, ajouter le double test suivant :

...
If InStr(1, [E4].Value, valeurAps) = 0 And InStr(1, [E8].Value, valeurAps) = 0 Then
Target.Value = valeurAvt & ", " & valeurAps
If Left(Target.Value, 2) = ", " Then Target.Value = Mid(Target.Value,2)
End If
...

La fonction InStr permet de trouver l'occurrence de la valeur cherchée (Le choix utilisateur valeurAps) dans une chaîne (Le cumul précédent E4 ou E8). Si le choix de l'utilisateur n'est pas trouvé (=0) dans les valeurs cumulées, alors le choix est assemblé avec les autres, moyennant une virgule de séparation suivie d'un espace (& ", "). En revanche, lorsqu'il s'agit du premier choix, la variable valeurAvt est vide, donc la virgule et son espace subsistent en valeur résiduelle en préfixe de la chaîne. C'est pourquoi nous testons si elle commence par cette succession de deux caractères grâce à la fonction Left (Left(Target.Value, 2) =", "). Si la réponse est positive, nous les éliminons en prélevant deux caractères plus loin, grâce à la fonction Mid.

Pour finir, il ne faut pas oublier de remettre en marche l'exécution des gestionnaires d'événements, sans quoi, les prochains cumuls ne pourront se faire puisque les listes déroulantes seront incapables de réagir. Pour ce cela, il suffit d'inverser la valeur de la propriété que nous avons réglée en début de code.

• A la toute fin du code, après le dernier End If, ajouter l'instruction VBA suivante :

...
End If

Application.EnableEvents = True
End Sub
...

Cumuler les choix
Il est temps de tester ce code VBA.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Avec la première liste déroulante en E4, réaliser un premier choix,

Le membre est parfaitement ajouté dans la cellule. Si vous réitérez ce même choix, vous constatez que la transaction est refusée. En conséquence, le membre n'est pas dupliqué. Si vous réalisez des choix différents, ils sont tous ajoutés les uns à la suite des autres dans la cellule de la liste déroulante.



Si vous choisissez un membre déjà pris, avec la seconde liste déroulante, le choix est refusé. En revanche, si vous cumulez les demandes sur des membres qui ne sont pris ni dans la première zone, ni dans la seconde, les choix multiples se réalisent.