[VB EXCEL] ajuster longueur liste déroulante à nbr données Résolu/Fermé

Question

Bonjour, 



J'utilise VB pour automatiser une action répétitive via excel pour mon travail.
Je crée des listes déroulantes grâce à vb avec ce code : 

            With Selection.Validation
                
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=liste"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
        End With

Le problème est que dans la liste déroulante que j'obtiens on me propose énormément de blancs. en effet je crée ainsi 12 listes différentes qui ne sont pas toutes de la même taille (de 2 éléments à plus d'une vingtaine), or la liste déroulante obtenue propose toujours le même nombre d'entrées et ainsi beaucoup de blancs inutils et dérangeant. 
J'ai recherché dans l'aide excel et sur différents forum mais je n'ai pas trouvé de résultats.

Quelqu'un sait comment régler ça?

merci d'avance.

ccm81 · Answer

Bonjour

En supposant que les listes sont dans la feuille 1, il te faut les définir avec quelque chose comme
=DECALER(Feuil1!$A$1;1;0;NBVAL(Feuil1!$A:$A)-1;1)

Bonne suite

Kam9Me210 · Answer

Merci pour votre réponse mais j'ai encore un problème avec ça.
En utilisant la formule :
=DECALER(Loco;0;0;NBVAL(Loco);1) où Loco est un nom que j'ai créé et qui appelle un Range.

cela me donne le bon résultat quand je le tape dans la fenêtre données mais quand je le rajoute directement dans ma macro de cette façon :

            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=DECALER(Loco;0;0;NBVAL(Loco);1)"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With

excel me dit qu'il y a une erreur définie par l'application ou par l'objet. 
D'où cela peut-il venir?

ccm81 · Answer

Bonjour 

1. C'est au niveau de la feuille que tu dois déclarer le nom de la liste "loco" 
exemple pour une liste colonne D et commençant à la ligne 2 (avec rien d'autre en colonne D) 
=DECALER(Feuil1!$D$2;0;0;NBVAL(Feuil1!$D:$D)-1;1) 

2. Puis au niveau du code VBA 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
        xlBetween, Formula1:="=loco"
Bonne suite

Kam9Me210 · Answer

En fait mon document excel contient deux feuilles.
Dans la feuille 2 je possède un tableau où sont rangés par colonne les types d'éléments que je peux utiliser pour construire mon train.
Chaque colonne présente en première ligne un grand type : loco, wagon, ballastière... Ensuite les lignes d'en dessous sont éléments appartenant à ce types : les différentes locos, les différents wagons,... 
La liste de chaque type peut évoluer (ajout, retrait,..) j'ai donc créé un programme qui me crée une liste de chacun de ses éléments, le nom de la liste étant le nom du grand type : 

Sub créalistes()
Worksheets("BdD TTx").Activate
Dim k As Integer
Dim i As Integer

For i = 12 To 24
k = 0
    While Worksheets("BdD TTx").Cells(206 + k, i) <> ""
        k = k + 1
    Wend
    
ActiveWorkbook.Worksheets("BdD TTx").Range(Cells(206, i), Cells(206 + k, i)).Name = WorksheetFunction.Substitute(Worksheets("BdD TTx").Cells(205, i).Value, " ", "_")

Next i
End Sub


Ensuite dans la feuille 1 je veux créer mon train.
Le but est que si l'utilisateur en ligne 1 colonne 1 choisi Loco dans une liste déroulante (celle-ci étant déjà présente) en ligne 2 colonne 1 se crée la liste déroulante de l'ensemble des éléments de la liste "Loco".
j'ai réalisé cela avec le code : 

    If Worksheets("Compo TTx").Cells(72 + 4 * i, j) = "Loco" Then
            Worksheets("Compo TTx").Cells(72 + 4 * i + 1, j).Select
            
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlEqual, Formula1:="=Loco"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With


(aux indices prêts)Mais avec cette écriture j'obtiens des listes déroulantes avec beaucoup de blancs comme expliqué au début.

Je pensais du coup que dans le Formula1 je pouvais écrire exactement la formule qui allait être utilisée dans l'utilitaire de création de liste déroulante et ainsi régler mon problème.

ccm81 · Answer

Quand tu attribues un nom à une plage de façon dynamique par exemple
=DECALER(Feuil1!$D$2;0;0;NBVAL(Feuil1!$D:$D)-1;1) 
tout ajout/suppression d'élément dans la liste est pris en compte pourvu qu'il n'y ait pas de blanc dans la liste
Donc la question du nombre d'éléments de la liste lors de l'attribution de la liste via la macro, ne se pose plus.

RQ. Est il vraiment nécessaire de passer par VBA pour attribuer ces listes déroulantes ?

Kam9Me210 · Answer

c'est en effet parfait comme ça!
merci beaucoup pour ton aide.

[VB EXCEL] ajuster longueur liste déroulante à nbr données

6 réponses

Discussions similaires