Liste déroulante Excel VBA

Version imprimable

05/09/2007, 15h09
a.dequidt

Liste déroulante Excel VBA

Boujour,

Je cherche a créer une liste déroulante ou les données de cette liste sont situées dans les cellules "Z1:Z5" sachant que le nombre de données peut varier. Le nombre total de données est située dans la cellule L1.

Savez vous comment faire?

Merci
05/09/2007, 15h16
ouskel'n'or

Ta liste, tu veux la placer dans une feuille de calculs ou dans un userform ?
05/09/2007, 15h26
a.dequidt

Etant donné que je souhaite réutiliser la valeur choisie, je pense qu'il vaut mieu que je mette dans une cellule. j'ai essayer de faire une validation de donnée mais comme le nombre de données peut varier, je ne sais pas comment gérer ça

J'ai eu à gérer ce genre de problème... ma solution pourra peut-être te convenir.

Il faut mettre ce code derrière la feuille où se trouvent les valeurs de la liste, événement Worksheet / Change.

Ici les données de ma liste sont en colonne A, à partir de la ligne 7.
La macro détecte tout changement dans la feuille mais ne se déclenche que si ce changement a lieu en colonne A et après la ligne 6.
Le nom de ma plage, auquel je fais référence pour la constitution de la liste déroulante, est "liste_admins".
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Private Sub Worksheet_Change(ByVal Target As Range) ' Redéfinition de la plage de la liste déroulante quand on ajoute ou supprime une entrée Dim var_active, var_range, var_refers, libelle_liste As String On Error GoTo fin If Target.Row > 6 And Target.Column = 1 Then var_active = ActiveCell.Address i = 7 Do While Cells(i, Target.Column) <> "" i = i + 1 Loop i = i - 1 var_range = "A7:A" & i var_refers = "=Paramètres!R7C1:R" & i & "C1" libelle_liste = "liste_admins" Range(var_range).Select ActiveWorkbook.Names.Add Name:=libelle_liste, RefersToR1C1:=var_refers Range(var_active).Select End If fin: End Sub

05/09/2007, 16h01
a.dequidt

J'aurai préférer utiliser une validation des données, mais le probleme que je rencontre est que la liste est "infinie", car étant donné que je ne connais pas la longueur de la liste j'ai pris toute la colonne ou était les données.
05/09/2007, 16h08
neupont

Oui c'est bien le but de mon exemple de limiter la liste aux cellules utilisées.
La boucle du "i" sert à ça.
05/09/2007, 16h10
a.dequidt

Le probleme avec ton code c'est que je le comprend pas et je ne sais pas a quoi il abouti :s
05/09/2007, 16h28
Gorfael

[Salut à tous
Un exemple de liste de validation à partir d'une liste déclarée de manière dynamique avec la fonction décaler et la fonction NbVal(qui peut être remplacée par $L$1, suivant le poste initial)

Les liste de validation accepte les listes déclarées sur une autre feuille uniquement si la liste est nommée.
Comme c'est le cas, ta liste peut être mise sur une autre feuille (masquée et/ou protégée si besoin est)

A+
NB Si besoin d'explications complémentaires, les demander

Bon je vais détailler un peu, et essayer de l'adapter pour ton cas.
Donc les données à utiliser pour ta liste sont en colonne Z, début de la plage à Z1 mais fin de la plage inconnue. C'est bien ça ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
'Worksheet_Change permet d'intercepter toute modification manuelle ou 
'macro dans la feuille (mais pas les modifs via formules, d'après ce que j'ai remarqué).
 
' Redéfinition de la plage de la liste déroulante quand on ajoute ou supprime une entrée
 
Dim var_active, var_range, var_refers, libelle_liste As String
 
On Error GoTo fin
 
'la macro détecte toute modification dans la feuille, mais on ne veut la
'déclencher que si la modif est sur le colonne Z (qui a le n° 26, sauf erreur de ma part :mrgreen:)
 
If Target.Column = 26 Then '=> donc seule colonne Z remplit la condition
 
    'on garde en mémoire la cellule actuellement active, pour la re-sélectionner à la fin :
    var_active = ActiveCell.Address 
 
    'on va identifier la dernière cellule remplie de la colonne Z, son n° de ligne
    'sera mis dans la variable i :
    i = 1 'on commence la boucle de vérification à la ligne 1...
    Do While Cells(i, Target.Column) <> "" '...et tant qu'on ne rencontre pas de cellule vide...
        i = i + 1 '...on continue...
    Loop
    i = i - 1 'à la fin i a été itéré une fois de trop, on rectifie
 
    'on définit les variables dont on aura besoin pour définir la plage de la liste, en fonction de i :
    var_range = "A7:A" & i
    var_refers = "=Paramètres!R7C1:R" & i & "C1"
    libelle_liste = "liste_admins" 'ici c'est le nom de ta liste, à modifier !
 
    'on sélectionne la plage de cellules qu'on va utiliser : colonne Z, de la ligne 1 à la ligne i :    
    Range(var_range).Select
 
    'on crée et "nomme" la référence à cette plage de cellules.
    'elle existait déjà bien sûr, mais on la redéfinit :
    ActiveWorkbook.Names.Add Name:=libelle_liste, RefersToR1C1:=var_refers
 
    'on re-sélectionne la cellule qui était active au début, pour que l'utilisateur ne se rende compte de rien (ou de pas grand-chose en tout cas) ;)  :
    Range(var_active).Select
 
End If
 
fin:
 
End Sub

Normalement, ça doit marcher :aie:

Edit : la solution de Gorfael est sûrement plus adaptée, sans macro en plus !