Listes déroulantes liées sur toutes les lignes du tableau

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 dépendantes sur chaque ligne du tableau

Nous avons déjà appris à créer des listes déroulantes reliées entre elles. Pour cela, nous avions bâti des calculs intermédiaires de repérage. Et sur la base des numéros incrémentés générés, nous avions produit l'extraction des données dépendantes, servant de source liée.



Mais lorsqu'il s'agit de proposer des listes déroulantes dépendantes sur chaque ligne de la base de données, le problème se corse. Dans ce contexte, il n'est pas envisageable de prévoir des calculs de repérage et d'extraction pour un nombre indéfini d'enregistrements. Et pourtant, cette fonctionnalité est précieuse dans le cas des tableaux de prospections par exemple. C'est pourquoi, nous apportons la solution dans cette formation.

Source et présentation de la problématique
Pour établir ces travaux, nous proposons de réceptionner un fichier Excel hébergeant des données et offrant certains précieux réglages.

• Télécharger le classeur listes-sur-chaque-ligne.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons un classeur composé des feuilles Prospection et Villes. La feuille Prospection dresse un tableau servant à archiver les nouveaux prospects au coup par coup. C'est en fonction du code postal saisi en colonne E que nous devons offrir une liste déroulante des villes adaptées en colonne F. Cette liste déroulante doit être présente pour chaque enregistrement et sur chaque nouvelle ligne de la base de données, au fil de l'implémentation. Chacune doit se nourrir en fonction de son code postal renseigné. Et c'est bien là toute la problématique.

En cellule I6, vous notez la présence d'une zone pour réceptionner un code postal de référence. C'est en fonction de cette valeur que des repérages et des extractions existent déjà.

• En bas de la fenêtre Excel, cliquer sur l'onglet Villes pour afficher sa feuille,

Cette feuille héberge la base de données des codes postaux et des villes associées pour la région PACA.



Un calcul de repérage par numéros incrémentés est présent en colonne E :

=SI(Prospection!$I$6=D3; MAX($E$2:E2)+1; '')

Nous maîtrisons désormais ces techniques. Si la correspondance est établie avec le code postal de référence issu de la feuille Prospection, un marquage s'opère. Et ce marquage est exploité en colonne H pour produire l'extraction, grâce aux fonctions Index et Equiv:

=SIERREUR(INDEX(C:C; EQUIV(LIGNE(A1); E:E; 0); 1); '')

Ces villes constituent la source de données dynamique des listes déroulantes du tableau des prospects.

Prélever l'information d'une cellule au clic
Pour que chaque liste déroulante puisse se nourrir des villes associées au code postal de l'enregistrement en cours, nous devons être en mesure de prélever ce code en cellule I6 de la feuille Prospection. Et ce prélèvement doit intervenir quand une cellule de la colonne F est activée. Ainsi, au moment de la demande utilisateur pour exploiter la liste déroulante, le contenu se regénèrera automatiquement.

Cependant, le recalcul automatique se génère automatiquement à chaque modification de cellule dans Excel, mais pas à la sélection. C'est pourquoi, nous avons besoin d'une instruction VBA triviale à déclencher sur un évènement précis. Nous avions d'ailleurs développé cette technique à l'occasion de la conception du jeudu Memory pour Excel. Ensuite, nous pourrons refermer cette parenthèse et nous concentrer sur les formules.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projet, double cliquer sur l'élément Feuil1 (Prospection),
• Au-dessus de la feuille de code, déployer la liste déroulante de gauche,
• Choisir l'objet Worksheet,

De cette manière, nous créons la procédure événementielle Worksheet_SelectionChange. Son code se déclenchera à chaque changement de sélection (SelectionChange) sur la feuille Excel (Worksheet) Prospection.

• Entre les bornes de la procédure événementielle, ajouter l'instruction VBA suivante :

Application.Calculate

La méthode Calculate de l'objet Application ordonne simplement le recalcul de toute la feuille. Et ce recalcul interviendra désormais au changement de sélection.

Pour préserver les ressources, le code suivant est encore plus approprié :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then
Application.Calculate
End If
End Sub

Une condition est posée sur la colonne cliquée. Cette information nous est transmise grâce à la propriété Column de l'objet Target passé en paramètre de la procédure. De cette façon, nous engageons le recalcul uniquement si la colonne cliquée est celle des listes déroulantes (6=F).

• Enregistrer les modifications (CTRL + S),
• Fermer l'éditeur VBA Excel,
• Puis, revenir sur la feuille Prospection,
• En cellule I6, taper la formule suivante et la valider :

=SI(CELLULE('colonne')=6; INDIRECT(ADRESSE(CELLULE('ligne'); 5)); '')

Nous exploitons la fonction conditionnelle pour engager la réception de la donnée dans la mesure où la cellule cliquée par l'utilisateur appartient bien à la colonne F. Cette colonne est la sixième. Cette information nous est retournée par la fonction Cellule avec le paramètre colonne. Dès lors, grâce à la fonction Excel Adresse, nous prélevons l'information située sur la ligne en cours pour la colonne précédente (5), soit celle des codes postaux. Mais comme vous le savez, la fonction Adresse retourne les coordonnées de la cellule ainsi recomposée. Pour récupérer son contenu, nous l'englobons dans la fonction Indirect. Elle permet d'interpréter l'information qui lui est passée en argument, soit le contenu de la cellule ainsi désignée.

• Cliquer sur l'une des villes de la colonne F,

Aussitôt, son code postal est rapatrié en cellule I6 grâce à ce calcul et à la gestion de l'événement par le code VBA.



Si vous affichez la feuille Villes sans cliquer sur une autre cellule, vous notez que la zone d'extraction propose bien toutes les villes associées à ce code postal réceptionné.

Listes déroulantes des données extraites
C'est donc de cette zone d'extraction dont doivent se nourrir les listes déroulantes de la colonne F. A chaque clic, leur contenu s'adaptera au code postal correspondant pour fournir les villes associées. Mais le nombre de ces villes varie en fonction du code postal. Donc, la hauteur de la liste déroulante doit s'adapter à ce contenu. Pour cela, nous devons premièrement nommer cette plage d'extraction. Ensuite, nous devons retravailler ce nom grâce à la fonction Decaler notamment. Elle permettra d'ajuster la hauteur des listes déroulantes aux contenus.

• Sélectionner la cellule H3 de la feuille Villes,
• En haut à gauche de la feuille, saisir l'intitulé suivant dans la zone Nom: Villes,
• Valider nécessairement ce nom par la touche Entrée du clavier,

Les données extraites disparaissent. La validation a ordonné le recalcul. Ce phénomène est donc tout à fait normal. A ce stade, notre nom ne fait référence qu'au point de départ de la plage d'extraction. Nous devons le retravailler pour le faire grandir avec le contenu.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue qui suit, sélectionner le nom Villes,
• Dans la zone Fait référence à, adapter la syntaxe comme suit :

=DECALER(Villes!$H$3; 0; 0; NB.SI(Villes!$H:$H; '>*<')-1)

• Cliquer sur le bouton à la coche verte pour valider la syntaxe,
• Puis, cliquer sur le bouton Fermer de la boîte de dialogue,

En premier argument de la fonction Decaler, nous indiquons le point de départ de la plage, soit la cellule H3, le début de l'extraction des villes. Les deux arguments suivants sont réglés à zéro. Ils définissent les potentiels décalages en ligne et colonne à observer. Dans notre cas, il n'y en a aucun. Le quatrième argument est fondamental. C'est lui qui permet de régler la hauteur variable de la plage grâce à la fonction Nb.SI. Elle dénombre les cellules non vides et ne portant pas de calcul conduisant à un résultat vierge grâce au critère '>*' appliqué sur la colonne H. Cette dernière doit être cliquée par son étiquette pour l'intégrer correctement dans la syntaxe.

Listes dépendantes de base de données
Il est temps de rendre opérationnelles ces listes déroulantes des villes pour chaque enregistrement et nouveau prospect. Nous suggérons de les paramétrer sur un nombre suffisamment important de lignes. Leur source de données est commune à toutes. Il s'agit de la plage dynamique Villes que nous venons de rendre extensible par calcul. Et bien qu'il s'agisse de la même source, ces listes proposeront un contenu différent, adapté au code postal de l'enregistrement.

• En bas de la fenêtre Excel, cliquer sur l'onglet Prospection pour revenir sur sa feuille,
• Sélectionner une grande plage pour construire les listes des villes, par exemple : F6:F100,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la zone Autoriser de la boîte de dialogue qui suit, choisir Liste,
• Dans la zone Source juste en-dessous, saisir la syntaxe suivante : =Villes,

C'est ainsi que nous établissons la liaison entre le contenu des listes déroulantes sur la plage définie et la zone d'extraction variable et dépendant du code postal pour la ligne cliquée. En effet, Villes est bien le nom que nous avons attribué à cette plage et que nous avons ensuite retravaillé par formule.



Ainsi configurée, la liste n'acceptera aucune saisie non prévue. Pour la débrider, vous pouvez vous référer à la formation sur les listes de choix non limitées.

• Cliquer sur le bouton Ok de la boîte de dialogue pour créer ces listes,
• Valider le message d'alerte qui suit,

Excel juge en effet la source comme erronée. En l'absence de données puisque le code postal de référence n'est pas rapatrié, la source lui paraît incohérente.

• Cliquer sur la ville de Belgentier en cellule F9,
• Puis, déployer sa liste déroulante,

Comme vous pouvez le voir, toutes les villes associées au code postal devenu la référence sont proposées. Si vous cliquez sur la ville de Castellane juste en dessous, en déployant sa liste, les suggestions s'adaptent parfaitement et précisément.



Si vous créez un nouveau prospect, au déploiement de sa liste déroulante des villes, son contenu s'auto génère en fonction du code postal saisi. Nous avons donc bâti un outil précieux déclinable dans bon nombre de contextes exploitant des bases de données avec des informations dépendantes.

Remarque : La liste déroulante pour la première ville ne fonctionne pas. La raison est simple. Les codes postaux du département du Vaucluse (84) sont absents de cette source de données.

Pour rappel, la solution que nous avons aboutie tient en plusieurs points :

• Un événement VBA permet le recalcul au clic sur une cellule,
• Grâce à lui, une formule exploitant les fonctions Cellule et Adresse rapatrient le code postal,
• Sur ce dernier, un calcul de repérage est effectué sur la feuille Villes,
• Sur ces numéros incrémentés, une extraction est produite grâce aux fonctions Index et Equiv,
• La plage de cette extraction est rendue dynamique grâce aux fonctions Decaler et Nb.Si,
• Le nom de cette plage est enfin utilisé comme source pour toutes les listes de la base.