[VBA-EXCEL] Colonne de Listes déroulantes

Version imprimable

17/02/2007, 01h01
bambou

[VBA-EXCEL] Colonne de Listes déroulantes

Bonjour,

Je cherche à faire une "colonne de listes déroulantes"

Je m'explique :
J'ai des données dans une feuille (F1) dans trois colonnes

Une colonne identifiant
Une colonne nom
Une colonne description

Je souhaiterai dans une autre feuille (F2) avoir une colonne qui dans chacune de ses cellules aurait une liste déroulante (dans le style des filtres automatiques) qui se répete et qui contiendrait les valeurs de la liste de la feuille F1.

Biensur, la liste devrait afficher les noms, mais la valeur de la cellule serait en fait l'identifiant

J'ai fait quelques recherches, et j'ai trouvé quelque chose ressemblant...mais c'est pas tout à fait ça. Dans le menu, données > Validation...

Dans le champs Autoriser, il y a l'option liste qui permet de faire pour tout une colonne ou une ligne de limiter les choix possible à une liste défini par le champs Source qui se trouve dans la meme fenetre...

Le probleme, c'est qu'avec ça, les données doivent se trouver dans la meme feuille et il n'y a pas moyen d'afficher les noms alors que la valeur est l'identifiant (comme dans une liste html par exemple avec l'attribu value de la balise option qui peut etre différent de la valeur affichée dans la liste).

Quelqu'un sait-il faire ça ou peut me donner une piste pour le faire?

Merci
17/02/2007, 06h48
SilkyRoad

bonjour

Citation:

Dans le champs Autoriser, il y a l'option liste qui permet de faire pour tout une colonne ou une ligne de limiter les choix possible à une liste défini par le champs Source qui se trouve dans la meme fenetre...

Le probleme, c'est qu'avec ça, les données doivent se trouver dans la meme feuille

j'espère que ce lien pourra t'aider

http://www.developpez.net/forums/sho...32&postcount=4

pour le reste je n'ai pas compris ...

michel
17/02/2007, 15h32
bambou

c'était pas trés clair effectivement..

avec ce que tu m'a donné, j'arrive bien a faire que dans une colonne, j'ai une liste déroulante qui se repete dans chaque cellule et qui contient des valeur contenue dans 1 colonne d'une autre feuille(F2).

ce que je veux, mais je ne suis pas sur que c'est faisable..

la source, pour l'instant se base sur une seule colonne (Nom) dans la feuille F2. Or dans ma feuille F2, j'ai deux colonnes, une ID et une NOM

je voudrait que dans les listes de la feuille F1 s'affiche le nom, mais que la valeur de la cellule soit l'identifiant (principe des clefs étrangere dans une base de donnée) car ce que je veux enregistré dans la feuille F1 est bien l'identifiant (qui est unique), mais pour l'utilisateur c'est plus facile de travailler avec les noms

avec quelques images ce sera peut-etre plus clair :

voici ma source sur la feuille F2 :
http://www.core1.free.fr/src.jpg

ma liste se base sur la colonne nom :
http://www.core1.free.Fr/lst.jpg

ce que je veux, c'est que lorsqu'un élement est selectionné dans la liste, ce soit l'identifiant qui soit la valeur de la cellule :
http://www.core1.free.Fr/fin.jpg

merci pour ton aide
18/02/2007, 01h10
loris

1 pièce(s) jointe(s)
Hello,

J'ai une solution [1] au problème qui -à mon avis- ne peut être traité qu'avec l'aide d'un petit code VBA (i.e. Visual Basic for Applications). Voir l'annexe pour les détails que je commente ci-dessous!

[Feuille "F1"]
- Cette feuille -si j'ai bien compris- sert de base de données pour l'enrichissement partiel de la feuille "F2".
- Dans "F1", la mention de la colonne "D" ne me semble pas pertinente pour le cas étudié. Oublions-la!
  La colonne "C" a été rajoutée afin de pouvoir utiliser la fonction vlookup() à partir du nom. Comme cette fonction cherche la valeur correspondante à la droite de la valeur recherchée, la colonne "id" doit forcément se trouver à droite de la colonne "nom". Soit c'est faisable sans problèmes et, dans ce cas, la colonne "id.bis" n'a pas lieu d'être. Le cas échéant (comme je l'ai prévu), la colonne "id.bis" doit être créée, contenir une formule copiant le contenu saisi dans la colonne "id" et, éventuellement, être cachée.
- Dans "F1", il y a 2 zones nommées (i.e. Ctrl+F3 pour y accéder):
  - "dbQui" contenant la liste des noms que l'on veut afficher dans "F2" et
  - "BDD" contenant la base de données sur laquelle on va appliquer la fonction vlookup() dans le code VBA.
[Feuille "F2"]
- La colonne "C" contient des cellules dont la valeur doit correspondre au contenu de "dbQui" (cf ci-dessus). En sélectionnant une valeur, on voit que le "miracle" se produit. Ce dernier fait appel à une fonction VBA attachée à la feuille "F2". Pour y accéder, faire un clic-droit sur l'onglet "F2"et sélectionner "View Code" (ou quelque chose similaire en français). La fonction est commentée brièvement mais je pense que c'est assez clair. Cette fonction est en fait appelée à chaque fois qu'un changement intervient sur la feuille "F2". Si le changement intervient dans la colonne "C", alors un traitement a lieu. Le cas échéant, rien ne se passe.
- Tout cela marche très bien sous les conditions suivantes:
  - dans le cas où la colonne "id" ne correspond pas à la colonne "C", il faut adapter le code en conséquence (ATTENTION donc aux insertions/suppressions de colonnes) et
  - il faudrait éviter que la sélection de la cellule suivante après pression sur la touche "Entrée" se fasse à droite ou à gauche (i.e. Tools > Options... > Edit > Move Selection after Enter [2]) car cela posera problème lors de la saisie manuelle dans les cellules adjacentes à la cellule "id" (i.e. cellules "B" ou "D" dans l'exemple).
Bon travail!

[1] basée sur Microsoft Excel 2002 SP3 et Microsoft Visual Basic 6.3
[2] désolé mais ma version est en anglais et je préfère mentionner le tout en anglais plutôt que de me risquer à faire des traductions malheureuses!
18/02/2007, 11h44
Pierre Fauconnier
Bonjour

Sans VBA, il te faudra une colonne supplémentaire F en feuil2 pour renvoyer l'ID correspondant à la saisie du nom en E.

1. Définis correctement les plages sur lesquelles s'appuient tes listes. Car dans l'exemple que tu illustres, je vois apparaître "Nom" dans la liste déroulante, ce qui n'est pas pratique. Conseil: Utilise la fonction DECALER pour référencer tes plages.
La plage des ID pourra être référencée de la manière suivante. Dans Insertion/Noms/Definir, tu nommes une plage ID et dans la zone Fait référence à, tu saisis ceci
Code:

1 2 =DECALER(Feuil1!$A$1;1;0;NBVAL(Feuil1!$A:$A)-1;1)
Cette formule avec la fonction DECALER permet à ta liste d'avoir une taille dynamique, et donc d'évoluer en fonction des ID et des noms qui sont saisis.
Tu opères de même pour la plage nommée Noms que tu définis avec

Code:

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

Tu appuies ta liste déroulante en E de Feuil2 sur la plage nommée Noms et en F2 de feuil2, tu saisis la formule suivante:

Code:

=INDEX(ID;EQUIV(E2;Noms;0))

que tu peux tirer vers le bas. Cette formule renverra l'ID du nom saisi en E.

Deux contraintes: Il faut que chaque nom soit unique dans la plage nommée Noms car seul l'ID du premier nom trouvé sera renvoyé. Il faut également qu'il y ait le même nombre d'ID que de noms, mais cela me paraît évident.

Avec ce système, tu ajoutes une colonne, mais tu ne dois pas utiliser VBA. C'est un bon exemple d'utilisation d'Excel et des possibilités de formules d'Excel

excusez moi pour ma réponse tardive, j'ai du travailler sur un autre projet en urgence ces derniers jours...

merci pour vos réponses, je me suis plutot orienté sur la solution VBA donnée par loris, j'ai repris le principê de ton code et j'en ai refait une fonction que je pourrais ré-utiliser ailleur :

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
Private Sub changeVal(srcSheet As String, srcRangeName As String, srcIDRangeName As String, IDcol As String)
'Déclaration de l'existence des variables utilisées plus bas
    Dim strAddress, strName, str As String
    Dim objCell1 As Range
 
'Routage du traitement des erreurs au cas où...
    On Error GoTo ErrorHandler
 
    Application.ScreenUpdating = False
    'Arrete l'execution si numéric ; a modifier si l'id n'est pas un numeric
    If IsNumeric(ActiveCell.Value) Then Exit Sub
 
    strAddress = ActiveCell.Address(ColumnAbsolute:=False)
    If Split(strAddress, "$")(0) <> IDcol Then  
        Exit Sub
    Else
        strName = ActiveCell.Value
        Set objCell1 = Application.Sheets(srcSheet).Range(srcRangeName).Find(What:=strName, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        str = objCell1.Address
        str = Split(str, "$")(2) - 1
        ActiveCell.Value = Sheets(srcSheet).Range(srcIDRangeName).Cells.Rows(str).Value
    End If
 
 
'Gestion des erreurs
ErrorHandler:
    Select Case Err.Number
        Case 0
        Case 28, 2042
            Exit Sub
        Case Else
            Dim strPrompt, strTitle As String
            strTitle = "Error handler"
            strPrompt = "Une erreur s'est produite avec les caractéristiques suivantes:" & vbCrLf & _
                        "     - numéro: " & Err.Number & vbCrLf & _
                        "     - description: " & Err.Description & vbCrLf & vbCrLf & _
                        "Veuillez svp noter ces informations et contacter votre administrateur!"
            MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
            Exit Sub
    End Select
End Sub

Avec :

srcSheet :Nom de la sheet qui contient la source de la liste déroulante
srcRangeName : nom du Range qui contient les nom a afficher dans la liste
rcIDRangeName : nom du range qui contient les ID qui vont remplacer les noms
IDcol : nom de la colonne pour verifier qu'on travail bien avec les bonnes valeures

Si vous avez des remarques sur la fonction ci-dessus, elles sont le bienvenu

encore merci à tous les 2!

22/02/2007, 18h20
bambou

j'ai encore une petite question...toujours dans le meme contexte

Comment puis-je faire maintenant pour que les éléments de ma liste soient basée sur 2 colonnes?

La source avec laquelle je travaille est composée de 3 colonnes :
ID : représente l'identifiant
Location : représente le lieu de stockage
Temp : représente la température

Je souhaite que la liste affiche non seulement la location mais aussi la température

d'autre part dans la feuille ou il y a la colonne de liste, j'ai des ID qui s'affichent maintenant...ce qui n'est pas trés lisible pour l'utilisateur...
il y aurait-il un moyen de changer le tooltip de la cellule ou autre pour aider un peu la lecture de l'utilisateur?
23/02/2007, 11h36
loris

Précision requise, svp!

Bambou, comment le programme changeVal(...) est-il initié? Merci de préciser!
23/02/2007, 13h59
bambou
le programme est lancé par un simple appel de fonction sur l'évenement worksheet_change de la feuille (F1) où les listes déroulantes se trouve
Code:

1 2 3 Private Sub Worksheet_Change(ByVal Target As Range) changeVal "F2", "src", "srcId", "A" End Sub
dans cet exemple, la source est sur la feuille F2, dans 3 colonnes : id, nom, description :

où
- "F2" est le nom de la feuille contenant la source de donnée
- "src" est le nom de la plage de la source dont les données seront affichées dans les listes déroulantes (la colonne des noms par exemple)
- "srcID" est le nom de la plage de la source contenant les ID
- "A" représente la colonne qui contient les listes déroulantes (dans la feuille F1)
23/02/2007, 14h57
bambou

1 pièce(s) jointe(s)

ce sera plus clair avec la source :)

j'ai ajouté la gestion de notes (tooltips) aussi

testé avec office 2000 et VBA 6.4.9972
25/02/2007, 10h51
loris

Merci, mais...

Merci pour les précisions et pour les sources!

Ceci écrit, il faudrait encore préciser davantage les questions car il est fait référence à des objets contenant de nouveaux éléments non traités jusqu'à présent (i.e. Location ou Température). Je n'ai malheureusement pas le temps d'identifier les changements d'une version à l'autre et une base unique de travail me semble primordiale.

Finalement, merci de nommer ce qui peut l'être: quand on parle de colonne, liste ou feuille -sachant qu'il peut y en avoir plusieurs- à laquelle fait-on référence? Un commentaire tel que

Citation:

... dans la feuille ou il y a la colonne de liste, ...

pourrait très avantageusement être remplacé par le nom de la feuille.

Merci de prendre bonne note et de préciser ce qui peut l'être afin d'éviter toute malencontreuse erreur d'interprétation!