Aspect deliste déroulante faisant référence à plage variable

Bonjour, j'espère que vous allez pouvoir m'aider. J'ai parcouru quelques forums, mais je n'ai jamais trouvé la réponse précise au problème sous excel 2007.

• sur une feuille A d'excel, je crée une liste (plage) de noms sur une colonne que je nomme "ma_liste" par exemple. Cette liste comprend un nombre de ligne fixe, 20 par exemple. Dans cette liste, un nombre variable de lignes sera renseigné, les autres seront des lignes vides. Un bouton sur lequel j'affecte une macro classe par ordre alphabétique cette liste les 20 lignes et renvoie aussi en fin de liste les lignes vides.
• sur une feuille B, à partir d'une cellule, je fais "validation des données", autoriser liste, et =ma_liste dans le champ précisé ou sélectionne la plage de cellules. Tout va bien. Apparait alors dans la cellule une liste déroulante qui me pose problème: le scroll est en bas de la liste et le focus sur la 1ère ligne vide. Je suis donc obligé de remonter le scroll pour voir les premiers noms de la liste, ce qui n'est pas très ergonomique. S'il n'y a pas de ligne vide dans "ma_liste", le scroll est en haut de la liste. La présence d'une ligne vide vient le placer en bas ! Mais je suis obligé de laisser des lignes vides dans "ma_liste" pour pouvoir y ajouter des noms et les classer par la macro.

• au mieux: hauteur de la liste adaptée automatiquement au nombre de lignes (évite le scroll), pas de lignes vides,
• intermédiaire: scroll en haut et pas de lignes vides,
• au pire: scroll en haut et lignes vides en bas.

J'espère avoir été clair et pas trop long !

N'étant même pas développeur amateur, j'espère une solution simple.

Merci. Pmx

Bonjour et bienvenue

pmx a écrit :

Mais je suis obligé de laisser des lignes vides dans "ma_liste" pour pouvoir y ajouter des noms et les classer par la macro.

Non, pas si tu indiques dans ton nom défini "Ma_Liste" que les cellules à prendre en compte sont celles qui contiennent une valeur.

Exemple : actuellement, Ma_Liste fait référence à :

=Feuil1!$A$1:$A$20

Remplace la formule par :

=DECALER(Feuil1!$A$1;;;NBVAL($A:$A))

Si tu ajoutes une donnée dans la colonne A, elle sera prise en compte dans Ma_Liste

Amicalement

Nad

Merci pour ta réponse plutôt rapide !

20 lignes de texte, 1 ligne de code pour résoudre le problème. Ou enfin presque car il persiste un petit souci !

La valeur renvoyée par NBVAL est supérieure de 1 au nombre de lignes remplies. Ma colonne possède un titre en A1 et j'ai bien fait démarrer "ma_liste" en A2 (si je laisse A1, j'ai le titre de la colonne dans la liste déroulante). Le fait que la valeur renvoyée soit supérieure de 1, "ma_liste" contient une ligne vide et la liste déroulante y faisant référence a le scroll en bas !

Alors j'ai écrit dans la formule (NBVAL(feuil1!$A:$A)-1). Cela fonctionne, plus de ligne vide et scroll enfin en haut.

Mais cela ressemble plus à une rustine qu'à une véritable formule !

• Pourquoi y a t-il 1 de plus ?
• y a t-il une autre formule plus rigoureuse à écrire ?
• ou dois-je laisser cette formule ?

Merci de poursuivre ton aide.

Pmx

Re

pmx a écrit :

Pourquoi y a t-il 1 de plus ?

Parce que le nombre de valeur de la colonne A inclus évidemment A1 (le titre)

pmx a écrit :

Alors j'ai écrit dans la formule (NBVAL(feuil1!$A:$A)-1).

C'est exactement ce qu'il fallait faire. La formule finale doit être :

=DECALER(Feuil1!$A$2;;;NBVAL(Feuil1!$A:$A)-1)

Amicalement

Nad

Bonjour,

Merci Nad pour tes réponses, la liste a enfin l'aspect que je souhaite

Je suis aussi intéressé par la question de "piratman" sur la "liste déroulante qui apparait au 1er clic" pour l'appliquer sur un grand tableau et diminuer le nombre de clic.

Question: où dois-je insérer le code proposé ? (question triviale j'imagine, mais bon ... !)

Merci

Pmx

Bonjour

Tu fais un click droit sur le nom de ta feuille ==> "Visualiser le code"

Tu n'as plus qu'à faire un COPIER-COLLER du code et changer les plages concernées.

Amicalement

Nad

Merci, cela fonctionne sauf dans un cas précis:

Mon tableau contient des cellules fusionnées (par 2, horizontales ou verticales) qui font aussi appel à la même liste. Dans ce cas, la sélection simple de la cellule ne suffit pas à dérouler la liste. Il faut les 2 clics habituels.

As-tu une solution ?

Merci

Pmx

Re

La fusion des cellules est la plaie des formules et/ou codes.

Joins ton fichier qu'on regarde.

Amicalement

Nad

Bonsoir,

Voila le fichier en PJ. J'ai dû supprimer les données (confidentielles) et en remplacer quelques unes par des données "bidon" !

Tes précédents conseils étaient OK (aspect de la liste déroulante qui ne contenait plus de ligne vide et déroulement de la liste au simple clic sur une cellule simple).

Le hic venait du clic sur une cellule fusionnée (horizontale ou verticale) où le double clic restait nécessaire.

Je ne pourrais voir ta réponse que lundi prochain.

Merci de ton aide.

Pmx

Re

Change le code par celui-ci :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Range("C6:AL110"), Target) Is Nothing Then
        SendKeys "%{down}"
    End If

End Sub

Amicalement

Nad

Bonsoir Nad,

OK tout fonctionne. Merci pour ces réponses rapides.

Sympa ce forum.

Une dernière question: la hauteur de la liste déroulante s'adapte automatiquement jusqu'à 8 lignes. Au dela, un scroll apparait. Peut-on agir sur ce paramètre et déterminer le nombre de lignes à partir duquel on veut voir le scroll apparaitre ?

S'il n'y a pas de réponse, tant pis, je suis déjà bien content de ce que j'ai eu.

Amicalement

Pmx

Bonjour

Là, aucune solution ; une liste créée par la validation de données ne peut comporter que 8 lignes visibles.

Amicalement

Nad