Zone liste déroulante et insertion-suppression de lignes
Bonjour à tous :
J'ai un problème sur un fichier .xlsm d' Excel 2016 :
- problème présent sur 500 cellules de listes déroulantes sur 25 feuilles
- listes déroulantes de 200 intitulés
Il faudrait que l'insertion ou la suppression de lignes d'intitulés dans une de 'Zone liste déroulante' ne changent pas les articles précédemment choisis (sur 500 autres cellules de listes déroulantes sur 25 feuilles), mais que le nombre représentant leur position dans la liste reflète leur nouvelle position et y soit lié.
- J'ai une liste de 200 intitulés
- J'ai créé 500 zone de liste déroulante (et non pas une simple liste déroulante créée avec les validations de données)
- La liste des intitulés sert comme plage d'entrée.
- La cellule liée peut être n'importe quelle cellule vide d'une feuille.
Le problème, c'est que lorsque j'insère ou supprime une ou plusieurs ligne(s) dans la liste des intitulés, le choix que j'avais fait dans la zone de liste déroulante se trouve décalé et ne reste pas fixe car la valeur de la cellule liée, elle, reste fixe...
J'aimerais savoir s'il existe un moyen pour que le choix fait dans la zone de liste déroulante reste fixe, peu importe si on insère ou supprime des lignes, mais que le nombre représentant leur position dans la liste reflète leur nouvelle position et y soit lié....
J'ai essayé de chercher des solutions par moi-même mais je ne trouve pas exactement une solution adaptée mon soucis....
Je joins un fichier Excel 2016 "Bug liste deroulante.xlsm" d' 1 feuille.
Merci d'avance pour vos réponses.
Heu... personne pour me donner une solution ou une piste de recherche ?
Ai-je été assez clair dans ma demande d'aide ?
Merci
Bonjour,
Pour une liste et 3 ou 4 zones combinées (de formulaire), une solution serait vite trouvée...
Je n'ai aucune envie de proposer une solution si je ne suis pas assuré préalablement qu'elle est réelle, c'est à dire qu'une seule et même procédure s'appliquera à l'ensemble des listes et zones du classeur sans qu'on ait à la multiplier.
Et pour aller un peu plus loin dans le raisonnement, c'est sans doute avant de flanquer 500 contrôles dans le classeur qu'il aurait fallu rechercher une solution plus économique à gérer...
Là, tu nous proposes de travailler sur un échantillon à une solution dont on peut augurer qu'elle ne va pas spontanément s'adapter à ton ensemble
Cordialement.
@ MFerrand:
Bonjour, et en préliminaire, merci de la réponse.
Ce ne sont pas des zones combinées, juste des zones déroulantes simples.
Pour moi, la présence de la flèche de la zone déroulante est visuellement importante.
La liste appelée par la zone déroulante est amenée à être modifiée : ajout, suppression, ... mais surtout des aouts
Le nombre de zones déroulantes n'est pas non plus gênant, c'est le même problème qui se répète 25 fois par feuille sur 25 feuilles.
On résout un seul problème et tous les autres sont réglés de la même manière.
Il n'y aurait qu'une feuille ce serait pareil, donc simplifions et disons qu'il n'y a plus qu'une feuille et 2 cellules de listes déroulantes.
Le problème est que si on ajoute un article dans la base des articles défilant dans la zone déroulante,
Le précédents choix des autres zones déroulantes faisant appel à la même liste sont modifiés et les précédents choix d'articles changent, car la liste déroulante fait référence à un numéro de position dans la liste et non à un article choisi dans cette liste.
La fonction 'RechercheV' me satisferait à condition de pouvoir choisir l'article à chercher "Valeur_cherchée" dans une zone de liste déroulante. Choix qui serait fixé jusqu'au prochain changement de sélection.
Si je choisis 'Validation des données' Liste ... mon problème est résolu ..
sauf que la flèche n'apparait plus dans la case et qu'il me faut d'abord cliquer sur la cellule pour faire apparaître la flèche,
puis sur la flèche de la liste et choisir l'article donc 2 clics au lieu d'un seul !
> gênant car double travail .. et je fais cela toute la journée !!
Honnêtement, entendre que cela te gêne d'avoir 2 clics après avoir mis 500 contrôles en place, je trouve que tu as bonne mine !
Une solution économique aurait été d'avoir un contrôle par feuille, amovible, apparaissant à la sélection d'une cellule destinée à la saisie en étant alors initialisée avec la liste correspondante, la sélection provoquant l'affectation de la valeur sélectionnée dans la liste et la disparition du contrôle.
La gestion des mouvements de contrôles étant gérée à partir d'une procédure d'évènement niveau classeur, SheetSelectionChange. Et pour les affectations de valeurs, les procédures Change des contrôles auraient renvoyé à une procédure unique d'affectation.
Le résultat aurait été un classeur beaucoup plus léger...
Là on en est à mettre des rustines...
Dans la mesure où tu as eu la mauvaise idée (en plus du nombre de contrôle) de choisir des Zones Combinées de formulaire au lieu d'ActiveX ComboBox, la solution de rattrapage consiste en cas d'ajout d'un élément dans la liste d'apporter un correctif aux valeurs des cellules liées : +1 si valeur >= au rang de l'élément ajouté. Et en cas de retrait d'un élément : -1 si valeur > au rang de l'élément retiré, et effacement si = à ce rang.
Il faut donc que les positions des cellules liées soient fixes sur les feuilles et que l'on puisse savoir la position de la liste en fonction de la position de ces cellules.
Il faut aussi que l'on puisse détecter les changements dans les listes, les listes en tableaux Excel judicieusement nommés y aideraient, mais à défaut cela sera un peu plus sportif...
Une autre solution serait d'automatiser les modifications de listes, mais les moyens qui auraient pu être utilisés avec des ComboBox ne marcheront pas avec les contrôles en place, et les moyens simples utilisables deviennent compliqués à mettre en oeuvre... Il ne resterait quasiment qu'un déclencheur manuel par liste, provoquant l'édition de la liste dans un Userform, où on la modifie, et à validation une procédure opère les modifications de la liste et apporte les correctifs nécessaires. On alourdirait un peu plus le classeur, mais pas trop à côté des 500 contrôles.
Bref, je crois que tu es loin d'être au bout de ta peine, et que la solution sera toujours de penser d'abord à tout avant de commencer à faire, car ensuite...
Cordialement.
Tu dis : Honnêtement, entendre que cela te gêne d'avoir 2 clics après avoir mis 500 contrôles en place, je trouve que tu as bonne mine !
Ø Honnêtement, 500 contrôles multiplié par 2 clics au lieu d'1 seul = 1000 clics au lieu de 500 !!le but de l'informatisation étant le gain de temps, cette solution est mauvaise de naissance.De plus, graphiquement c'est pas top, l'utilisateur ne voit pas de flèche pour l'inciter à cliquer sur la cellule.
Ø Microsoft aurait dû avoir la bonne idée pour Excel, de développer la fonction de 'zone de liste déroulante' avec une 'option' de suivi sur le nom de l'article de la liste, et non sur la position dans la liste, il y aurait moins de question sur le net à ce sujet.
Ø Ex : on choisit un article dans la 'zone de liste déroulante', et ce choix va faire une RECHERCHEV ou RECHERCHEH dans une matrice, et retourner des valeurs dans les colonnes ou lignes de la matrice.
Ø Une RECHERCHEV ou RECHERCHEH avec comme 'Valeur_recherchée', un élément choisi dans la 'zone de liste déroulante'.
Tu dis : Une solution économique aurait été d'avoir un contrôle par feuille amovible, apparaissant à la sélection d'une cellule destinée à la saisie en étant alors initialisée avec la liste correspondante, la sélection provoquant l'affectation de la valeur sélectionnée dans la liste et la disparition du contrôle.
La gestion des mouvements de contrôles étant gérée à partir d'une procédure d'évènement niveau classeur, SheetSelectionChange. Et pour les affectations de valeurs, les procédures Change des contrôles auraient renvoyé à une procédure unique d'affectation.
Le résultat aurait été un classeur beaucoup plus léger...
- Je préfère une cellule fléchée dans laquelle on clique, la liste apparait, on choisit une valeur qui va faire une RechercheV dans une matrice et renvoyer des valeurs.
- Microsoft n'y a pas pensé, si j'étais le boss, ce serait à régler rapidement car c'est un problème récurrent.
Tu dis : Là on en est à mettre des rustines…- Ok, mais c'est devant le problème que l'on cherche des solutions… avant on peut penser que sa solution marche …La solution 'zone de liste déroulante' est bonne… tant que l'on ne modifie pas la matrice : insertion, suppression.
Quand tu montes dans ton auto, tu penses qu'elle ne tombera pas en panne ..Si elle tombe en panne, tu trouves une solution.Le mécano ne reproche pas au conducteur de n'avoir pas son BTS mécanique.On doit s'adapter et non pas reprocher à celui qui essai .. de n'avoir pas penser à tout, ce n'est pas mon job de développer sur Excel … et je vois des développeurs qui cherchent comme moi, c'est comme ça qu'on progresse.
Tu dis : Dans la mesure où tu as eu la mauvaise idée (en plus du nombre de contrôle) de choisir des Zones Combinées de formulaire au lieu d'ActiveX ComboBox, la solution de rattrapage consiste en cas d'ajout d'un élément dans la liste d'apporter un correctif aux valeurs des cellules liées : +1 si valeur >= au rang de l'élément ajouté. Et en cas de retrait d'un élément : -1 si valeur > au rang de l'élément retiré, et effacement si = à ce rang.
- Tu imagines de faire cela manuellement à chaque ajout/suppression sur les 500 contrôles ? ou j'ai mal compris ..!
- J'ai eu la très bonne idée de commencer un développement sans être ingénieur développeur …
- On fait du vélo, on tombe, on se relève, on apprend toujours par l'échec.
Tu dis : Il faut donc que les positions des cellules liées soient fixes sur les feuilles …- La position des cellules liées sont connues et fixes sur la feuille, juste à droite de celle des 'zones de listes déroulantes
Tu dis : … et que l'on puisse savoir la position de la liste en fonction de la position de ces cellules.
- Là, je ne comprends pas.
Il faut aussi que l'on puisse détecter les changements dans les listes, les listes en tableaux Excel judicieusement nommés y aideraient, mais à défaut cela sera un peu plus sportif…
- On peut nommer des listes et détecter les changements de la matrice en question …
Tu dis positivement : Une autre solution serait d'automatiser les modifications de listes, mais les moyens qui auraient pu être utilisés avec des ComboBox ne marcheront pas avec les contrôles en place, et les moyens simples utilisables deviennent compliqués à mettre en œuvre... Il ne resterait quasiment qu'un déclencheur manuel par liste, provoquant l'édition de la liste dans un Userform, où on la modifie, et à validation une procédure opère les modifications de la liste et apporte les correctifs nécessaires. On alourdirait un peu plus le classeur, mais pas trop à côté des 500 contrôles.
- aurais-tu un exemple sur mon fichier joint, avec seulement 2 zones de listes déroulantes auxquelles on ajoute ou retranche des articles ?
- - avec des ComboBox ou autres solutions à ta convenance, oublie les 500 contrôles, les modifs seront de mon problème.
Cordialement et encore vraiment merci de t'être penché sur mon soucis …
Bonjour,
le but de l'informatisation étant le gain de temps, cette solution est mauvaise de naissance
Parce que tu ne compte pas la mise en place !
Microsoft aurait dû avoir la bonne idée pour Excel, de développer la fonction de 'zone de liste déroulante' avec une 'option' de suivi sur le nom de l'article de la liste
T'aurait-il échappé que pour les ComboBox c'est justement la valeur, soit le choix de l'utilisateur qui se retrouve dans la cellule liée, et non l'index de liste ?
NB- Le fonctionnement des contrôles n'utilise pas de fonction, RECHERCHEV ou autre (Heureusement !)
c'est devant le problème que l'on cherche des solutions…
Il convient de prévoir avant, y compris les modifications que l'on est susceptible d'apporter par la suite. Ce qui n'a pas été prévu au départ ne se retrouvera pas à l'arrivée. De plus, lorsque l'on aboutit à quelque chose de compliqué et lourd, on peut prévoir des difficulté de fonctionnement ultérieure, et donc chercher une autre solution dès le départ. Et passé un seuil, la solution et de reprendre la conception. On efface et on recommence !
Tu imagines de faire cela manuellement
Je ne parle à aucun moment d'opérer manuellement !
J'ai indiqué plusieurs pistes... mais cela reste des solutions toutes relatives dans la mesure où le principal problème est le nombre de contrôles...
Pour l'examiner de plus près et tester des solutions, il faut un classeur avec au moins 2 feuilles, qui contiennent autant de listes, contrôles et cellules liées que tes feuilles originales, et aux mêmes emplacements.
Cordialement.
Bonjour,
Poids du fichier, la perte de fluidité et un certain ralentissement inhérent aux fichiers lourds
>> Mon fichier de 5 Mo est fluide
T'aurait-il échappé que pour les ComboBox c'est justement la valeur, soit le choix de l'utilisateur qui se retrouve dans la cellule liée, et non l'index de liste ?
>> Visuellement, je souhaiterais une flèche dans la cellule qui ouvre la liste,
et un seul clic sur cette cellule pour ouvrir la liste déroulante
Il convient de prévoir avant, les modifications que l'on est susceptible d'apporter par la suite.
>> Tout évolue en temps réel, même les idées, c'est la vie ..
- Un nouvel élève qu'on ajoute à une classe
- Un fruit, une voiture, etc… que l'on ajoute à ses ventes …
-Mais c'est impératif dans mon cas
J'ai indiqué plusieurs pistes...
.. mais cela reste des solutions toutes relatives dans la mesure où le principal problème est le nombre de contrôles…
-Dans mon exemple joint, il y a peu de contrôles, ne reste pas bloqué sur le problème initial stp
Pour tester des solutions, il faut un classeur avec au moins 2 feuilles,
qui contiennent autant de listes, contrôles et cellules liées que tes feuilles originales, et aux mêmes emplacements.
-J'ai fait un nouvel exemple simplifié : "Bug3 liste deroulante.xlsm"
Cordialement.
Bonjour,
J'avais entrepris d'expliquer en quoi tes propos reposaient en partie sur des idées fausses portant sur les composants Excel, la programmation et les modalités d'automatisation... interrompue par une panne d'électricité intempestive qui a du même coup volatilisé mon message !
Trop long pour que je recommence, ce sera pour une autre occasion.
Dans l'immédiat, ce qui n'a pas dû t'échapper puisque tu me cites (NB- utiliser les balises Quote permettrait de distinguer les propos cités de tes propres commentaires
Coder sur un échantillon individualisé ne peut être d'aucune utilité lorsqu'il s'agit de coder une méthode d'identification d'un élément en cause et localiser à partir de là tous les éléments liés sur lesquels intervenir, ce à partir d'un élément déclencheur localisable.
Je renouvelle donc cette exigence si tu veux que je puisse te proposer quelque chose de fonctionnel.
Cordialement.
Panne d'électricité intempestive : je connais, ça fait bien craquer de tout perdre ... grrr !!
... depuis, j'utilise ONENOTE qui enregistre au fur et à mesure, en tache de fond, et je ne perds plus rien e mes écrits ...
Mon modèle est un exemple simplifié, mais 100% représentatif du cas réel,
afin que je puisse comprendre la manière dont il est résolu par quelqu'un d'autre,
pour qu'au final, je puisse apprendre et essayer de l'appliquer moi-même ...
sans doute avec quelques explications ..
ce qui pourrait servir à bien d'autres lecteurs.
Le but étant que j'apprenne, et non que l'on me fasse toute la correction ...
J'ai mis 2 feuilles et peu de contrôles mais les autres contrôles n'apporteraient rien de plus,
puisqu'ils sont les mêmes que ceux qui sont présents dans le modèle joint.
Tant pis si tu ne souhaites pas t'y investir, je ne t'en veux vraiment pas.
Ici, les gens aident s'ils le veulent bien et c'est bien comme cela.
Merci encore de m'avoir donné de ton temps.
C'était mal parti et mal arrivé
Si j'exige 2 feuilles, c'est que les listes sont réparties sur plusieurs feuilles et qu'il faut donc coder un repérage multifeuilles. Deux devraient normalement suffire pour écrire et tester le code... mais il convient qu'elles soient des plus ressemblantes aux feuilles originales pour pouvoir y définir de façon fiables des règles de repérage.
A partir de quoi le code doit pouvoir opérer en déterminant sur quelle feuille se trouve la liste modifiée, son emplacement, son étendue, et à partir de l'emplacement de la liste celui des cellules liées.
Une fois ces éléments définis le reste coule de source.
Les demandeurs n'étant, à quelques exceptions près, déjà pas en mesure d'adapter un code simple...
Le sens de ma proposition était de t'éviter de continuer à alourdir ton classeur, ce qui, à défaut d'une véritable solution à mon sens, pouvait constituer un pis-aller, tu peux cependant poursuivre dans la même voie, ce n'est pas mon problème. Je n'écris aucune ligne de code dont je ne sois persuadé qu'elle réponde à la situation.
Cordialement.
Tu as demandé 2 feuilles et j'ai bien fourni un fichier de 2 feuilles d'un problème qu'il me tient à cœur de résoudre.
Il m'aurait été profitable que tu te penches sur CE projet, mais tes choix de ne pas le faire sont respectables.
Je ne peux que te remercier de ton temps déjà investi pour toutes tes réponses, qui est pris sur le temps de ta vie personnelle.
Cordialement
Tu as demandé 2 feuilles et j'ai bien fourni un fichier de 2 feuilles d'un problème qu'il me tient à cœur de résoudre.
En effet !
Idem que pour mai et tous les mois de l'année , et chaque année
Donc beaucoup de feuilles identiques sur la forme
D'autre part, nommant tes feuilles avec une indication à caractère chronologique, tu indiques un éventail de feuilles devant couvrir plusieurs années mais rien n'identifie l'année dans le nom de feuille...
En outre, si ton projet est ainsi temporalisé, on ne voit pas bien pourquoi tu irais mettre à jour des listes concernant des mois ou des années passées !
Cette situation pourrait faire apparaître le problème sous un jour radicalement nouveau.
On se trouve donc dans une expectative elle aussi radicalement incertaine.
La première feuille porte une liste, une malheureuse liste isolée
Ce qu'on est vite amené à constater :
- la liste n'est pas sur la feuille, contrairement à ton fichier précédent...
Le problème a donc déjà changé de nature, on ne sait plus combien de listes on aura ou si on n'en aura qu'une pour toutes les listes.
D'autres éléments apparaissent : couleur, poids, prix, dont il n'était nullement question auparavant !
Les zones combinées sont disposées en ligne 1, espacées d'une colonne, chacune ayant sa cellule liée sur la même ligne dans la colonne qui suit, disposition complètement différente de celle du fichier précédent.
Et tu veux mobiliser des intervenants sur des éléments qui fluctuent ainsi selon tes pulsions du moment !
Tu offres toutes les garanties pour qu'on fasse un travail totalement inutilisable !