Liste déroulante sous VBA

Bonjour,

Je cherche à créer une liste déroulante sous vba

Ma colonne A comporte des fournisseurs

Ma colonne B comporte les articles liés

Ex : Zone A1:B9 = "ArticleFou"

Col A Col B

1 Frn1 Art11

2 Frn1 Art12

3 Frn1 Art13

4 Frn1 Art14

5 Frn2 Art21

6 Frn2 Art22

7 Frn2 Art23

8 Frn3 Art31

9 Frn3 Art32

Je voudrais que la macro créé les listes déroulantes dans la zone "ArticleFou" pour chaque fournisseur :

"Frn1" comprenant les articles Art11 à Art14 (puisque tous liés à Frn1)

"Frn2" comprenant les articles Art21 à Art23 (puisque tous liés à Frn2)

"Frn3" comprenant les articles Art31 à Art32 (puisque tous liés à Frn3)

Quelqu'un sait-il faire cela ?

Merci d'avance pour votre aide !

Bonjour Laet,

Tu trouveras tous les éléments nécessaire grâce au site de Jacques Boisgontier.

http://boisgontierjacques.free.fr/pages_site/formulairecascade.htm#Cascade3Niv

Tu peux commencer à regarder un peu pour créer un fichier et au besoin le forum est là.

Bonjour Florian,

Merci pour ta réponse rapide en plus.

Ok, je vais aller voir

Pour précision, je voudrais juste que la macro nomme les plages de cellules qui contiennent les articles, pour chaque fournisseur.

Bonjour, Salut à tous !

Pour précision, je voudrais juste que la macro nomme les plages de cellules qui contiennent les articles, pour chaque fournisseur.

Ça, c'est un peu prématuré ! Il convient d'abord que tu fournisses, sur fichier, le modèle d'organisation de tes données. On pourra alors voir si elles se prêtent spontanément et de quelle façon à la constitution de listes-cascade, s'il faut éventuellement te conseiller de les organiser autrement, ou bien définir un traitement approprié, par exemple extraction de listes par filtrage avancé...

Cordialement.

Pour préciser, j'ai besoin que la macro nomme des plages de cellules : voir fichier en PJ

Si quelqu'un sait faire... je suis preneuse !

niveau vba débutant

Je précise que les fournisseurs/Articles sont régulièrement mis à jour...

Mais toujours insérés dans la zone nommée "Zone Art" (voir fichier excel)

Têtue avec ça !

J'espère que tu vas comprendre le mécanisme...

D'abord on renomme ta ZoneArt en dynamique :

=DECALER(Article!$G$1;;;NBVAL(Article!$G:$G);2)

car elle n'est pas figée et évoluera certainement...

Tu as besoin d'une liste fournisseurs uniques pour alimenter ta liste en B2.

On va la créer par extraction (filtrage avancé) des éléments uniques de la première colonne de ZoneArt :

Sub LstFournisseurs()
    Worksheets("Cde").Range("B2").ClearContents
    With [ZoneArt]
        .Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 2), _
         order2:=xlAscending, Header:=xlYes
        [LstFou].Offset(1).ClearContents
        With .Resize(, 1)
            .AdvancedFilter xlFilterCopy, , .Worksheet.Range("O1"), True
        End With
    End With
End Sub

Cette macro opère un tri pour classer ta zone dans l'ordre, sur les 2 colonnes, puis extrait les éléments de la première colonne pour en faire une liste en O.

Cette liste est nommée LstFou toujours en dynamique, à partir de O2 :

=DECALER(Article!$O$2;;;NBVAL(Article!$O:$O)-1)

Elle est affectée en liste de validation à B2.

La macro est affectée à un bouton mise à jour, à utiliser lors de changements dans la zone (tant que pas de changement, pas de raison de mettre à jour). Pourquoi un bouton ? (on aurait pu le déclencher automatiquement) Parce que 2 colonnes, et si ayant tapé une donnée dans une colonne, ta liste se trouve retriée avant que tu aies tapé la valeur de la seconde, ce serait gênant...

La première ligne de la macro, effaçant B2 au cas où une valeur y serait sélectionnée, est destinée à éliminer un effet pervers de la liste de validation en cours d'utilisation sur le filtrage, qui se manifesterait par l'extraction du seul élément sélectionné... ce qui désorganiserait le dispositif. On prend donc la précaution de l'effacer pour que tout reste dans l'ordre.

A noter également qu'avant l'extraction, on efface la liste en O, en prenant soin d'en laisser une ligne (qui sera remplacée lors de l'extraction), car si on l'efface en entier, le nom ne serait plus défini, et pourrait générer des erreurs.

Etape suivante : la liste d'articles correspondant au fournisseur sélectionné qui devra apparaître en B7.

Le déclencheur va être le choix d'un fournisseur en B2, déclenchement assuré automatiquement par une procédure évènementielle Change :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$2" Then
        With Me.Range("B7")
            .ClearContents
            .Validation.Delete
        End With
        [Crit].Cells(2, 1) = Target
        LstArticles
    End If
End Sub

Cette procédure se lance automatiquement lorsque la valeur de B2 sur la feuille Cde est modifiée.

Elle efface B7, qui le cas échéant ne correspond plus au nouveau choix en B2. Et élimine aussi la liste de B7, car comme on n'efface pas la totalité des listes nommées pour que le nom ne cesse pas de référer à une plage, il demeurerait une liste, inadéquate, si la cellule B2 est effacée sans qu'un nouveau nom soit sélectionné...

Elle reporte ensuite la valeur de B2 dans la zone de critères, nommée Crit, placée en M1:M2 de la feuille Article, avant de lancer la procédure qui va constituer la liste d'articles correspondant au choix en B2.

Sub LstArticles()
    [LstArt].Offset(1, -1).Resize(, 2).ClearContents
    If [Crit].Cells(2, 1) = "" Then Exit Sub
    With [ZoneArt]
        .AdvancedFilter xlFilterCopy, [Crit], .Worksheet.Range("Q1:R1"), True
    End With
    Worksheets("Cde").Range("B7").Validation.Add xlValidateList, , , "=LstArt"
End Sub

Cette procédure efface la liste existante (en enconservant une ligne pour les raisons qu'on a vu...), procède à l'extraction des éléments de ZoneArt correspondant au critère (choix en B2), et réaffecte la liste de validation à B7.

Si B2 a été effacée, la procédure s'interrompt après effacement de la liste, et ne procède ni à l'extraction, ni à la réaffectation de la liste de validation.

Noter que l'extraction s'opère sur les deux colonnes et le résultat est placé dans les colonnes Q:R, mais seule la colonne Articles (R) est nommée LstArt à partir de R2, en dynamique, comme toujours puisque ces listes sont de taille variable :

=DECALER(Article!$R$2;;;NBVAL(Article!$R:$R)-1)

Lors de l'effacement dans le code, on redimensionne la plage sur 2 colonnes en la décalant de -1.

Dernière info sur le code : on cible la feuille Article lorsqu'il en est besoin à partir de plages nommées qui y sont situées : le nom de feuille peut donc changer sans qu'il y ait de modification à apporter au code. Il n'en est pas de même pour la feuille Cde, qui est ciblée par son nom dans le code.

La procédure évènementielle se trouve dans le module de la feuille Cde. Les autres dans Module1.

Cordialement.

J'ai compris le principe et la lecture des vba

Je vais tester de ce pas.

Un grand merci pour le temps déjà consacré et ton aide précieuse

Têtue, moi ? non, persévérante !

Bonjour !

Après test, c'est vraiment parfait.

maintenant, j'ai transposé tout ça dans mon fichier de travail, un peu plus complet que le fichier envoyé sur le site.

J'ai 6 colonnes dans "ZoneArt" (de G à L)

La macro me stoppe à l'endroit suivant et je ne sais pas pourquoi...

Sub LstArticles ( )

[LstArt].Offset(1, -1).Resize(, 2).ClearContents

A priori, on fait référence ici aux deux seules premières colonnes de ZoneArt transposées vers Q:R donc...

J'avais testé en ajoutant deux colonnes dans le fichier reçu complété avec les macros (donc 4 colonnes) et ça fonctionnait

Merci encore pour votre temps et votre aide !

Laet

Visual Basic me dit

"Erreur d'exécution '424':

Objet requis

Bonjour Laet,

Tu a écrit :

[LstArt].Offset(1, -1).Resize(, 2).ClearContents

A priori, on fait référence ici aux deux seules premières colonnes de ZoneArt transposées vers Q:R donc...

alors c'est peut-être avec : [ZoneArt].Offset(1, -1).Resize(, 2).ClearContents

dans tous les cas, sur la feuille de calcul : fais Ctrl F3 pour vérifier les noms définis.

dhany

Bonjour,

C'est bien la suppression des 2 colonnes issues du filtrage qui sont effacées, sauf première ligne pour ne pas casser la référence au nom... Le filtrage sera ensuite renouvelé. Si tu avais fait un essai fonctionnel sur le modèle, pas de raison que cela ne marche pas sur ton fichier final.

L'erreur 424 peut provenir du fait que lors du transfert, la référence au nom s'est trouvée cassée. C'est le cas si la cellule R2 est vide. Dans ce cas tu peux la mettre en commentaire (apostrophe devant), le temps de relancer une opération (sélection en B2 qui reconstruira la liste en B7... puis tu enlèves le commentaire. Ou même, plus simplement, tu tapes un caractère quelconque en R2, cela devrait suffire.

Si cela ne suffit pas, vérifier dans le Gestionnaire de noms que tu n'as pas de #REF! dans la formule de ce nom. Là il faut la reconstruire...

Cordialement.

Salut Dhany,

Merci pour ton conseil mais tout est ok de ce côté là...

Bonjour MFERRAND,

Effectivement, ne pas laisser R2 vide était un bon conseil !

Pas d'erreur REF dans le gestionnaire de nom.

Maintenant, j'ai une nouvelle erreur :

erreur d'exécution '1004' :

Le nom de champ est incorrect ou manquant dans la zone d'extraction.

Sur la macro Sub LstFournisseur ( ), au niveau de la ligne :

.AdvancedFilter xlFilterCopy, , .Worksheet.Range("O1"), True

Dur dur les débuts sur vba... mais j'aime bien et heureusement que vous m'aidez !

Merci encore.

Vérifie que tu as bien un nom de champ dans ta première colonne de données (Fou dans le modèle). Si tu n'as pas le même en O1, tu l'effaces, et tu relances. (Ou même, tu l'effaces, cela éliminera éventuellement des écarts dus à des caractères invisibles, et le filtrage le réintroduira).

Merci pour ces précisions, mais toujours pas réussi...

Je remets le fichier en PJ des fois que !

Si tu modifies les noms de champs sans les modifier aussi dans la zone cible du filtrage et la zone de critère, le filtrage va hoqueter !

C'est rétabli pour M1, O1 et Q1:R1

J'ai aussi étendu A15 à A15:A25 (on traite toute la plage de la même façon) : modifié dans _Change et dans LstArticles.

J'ai aussi fusionné ta zone de choix du fournisseur, et ajusté dans LstFournisseurs :

....Range("H15").MergeArea....

sinon erreur à l'effacement.

Cordialement.

Bonjour MFERRAND,

Oups, j'ai apparemment fait des bêtises...

Mais pour bien comprendre, pouvez-vous préciser cela :

" Si tu modifies les noms de champs sans les modifier aussi dans la zone cible du filtrage et la zone de critère, le filtrage va hoqueter !

C'est rétabli pour M1, O1 et Q1:R1"

Cela veut-il dire que G1, M1, O1 et Q1 doivent être identiques ?

ce que vous appelez les "champs" sont les en-têtes de colonnes de ZoneArt ?

et la "zone cible du filtrage" = colonnes O et Q:R ?

enfin la "zone de critère" = colonne M ?

Bon, j'apprends...

Je m'y remet ce midi, en essayant de ne plus faire de bêtises !!!

Merci pour votre aide, j'apprécie votre patience et vos conseils pour apprendre.

Cordialement.

Bonjour,

Oui, les intitulés de champs sont ta ligne d'en-tête de ton tableau de base, ZoneArt.

Le filtre avancé (ou élaboré, qui était son ancienne appellation) ne fonctionne que sur un tableau doté d'intitulés de champs. Il permet de filtrer ton tableau sur place ou en copiant le résultat à un autre emplacement (qui peut être également sur une autre feuille). Tu peux noter qu'il procède par copie, qui reproduit exactement ce qui est dans le tableau de base, y compris la mise en forme.

On utilise normalement une zone de critères pour définir les critères de filtrage. Cette zone comporte au moins 2 lignes et autant de colonnes que tu as de critères à définir. La première ligne comporte les en-têtes de champs pour lesquels tu définis un critère, qui doivent correspondre exactement à ceux du tableau pour être reconnus. Sur la seconde tu mets la valeur du champ sur laquelle tu filtres.

Ici on n'utilise qu'un critère pour filtrer sur le champ Fournisseurs, intitulé qui doit obligatoirement figurer en M1, et en M2 le nom du fournisseur, critère de filtrage sur ce champ. On filtre sur ce critère 2 colonnes de ton tableau : Fournisseurs et Articles. Filtrant par copie sur un autre emplacement, si cet emplacement est vide, le filtre va y reproduire les intitulés de champs et au-dessous les lignes du tableau (limitées au deux colonnes que l'on filtre) dont la valeur du champ Fournisseurs correspond au critère, ce qui nous fournit dans la 2e colonne les articles associés à ce fournisseur. Si l'emplacement n'est pas vide, les intitulés de la 1re ligne doivent reproduire les intitulés de champs du tableau.

Si tu utilises plusieurs critères (plusieurs colonnes dans ta zone de critères, les critères définis sur la 2e ligne sont associés par ET. Le filtre doit les retrouver tous dans ta ligne de tableau pour la retenir.

Si tu utilises plus de 2 lignes, les critères que tu mets par exemple sur la 2e et sur la 3e ligne, pour le même champ, sont associés par OU. Que l'un ou l'autre soit trouvé fera retenir la ligne.

L'autre utilisation du filtrage avancé, utile pour faciliter la constitution de listes d'éléments uniques, avec ou sans critère. Lorsqu'on filtre la colonne Fournisseurs en indiquant qu'on veut des éléments uniques, le résultat sera une liste des fournisseurs, dans laquelle chaque fournisseur ne se retrouvera qu'une fois.

Même chose si on filtre sur 2 colonnes en demandant des éléments uniques avec un critère pour n'extraire que les lignes concernant un fournisseur donné. Dans ton tableau les couples fournisseur-article sont uniques, mais supposons que tu aies une colonne supplémentaire définissant une caratéristique de l'article, la couleur par exemple. Un article existant en 3 couleurs, tu retrouverais 3 fois le couple fournisseur-article : en filtrant pour obtenir des éléments uniques le filtre ne le renverrait qu'une fois et tu obtiendras une liste d'articles du fournisseur dans laquelle chaque article ne se retrouvera qu'une fois.

Tout cela relève d'Excel, pas de VBA, mais en la matière VBA ne faisant qu'utiliser un outil Excel, c'est ce dernier qui conserve la main pour dire tout va bien lorsqu'il renvoie le résultat ou stop ! je n'y retrouve pas mes petits lorsqu'un élément ne colle pas avec ce qu'il attend.

Le plus souvent l'erreur que tu obtiens sera l'erreur 1004, dont tu peux être sûre qu'elle relève d'Excel et non de VBA, ce peut être aussi l'erreur 424 (objet requis) qui relève le plus souvent d'Excel (dont certaines sont d'ailleurs certainement des bogues que Microsoft n'a jamais rectifiés et qu'il faut contourner, comme l'impossibilité d'affecter une feuille copiée à une variable objet lors de la copie, on doit procéder en deux temps, alors qu'on peut le faire pour un classeur lors de son ouverture...)

Pour bien comprendre, il y a quelques précautions à prendre lors de la mise en place initiale, qui t'expliqueront sans doute certaines erreurs que tu as obtenues.

Je commence par créer la liste de fournisseurs, pour cela dans un premier temps je place dans la macro le tri de la base (qui n'a pas d'effet sur le filtrage, mais qui permettra d'obtenir les listes dans l'ordre alphabétique. Ensuite le filtrage d'éléments uniques de la colonne Fournisseurs.

(NB- remarque au passage que le code de filtrage tient en une ligne de code, et qu'il s'agit d'une des rares opérations qu'il est plus rapide de coder en VBA, écriture du code incluse (en étant un peu entraîné), que de réaliser manuellement...)

A ce stade, ma zone cible en O est vide. Je lance la macro une première fois, qui va me la remplir.

Une fois remplie, je peux créer le nom de la liste, et placer ce nom en validation, opérations qui me seraient refusées tant que la zone était vide.

Ensuite seulement je complète ma macro pour y introduire l'effacement (partiel) de la liste antérieure, qui aurait déclenché une erreur tant que la liste n'avait pas d'existence.

Et il faudra opérer de façon semblable pour la deuxième liste en ayant pris soin préalablement d'opérer un choix de fournisseurs dans la cellule où l'on a placé la 1re liste déroulante, de façon que lorsque je voudrais placer la seconde liste en validation, cette dernière trouve bien une liste construite sur le 1er choix effectué et ne me renvoie pas un message de liste erronée ou inexistante...

Après, une fois l'ensemble mis en place, ça tourne... On n'efface pas les en-têtes des zones cibles, car nos listes étant définies avec des noms dynamiques (longueur de la liste calculée sur le nombre de valeurs de la colonne), l'effacement fausserait le dimensionnement de la liste, mais l'intitulé correspondant alors à celui de la base, aucun problème lors des filtrages ultérieurs (sauf si on change des noms de champs dans la base !).

Tant que j'y suis, éléments qui n'ont pas d'utilité immédiate sur ton travail actuel, mais pourront t'être utile ultérieurement si tu poursuis en VBA... On a opéré ici la mise en place de liste de validation à partir de listes nommées, lorsqu'on le fait en VBA, la formule à placer en validation est "=NomListe", après avoir pris la précaution de supprimer une liste de validation antérieure éventuelle. Mais tu sais qu'en manuel, tu peux insérer des listes en tapant les noms séparés par des points-virgules, tu peux faire de même en VBA en tenant compte que le séparateur sera obligatoirement la virgule... De même si tu définis une validation par formule en VBA, la formule devra être composée en anglais...

A l'inverse si tu dois coder en VBA une mise en forme conditionnelle (ce que je ne conseille jamais, sauf lorsque c'est justifié par une détérioration progressive de la MFC, notamment par fractionnements lorsque l'on opère des suppressions ou insertions de lignes), là la formule se compose en français (dans la langue sur laquelle est paramétré Excel).

Sur ce, bonne continuation et à une prochaine...