Les 3 types de menus déroulants d’Excel
Aujourd’hui nous allons voir ensemble comment optimiser et sécuriser les informations saisies par des utilisateurs dans une feuille de calcul d’Excel, en utilisant les menus déroulants d’Excel. Nous verrons tout au long de ce tutoriel qu’il existe trois méthodes pour insérer un menu déroulant, chacune de ces méthodes ayant des avantages et des inconvénients que nous détaillerons également. Beaucoup de personnes ont pour habitude d’utiliser la première méthode que nous allons voir juste après, en revanche, la troisième méthode est inconnue de nombreuses personnes, alors que celle-ci s’avère être extrêmement puissante ! Alors suivez bien ce tutoriel jusqu’au bout, car je vous réserve une surprise pour la fin !
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous :
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
1. Présentation du cas
Pour illustrer ce tutoriel, nous allons partir d’un exemple très simple : nous disposons d’une liste composée de cinq pays, et nous souhaitons pouvoir choisir l’un d’entre eux en utilisant un menu déroulant, afin de gagner de précieuses secondes, et également de sécuriser la saisie en évitant toute ressaisie manuelle inutile.
L’objectif va être de récupérer la valeur choisie au sein d’un menu déroulant, pour l’afficher dans une cellule, en utilisant trois méthodes successives, de la plus simple à la plus élaborée :
Avant d’entrer dans le vif du sujet, nous allons commencer par donner des noms aux plages de cellules, ce qui va nous permettre par la suite de gagner du temps, et de la clarté dans la création des menus déroulants :
- « _source » : correspond à la plage des pays :
- « _m1 », « _m2 » et « _m3 » : correspond aux trois cellules dans lesquelles nous allons vouloir saisir les valeurs sélectionnées dans le menu déroulant
Pour donner un nom à une plage de cellules, il suffit de sélectionner cette dernière, puis de saisir le nom désiré dans la zone des noms de plage en haut à gauche de la fenêtre Excel.
J’ai pour habitude de saisir un underscore au début des noms de plages, cela n’est absolument pas obligatoire, mais permets de retrouver celles-ci facilement dans les formules, en commençant simplement en saisissant ce symbole :
2. Utilisation de la validation des données
La validation des données d’Excel permet de définir des règles strictes de contrôle de la validité des données saisies par un utilisateur au sein de cellules d’une feuille de calculs.
Nous avons déjà eu l’occasion d’étudier dans le détail son fonctionnement dans un tutoriel précédant, que vous pourrez retrouver en cliquant ici.
Dans le cadre de cet article, nous allons nous intéresser exclusivement à la validation de cellules saisies dans une liste :
- S’agissant d’une méthode qui permet l’insertion directe du menu déroulant dans la cellule, nous commençons par sélectionner celle-ci :
- Puis pour insérer la validation de données, nous nous rendons dans le menu Données du ruban, puis nous cliquons sur le bouton Validation des données :
- Dans la fenêtre qui s’affiche, nous choisissons d’autoriser les données contenues dans une liste :
- Ensuite, nous vérifions que la case Liste déroulante dans la cellule est bien cochée, et nous saisissons le nom qui correspond à la plage des cellules contenant les noms de pays dans la zone Source :
- Et enfin, nous validons en appuyant sur le bouton [OK] !
Maintenant, lorsque nous sélectionnons la cellule _m1, un bouton apparaît sur la droite, afin de pouvoir dérouler le menu déroulant !
Cette première méthode d’insertion d’un menu déroulant dans une feuille de calcul est très simple à mettre en place. De plus, s’agissant d’un paramètre de la cellule, il suffit de copier puis de coller la cellule, pour que le menu déroulant soit lui aussi dupliquer ! Cela s’avère très pratique pour utiliser des menus déroulants, directement en tant que choix de cellule dans un tableau !
En revanche elle souffre également de deux défauts principaux :
- Le premier d’entre eux vient du fait que comme nous venons de le voir, il s’agit d’un paramètre de la cellule, il n’existe donc pas de lien direct avec les outils de développement de macro-commande (contrairement aux deux méthodes que nous allons voir juste après, qui sont de fait beaucoup plus puissantes !). Ici, pour lancer automatiquement une macro au changement de la valeur sélectionné dans le menu déroulant, il faut donc instancier une procédure évènementielle,
- Le second inconvénient de cette méthode vient du fait que le bouton d’identification ne reste pas visible lorsque la cellule n’est plus sélectionnée,
3. Création d’un Contrôle de formulaire
Ici, nous allons voir comment créer objet de type Contrôle de formulaire dans la feuille de calcul afin d’insérer un menu déroulant.
Tout d’abord, il est nécessaire d’afficher l’onglet Développeur du menu ruban. Ce menu qui n’est pas présent par défaut permet notamment de gérer les macro-commandes en VBA, d’utiliser des contrôleurs de formulaire (comme nous allons le voir à l’instant), ou encore des contrôleurs ActiveX.
Pour l’afficher, rendez-vous dans Fichier > Options, puis dans la rubrique Personnaliser le ruban, cochez la case Développeur dans la zone Onglets principaux. Enfin, pensez à valider en appuyant sur le bouton [OK] :
À présent, nous allons pouvoir insérer le menu-déroulant, en nous rendant dans l’onglet Développeur > groupe Contrôles > bouton Zone de liste déroulante :
Le curseur prend alors la forme d’une petite croix, ce qui nous permet de dessiner le menu déroulant à l’endroit désiré au sein de la feuille de calculs :
Pour définir le paramétrage de ce menu déroulant, nous effectuons un clic-droit sur ce dernier afin de choisir Format de contrôle… :
Cela permet d’afficher la fenêtre de réglage du contrôle :
- Dans la zone d’entrée, nous pouvons simplement saisir le nom de la plage des cellules sources,
- Dans la zone Cellule liées, nous allons sélectionner une cellule de la feuille de calcul qui ne contient aucune information. En effet, le contrôle de formulaire du menu déroulant va retourner dans la cellule que nous allons sélectionner ici le numéro de la position de la cellule sélectionnée au sein de la liste des données sources (par exemple, celui-ci va nous retourner « 1 » si nous sélectionnons l’Allemagne, « 4 » pour la France)
- Nombre de ligne permet simplement de spécifier combien d’entrées sont disponible dans la base des données source
Lorsque l’objet a été créé, nous pouvons tester différentes saisies afin de nous rendre compte du comportement que nous venons de décrire de celui-ci :
La dernière étape pour récupérer le résultat effectivement sélectionné par l’utilisateur au lieu du numéro de celui-ci est donc d’utiliser une formule INDEX() :
=INDEX(_source;C12)
Le contrôle de formulaire, est un objet indépendant, ce qui présente l’avantage de pouvoir le déplacer librement dans la feuille de calcul.
En revanche, celui-ci nécessite d’immobiliser au moins deux cellules et de créer une formule pour pouvoir récupérer son résultat, ce qui n’est pas le cas de la dernière méthode que nous allons voir maintenant !
4. Création d’un objet ActiveX
L’ultime méthode que nous allons maintenant mettre en place consiste à insérer dans la feuille de calcul un objet ActiveX.
Tout comme pour l’insertion d’un contrôle de formulaire, nous allons ici passer par l’onglet Développeur, à la différence que cette fois-ci nous allons sélectionner l’outil Zone de liste déroulante du Contrôle ActiveX :
Après avoir inséré celui-ci dans la feuille de calcul, il est nécessaire d’activer le Mode Création, qui normalement est activé par défaut après l’insertion d’objet ActiveX. Si ce n’est pas le cas, cliquez simplement sur le bouton Mode Création toujours depuis l’onglet Développeur :
Puis nous allons modifier les propriétés de l’objet en sélectionnant ce dernier > Propriétés (depuis l’onglet Développeur) :
Excel affiche alors une fenêtre avec toutes les propriétés de l’objet sélectionné, mais seules deux nous intéressent :
- LinkedCell : permet de spécifier dans quelle cellule nous souhaitons insérer le résultat (ici la cellule nommée « _m3 »),
- ListFillRange : permet de spécifier les cellules contenant les données à afficher dans le menu déroulant
Pour tester le contrôleur, il suffit de quitter le Mode Création, en appuyant sur le bouton correspondant de l’onglet Développeur :
Encore deux petites précisions qui montrent à quelle point ce contrôleur peut s’avérer être puissant :
Par défaut la propriété Style a pour valeur fmStyleDropDownCombo ce qui peut permettre de saisir une donnée qui n’est pas présente dans la liste des données d’origine (la cellule liée se met alors à jour en temps réel !) :
Pour transformer le contrôleur en un menu déroulant classique, il suffit de modifier la propriété Style par : fmStyleDropDownList :
De plus, il est possible d’avoir la main sur les macros commandes VBA directement à partir de l’objet ActiveX ! Pour cela, double cliquez sur celui-ci lorsque le Mode Création est activé afin de lancer directement l’outil VBA de développement des macros commandes Excel, avec l’évènement Change d’instancié :
Il suffit alors de saisir une commande pour que celle-ci se lance automatiquement lorsqu’un utilisateur utilise le menu déroulant :
Ce qui donne alors le résultat suivant :
Et surprise, voici la macro-commande qui permet de récupérer la capital d'un pays lors de sa sélection :
Private Sub ComboBox1_Change()
Dim capitale As String
If ComboBox1 = "Allemagne" Then
capitale = "Berlin"
ElseIf ComboBox1 = "Belgique" Then
capitale = "Bruxelles"
ElseIf ComboBox1 = "Espagne" Then
capitale = "Madrid"
ElseIf ComboBox1 = "France" Then
capitale = "Paris"
ElseIf ComboBox1 = "Italie" Then
capitale = "Rome"
End If
MsgBox "La capitale de : " & ComboBox1 & " est " & capitale
End Sub
Article publié initialement le 16 septembre 2018, et modifié le 18 juin 2019