Liste variable

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

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.

1^erfaçon: Je nomme juste la liste à l'aide de ton code (ci-dessous); puis dans la feuille2 sur toutes les cellules de la colonne A (sauf A1), j'utilise la fonction validation où je renseigne les champs 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.

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

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

Je te remercie beaucoup. Ca marche.
Bonne nuit.

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

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:

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.  

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