Liste variable [Résolu]

Signaler
Messages postés
6
Date d'inscription
mardi 26 décembre 2000
Statut
Membre
Dernière intervention
8 novembre 2008
-
Messages postés
6
Date d'inscription
mardi 26 décembre 2000
Statut
Membre
Dernière intervention
8 novembre 2008
-
Bonjour,
Je souhaiterai créer une cellule dans laquelle on a accès a une liste déroulante de choix prédéfinis mais variable.
Je m'explique Mon classeur comporte deux feuilles.
Dans la première, Colonne A: j'ai une une liste de pays (par exemple) A1="France"; A2="Allemagne".
Il est à noter que cette liste évoluera dans le temps à la prochaine utilisation du fichier cela pourrait-être par exemple A1="France"; A2="Allemagne"; A3="Espagne".

Dans la deuxième feuille, je souhaite faire apparaitre cette liste variable sous forme de liste déroulante. Comme losrqu'on utilise la fonction validation.

J'ai essayé de donner un nom à la colonne A, puis de l'utiliser dans la fonction validation. J'obtient bien une liste déroulante variable avec toutes les données mais je ne suis pas restreint en écriture. En effet, je peux écrire par exemple Australie même si ce mot n'était pas dans la liste.

Avez-vous une idée?

J'ai essayé aussi avec visual basic mais je n'arrive pas nommer la liste qui varie (cf. mon code)
Sélection de la liste variable
Dim a As Integer
For i = 1 To 10000
If Cells(i, 1) = "" Then
a = i
End If
Next
For k = 1 To a
Union(Selection, Cells(k, 1)).Select
Tentative pour nommer la serie
Next
ActiveWorkbook.Names.Add Name:="Pays", RefersToR1C1:="=Feuille1!R1C1:R4C1"

Le code en rouge doit être entré sous cette forme je ne peux donc pas faire varier la taille de la liste.

Avez-vous une idée? Excel ou VBA?

Merci d'avance

10 réponses

Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
Tu pourrais essayer ainsi

    Dim nbLignes As Long
   
    'Pour savoir combien il y a de lignes dans la liste en colonne A
    nbLignes = Sheets("Feuil1").Cells(Rows.Count, "A").End(xlUp).Row
   
    'Utilisation de RefersTo plutôt que RefersToR1C1
    ActiveWorkbook.Names.Add Name:="Pays", RefersTo:="=Feuil1!A1:A" & nbLignes
   
    'Ajout d'une liste déroulante à la cellule A1
    With Sheets("Feuil2").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="=Pays"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With

Et plutôt que d'ajouter la liste de validation seulement qu'à A1, tu pourrais le faire pour un Range quelconque en modifiant l'adresse.

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
C'est probablement dû aux adresses...
Essaie ceci

ActiveWorkbook.Names.Add Name:="Pays", RefersTo:="=Feuil1!A$1:A$" & nbLignes

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
6
Date d'inscription
mardi 26 décembre 2000
Statut
Membre
Dernière intervention
8 novembre 2008

Bonsoir MPi,


Je te remercie pour ta réponse. Effectivement, ton code marche très bien.
Cependant, je souhaiterai affecter la liste déroulante à toute la colonne A de la feuille2 (sauf la cellule A1: zone de titre).

J'ai pensé à 3 manières mais elles ne marchent pas.

erfaçon: Autoriser :Liste et Source:=Pays.
J'ai bien des listes déroulantes mais pas la totalité de la liste.

Dim nbLignes As Long
   
    'Pour savoir combien il y a de lignes dans la liste en colonne A
    nbLignes = Sheets("Feuil1").Cells(Rows.Count, "A").End(xlUp).Row
   
    'Utilisation de RefersTo plutôt que RefersToR1C1
    ActiveWorkbook.Names.Add Name:="Pays", RefersTo:="=Feuil1!A1:A" & nbLignes

2èmefaçon: J 'ai créer une boucle dans ton code pour affecter la même opération à toutes les cellules, mais en plus du temps très important pour effectuer la macro j'ai le même problème de liste déroulante incomplète que dans la façon 1.

Dim nbLignes As Long
    'Pour savoir combien il y a de lignes dans la liste en colonne A
    nbLignes = Sheets("Feuil1").Cells(Rows.Count, "A").End(xlUp).Row
    'Utilisation de RefersTo plutôt que RefersToR1C1
    ActiveWorkbook.Names.Add Name:="Pays", RefersTo:="=Feuil1!A1:A" & nbLignes
    For i=2 to 65336
        With Sheets("Feuil2").Cells(i,1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="=Pays"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
Next

3èmefaçon:  J'ai utilisé ton code avec Range("A:A") à la place de range ("A1"); mais j'ai toujours le même problème.

Vois-tu une explication? As-tu une solution?


Quoiqu'il en soit je teremercie déjà de t'être penché sur mon problème. j'ai bien avnacé grace à toi.
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
Et si tu changes ceci ?

With Sheets("Feuil2").Range("A1").Validation
pour
With Sheets("Feuil2").Range("A2:A65536").Validation

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
6
Date d'inscription
mardi 26 décembre 2000
Statut
Membre
Dernière intervention
8 novembre 2008

Non ça ne marche toujours pas. En fait, le code affiche bien la liste déroulante mais à chaque ligne je perde un choix dans la listec'est-à-dire;


Si en feuille1 j'ai :A1:allemagne A2:Australie A3:Italie A4: Portugal
Avec tous les codes essayé j'ai en liste déroulante dans la cellule:
A2:"Australie;Italie; Portugal"
A3:"Italie; Portugal"
A4:"Portugal"
A5: plus rien etc...

Je ne comprends pas pourquoi
Messages postés
6
Date d'inscription
mardi 26 décembre 2000
Statut
Membre
Dernière intervention
8 novembre 2008

Je te remercie beaucoup. Ca marche.
Bonne nuit.
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
Bonne soirée ou bonne nuit, selon...
Et bonne continuation !

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
6
Date d'inscription
mardi 26 décembre 2000
Statut
Membre
Dernière intervention
8 novembre 2008

Bonjour,
Maintenant je souhaiterai effectuer des listes déroulantes imbriquées.
J'ai cherché dans le site, mais je n'est pas réussi à trouver.
En feuille1 j'ai:


<colgroup>
<col style=\"WIDTH: 60pt\" span=\"2\" width=\"80\" />
</colgroup>

----

ColonneA
allemangne,

ColonneB
rouge,

----

Italie,
rouge ,

----

allemangne,
bleu,

----

France,
vert,

----

France,
bleu,

----

italie,
rose,

----

allemangne,
vert



En feuille2:
Cellule A1: Choix du pays
Cellule B1: Choix de la couleur
Je souhaiterai que le choix de la couleur dépende du pays.
Donc si A1=Italie on a en B1 le choix entre rouge et rose.

Avez-vous une solution? 
Merci d'avance.  
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
Tu devras boucler la liste des valeurs pour "emmagasiner" les couleurs correspondantes dans une chaîne. Chaque couleur devra être séparée par une virgule et, plutôt que de mettre un nom de plage, tu mets cette chaîne préalablement créée.

strCouleur = "rouge,rose"
..........
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=strCouleur
..........

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
6
Date d'inscription
mardi 26 décembre 2000
Statut
Membre
Dernière intervention
8 novembre 2008

Merci de ta réponse.
Cependant je n'arrive pas très bien à la mettre en oeuvre.
Car dans la Feuille1; la base de données peut évolué. On peut ainsi avoir des lignes supplémentaires (par exemple Italie-violet). Donc le choix en celluleB1 feuille2 évolue aussi (rouge; rose; violet).


Enfin, si on ajoute une ligne identique (par exemple: Italie-rose).
J'aimerai que le choix soit (rouge;rose;violet) et non pas (rouge; rose; violet;rose)