IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

[VBA-EXCEL] Colonne de Listes déroulantes


Sujet :

Macros et VBA Excel

  1. #1
    Membre confirmé Avatar de bambou
    Profil pro
    Inscrit en
    Mars 2004
    Messages
    192
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations forums :
    Inscription : Mars 2004
    Messages : 192
    Par défaut [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

  2. #2
    Expert confirmé

    Homme Profil pro
    Inscrit en
    Août 2005
    Messages
    3 317
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Secteur : Industrie

    Informations forums :
    Inscription : Août 2005
    Messages : 3 317
    Par défaut
    bonjour

    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

  3. #3
    Membre confirmé Avatar de bambou
    Profil pro
    Inscrit en
    Mars 2004
    Messages
    192
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations forums :
    Inscription : Mars 2004
    Messages : 192
    Par défaut
    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 :


    ma liste se base sur la colonne nom :


    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 :


    merci pour ton aide

  4. #4
    Membre habitué
    Profil pro
    Inscrit en
    Octobre 2004
    Messages
    11
    Détails du profil
    Informations personnelles :
    Localisation : Suisse

    Informations forums :
    Inscription : Octobre 2004
    Messages : 11
    Par défaut
    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!
    Fichiers attachés Fichiers attachés

  5. #5
    Rédacteur/Modérateur


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 125
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 58
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 125
    Billets dans le blog
    131
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  6. #6
    Membre confirmé Avatar de bambou
    Profil pro
    Inscrit en
    Mars 2004
    Messages
    192
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations forums :
    Inscription : Mars 2004
    Messages : 192
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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!

  7. #7
    Membre confirmé Avatar de bambou
    Profil pro
    Inscrit en
    Mars 2004
    Messages
    192
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations forums :
    Inscription : Mars 2004
    Messages : 192
    Par défaut
    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?

  8. #8
    Membre habitué
    Profil pro
    Inscrit en
    Octobre 2004
    Messages
    11
    Détails du profil
    Informations personnelles :
    Localisation : Suisse

    Informations forums :
    Inscription : Octobre 2004
    Messages : 11
    Par défaut Précision requise, svp!
    Bambou, comment le programme changeVal(...) est-il initié? Merci de préciser!

  9. #9
    Membre confirmé Avatar de bambou
    Profil pro
    Inscrit en
    Mars 2004
    Messages
    192
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations forums :
    Inscription : Mars 2004
    Messages : 192
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 :


    - "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)

  10. #10
    Membre confirmé Avatar de bambou
    Profil pro
    Inscrit en
    Mars 2004
    Messages
    192
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations forums :
    Inscription : Mars 2004
    Messages : 192
    Par défaut
    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
    Fichiers attachés Fichiers attachés

  11. #11
    Membre habitué
    Profil pro
    Inscrit en
    Octobre 2004
    Messages
    11
    Détails du profil
    Informations personnelles :
    Localisation : Suisse

    Informations forums :
    Inscription : Octobre 2004
    Messages : 11
    Par défaut 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
    ... 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!

Discussions similaires

  1. Copier des cellules excel dans une liste déroulante vba
    Par Papillon34 dans le forum Macros et VBA Excel
    Réponses: 1
    Dernier message: 19/01/2010, 16h03
  2. Réponses: 10
    Dernier message: 20/04/2007, 23h16
  3. Réponses: 15
    Dernier message: 21/11/2006, 10h13
  4. [VBA][Excel] Colonne : Pourquoi?
    Par le_sonic dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 31/12/2005, 02h45
  5. Réponses: 6
    Dernier message: 13/12/2005, 11h05

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo