Listes déroulante cascades 4 niveaux

Bonjour à tous! je galère pour établir un bon de commande que j'utiliserai pour mes clients! j'ai réussi à réaliser les 2 premiers niveaux de la cascade mais pour le niveau 3 et 4 impossible! je m'explique

en choix 1: j'ai créé une liste que j'ai nommé "couverture" (cellule E6)

en choix 2 : j'ai crée une liste en indirect par rapport à la cellule E6

jusque là tout fonctionne bien, ensuite en fonction du choix de E6, je voudrai qu'en choix 3 (cellule b16) un certain choix soit fait et en cellule B16 en fonction du choix 3. hors là je bug je ne sais comment faire! j'ai lu sur le net la fonction =decaler qui m'a l'air pas mal si on veut ajouter des valeurs par la suite mais je n'arrive pas à le mettre en forme. de même que mon tableau (en onglet paramètre) je pense qu'il est mal établi mais je ne sais pas non plus comment faire pour qu'il soit assez dynamique pour que mes listes déroulantes fonctionnent!

une bonne âme charitable pour m'aider?

Merci

Bonjour,

Parti ainsi, tu vas continuer à galérer : démarrer sur une organisation fonctionnelle sur des listes-cascade à 2 niveaux ne te permettra pas de passer de la même façon sur les listes à 3 ou 4 niveaux !

Si tu as effectivement à mettre en place des listes à 4 niveaux de choix, le choix final étant celui réalisé au 4e niveau, il te faut commencer par lister en base de données toutes les chaînes de choix successifs conduisant à chacun des choix de niveau 4.

Si par exemple tu as 3 choix possibles de niveau 1, si chacun génère 3 choix possibles (on les consière égaux en nombre pour simplifier...) de niveau 2, tu auras alors 9 choix à ce niveau, et si chacun génère 3 choix possibles au niveau suivant, tu auras 27 choix de niveau 3, en faisant de même pour le niveau suivant, cela t'amène à 81 choix de niveau 4.

Ce qui te conduit à construire une base de 81 lignes sur 4 colonnes, chaque colonne concernant un niveau, et chaque ligne constituant la succession des 4 choix successifs conduisant à chacun des choix de niveau 4.

A partir de cette base on procède à des extractions par filtrage avancé d'éléments uniques sur 1, 2 ou 3 colonnes pour produire des listes utilisables (mais chaque chose en son temps)...

Si je vois bien dans ton fichier un niveau 2 dépendant du niveau 1, je ne vois pas où est la dépendance d'un niveau 3 avec le niveau 2 (?), quant au niveau 4, on la voit mais elle n'est qu'ébauchée.

Ensuite si l'on peut opérer par formules pour disposer de listes déroulantes, sans utiliser un minimum de code VBA l'utilisation sera à tout le moins inconfortable.

Exemple basique : on a effectué les 4 niveaux de choix, puis on est conduit à modifier le choix 1, si la liste de choix 2 va s'adapter, le choix inscrit dans la cellule demeure, source d'erreur potentielle, de même pour les choix 3 et 4, pour lesquels les listes ne seront pas mises à jour avant le choix antérieur pour chacune... Si l'on n'a pas au moins un effacement automatique des choix dépendants d'un élément qu'on modifie, on se crée des difficultés. Et je trouve également sécurisant de ne pas avoir de liste tant que le choix antérieurs n'est pas réalisé.

Et le filtrage avancé est une opération quelque peu fastidieuse manuellement, alors qu'elle est à la fois plus simple et plus rapide en VBA. Ce qui facilite la mise à jour à partir de changements dans la base.

Mais le point de départ demeure la constitution de la base exhaustive des choix, dont tout dépend.

Cordialement.

Bonsoir Mferrand,

Merci pour toutes ces explications! je vais d'abord constituer cette fameuse base avec tous mes différents choix et puis je verrai ce que je peux faire. encore merci pour ton aide!

belle soirée

Reviens ensuite, car tu auras besoin d'explications.

Bonjour Mferrand, vous allez bien?

J'ai constitué ma base de données en espérant avoir fait tout ca comme un pro!

Par contre là je suis complètement bloqué, je ne sais pas par où commencer!

est-ce possible de faire en sorte que je puisse mettre à jour ma base de données et que ca se mette à jour dans la liste déroulante?

Merci d'avance pour votre aide

Bonjour,

J'ai commencé... mais vais être occupé à autre chose pour quasiment toute la soirée... Un peu de patience donc.

@+

Pas de souci, il n'y a pas le feu! c'est déjà bien gentil à toi d'y regarder!

Passe une bonne journée

Bonjour,

J'avais basculé en cuisine dans une préparation de repas avec une partie de mes enfants et petits-enfants...

Parfaite, ta base de données, avec juste un petit détail à rectifier : il lui faut une ligne d'en-tête, une seule, et pas de ligne vide. J'ai donc supprimé la ligne 3, ce qui laisse en ligne d'en-tête choix1, choix2..., intitulés que tu pourras modifier à ta convenance (en veillant à ce qu'ils soient rigoureusement identiques partout où ils apparaissent dans les opérations de filtrage.

J'ai également supprimé la ligne1, vide, ce qui élimine un risque qu'on y place des indications susceptibles d'entraîner des erreurs ultérieures.

Et j'ai nommé Couv la 1re colonne de ta base, nom dynamique défini par la formule suivante :

=DECALER(paramètres!$A$1;;;NBVAL(paramètres!$A:$A))

Le nom recouvre donc la zone utilisée par des données en colonne A, incluant l'en-tête (car nécessaire lors des filtrages), et s'adaptera aux variations, que tu ajoutes ou enlèves (sans laisser de ligne intermédiaire vide) des lignes.

Le principe de cette formule de nommage repose sur la fonction DECALER : on applique à la cellule supérieure gauche de la plage qu'on veut nommer un décalage nul, en ligne et colonne, et un dimensionnement en lignes calculé sur le nombre de valeurs de la colonne (et le cas échéant un dimensionnement en colonnes calculé pareillement sur la ligne d'en-tête ou indiqué directement car le nombre de colonnes est souvent fixe). Si l'on excluait l'en-tête du nom, la cellule de référence pour nommer serait A2 et l'on ôterait 1 au dimensionnement en lignes pour compenser la présence de l'en-tête dans la colonne. Tu verras cela dans le Gestionnaire de noms.

Je ne nomme habituellement qu'une colonne car cela suffit pour atteindre n'importe quelle colonne ou groupe de colonne de la base par décalage et redimensionnement.

Dans la foulée, j'ai donc aussi ajouté un bouton (objet Shape) à utiliser pour opérer la mise à joursi tu modifies un ou plusieurs éléments de la base. Un clic sur ce bouton déclenchera une procédure de mise à jour :

Sub MajBase()
    With [Couv].Resize(, 4)
        .Sort key1:=.Cells(1, 4), order1:=xlAscending, Header:=xlYes
        .Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 2), _
         order2:=xlAscending, key3:=.Cells(1, 3), order3:=xlAscending, Header:=xlYes
        [List1].Offset(1).ClearContents
        [Couv].AdvancedFilter xlFilterCopy, , .Worksheet.Range("H1"), True
    End With
End Sub

Ce code est fort simple : on opère sur la plage Couv, étendue à 4 colonnes (soit ta base entière) un tri alphabétique croissant sur la 1re colonne, ainsi que sur les 3 autres en cas d'égalité de la colonne précédente.

On efface la plage nommée List1 (voir ci-dessous ce dont il s'agit) en décalant l'effacement d'une ligne, de façon que le nom puisse toujours référer à une plage existante (sans cela risque d'erreur) tout en éliminant la possibilité de laisser subsister un nom de l'ancienne liste disparu...

La dernière ligne exécutable consiste à appliquer un filtre avancé à la plage Couv, par copie du résultat sur un autre emplacement (H1), sans définir de critère de filtrage mais en indiquant au filtre de ne retenir que des éléments uniques. Le résultat sera : l'intitulé de la colonne en H1 (choix1), et dans les lignes qui suivent la liste des noms présents dans cette colonne, en un seul exemplaire.

Cette liste est nommée List1, à partir de H2 (nom dynamique, mais ici on exclue l'en-tête), nom qui servira à placer la liste déroulante en Bc!E6.

Note pour parfaire ton appréhension du code précédent que l'on utilise une instruction With... End With, l'expression suivant With désignant ta base : ce qui évite d'avoir à le répéter, toute expression à l'intérieur de cette instruction débutant par un point réfère à l'objet sous With, soit à ta base, c'est le cas des .Cells... indiquant les cellules de référence à prendre en compte pour chacun des niveaux de tri, et c'est le cas de l'expression .Worksheet.Range("H1") désignant la cible du filtrage. Ce qu'il est intéressant que tu retiennes, c'est qu'à partir de la base (soit une plage de cellule nommée) on atteint la feuille dans laquelle se trouve cette plage (son objet parent) pour ensuite atteindre une autre plage en la référençant à partir de ses références dans la feuille (H1). A partir d'un nom de plage, on peut ainsi cibler n'importe quelle autre cellule ou plage dans la feuille sans jamais faire référence au nom de cette feuille : tu peux donc changer le nom de cette feuille sans que cela ait la moindre incidence, Excel fera la substitution pour ce qui est de la définition du nom et le code VBA ignorant le nom de feuille n'en sera point affecté...

Hier soir j'étais peut-être allé un peu vite au démarrage, et considéré que tes choix 1 à 4 étaient réalisés dans les cellules E6 à E9 de Bc.

Cela me semble OK pour le choix 1 en E6, et le choix 2 en E7, mais je vois ce matin que tu as noté les choix 3 et 4 en regard de B16 et B17. Peux-tu me confirmer qu'il s'agit bien des cellules où se feront respectivement ces choix ?

Et dans l'immédiat, il faudrait que je sache à quoi sont dévolues : E8, E9, ainsi que E16 et E17 ?

Cordialement.

Bonjour Mferrand,

Wouaw... pas de mots pour toutes ces explications! j'y vois beaucoup plus clair! je ne sais comment te remercier!

"mais je vois ce matin que tu as noté les choix 3 et 4 en regard de B16 et B17. Peux-tu me confirmer qu'il s'agit bien des cellules où se feront respectivement ces choix ?

Et dans l'immédiat, il faudrait que je sache à quoi sont dévolues : E8, E9, ainsi que E16 et E17 ?"

pour y répondre le choix 3 se fera bien en b16 et le choix 4 en b17.

les cellules e8,e9, e16 et e17 seront des liste de choix sur 2 niveaux que je ferai plus tard car 2 niveaux je sais maitriser

encore un tout grand merci

Ok ! Le système que je te propose va consister en des listes nommées : List1 à List4, qui seront affectées à la validation pour produire les listes déroulantes des choix 1 à 4. Donc simplification pour le système validation par listes...

Mais d'un autre côté il faut donc mettre à jour les listes chaque fois que tu fais ou modifies une sélection :

List1, la liste de 1er niveau, elle, ne bouge pas (sauf modification de la base), elle sera donc toujours présente en E6.

Lorsque tu fais un choix1 ou modifies E6, il faut qu'on efface les choix éventuels en E7 et B16, efface les listes de choix 2 et 3, et lance la mise à jour de List2 et List3, qui seront obtenues également par filtrage avancé d'éléments uniques de la base mais cette fois sur 2 colonnes, 1 et 2 pour List2 et 1 et 3 pour List3, avec utilisation d'un critère cette fois, constitué par ton choix 1.

Pour pouvoir effacer les éléments de choix antérieurs lorsque tu interviens sur E6, on doit passer par une procédure dite évènementielle, qui s'exécutera automatiquement chaque fois que la valeur de E6 est modifiée, on profite de l'occasion pour lui faire éliminer les listes antérieures (en fait, on les réduit à un élément, par commodité... ce sera à toi de me dire si cela peut être gênant et t'induire en erreur, auquel cas on peut faire disparaître cet élément résiduel...), et si nouvelle valeur en E6, lancer la mise à jour de List2 et List3 qui seront alors mises en adéquation avec E6.

La même chose interviendra lors de modification de B16, on efface B17, on efface List4, et on mettra à jour List4 en fonction de la nouvelle valeur de B16, par un filtrage avancé sur 3 colonnes (1, 3 et 4) toujours d'éléments uniques, avec comme critères les valeurs de E6 et B16.

Cordialement.

tu peux faire au plus simple pour toi, moi je m'adapte! le principal était de pouvoir filtrer les choix, si après les cellules ne s’effacent pas ce n'est pas grave, tant que le choix de la liste est bon c'est le principal!

mais fais au plus facile pour toi!

hâte de voir le résultats , encore milles mercis

Bonjour,

Pas eu le loisir de finaliser hier, mais ça arrive !

Je ne reviens pas sur les modifications de la base et la mise à jour, laquelle reconstitue alors la liste de choix1...

Je reprends à la suite :

Sur la feuille paramètes, la liste de choix1 (List1) se trouve en colonne I. Elle est nommée avec la formule:

=DECALER(paramètres!$I$2;;;NBVAL(paramètres!$I:$I)-1)

De même, les listes de choix2, 3 et 4 (List2, List3 et List4) se trouvent en colonnes L, O et S, et sont semblablement nommées...

Pour que les formules de nommage jouent leur rôle, il ne doit rien y avoir en dessous des listes dans les colonnes citées (il en était de même pour la colonne A en ce qui concerne la base).

Sur la feuille Bc, les listes (List1 à List4) sont affectées en listes de validation, respectivement aux cellules E6, E7, B16 et B17.

En E6 (choix1), la liste déroulante est toujours présente. Pour les autres choix, une liste n'apparaîtra que si un choix antérieur a été fait : les listes de choix2 et choix3 apparaîtront lorsque le choix1 dont elles dépendent est fait, la liste de choix4 lorsque le choix3 est fait.

C'est à ce stade qu'un code VBA intervient pour effectuer automatiquement les opérations qui seraient fastidieuses ou sources d'erreurs manuellement : effacement des choix devenus caducs et élimination des listes lorsqu'il n'y a pas de choix antérieur valide.

C'est une procédure d'évènement qui fait ce travail et se lancera automatiquement lorsqu'un modification intervient en E6 ou B16 dont dépendent d'autres choix.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, isect As Range
    Set isect = Intersect(Target, Union(Me.[E6], Me.[B16]))
    If isect Is Nothing Then Exit Sub
    For Each c In isect.Cells
        Select Case c.Address
            Case "$E$6"
                Me.Range("E7").ClearContents
                Me.Range("B16").ClearContents
                With [List2]
                    .Offset(1).ClearContents: .Cells(1, 1) = Chr(160)
                End With
                With [List3]
                    .Offset(1).ClearContents: .Cells(1, 1) = Chr(160)
                End With
                If Me.Range("E6") <> "" Then MajLst2Lst3
            Case "$B$16"
                Me.Range("B17").ClearContents
                With [List4]
                    .Offset(1).ClearContents: .Cells(1, 1) = Chr(160)
                End With
                If Me.Range("B16") <> "" Then MajLst4
        End Select
    Next c
End Sub

Cette procédure nous fournit avec son argument Target, la plage modifiée, permettant de l'identifier. On détectera selon l'adresse de la cellule modifiée s'il convient d'intervenir et comment.

En première lecture, le code peut paraître compliqué (plus qu'il ne serait nécessaire) mais ce n'est pas le cas. Lorsque l'utilisateur fera un choix dans une liste, il intervient dans une seule cellule, l'adresse de cette cellule sera directement identifiable, par contre s'il efface il peut fort bien effacer simultanément plusieurs cellules, et l'adresse de la plage effacée ne sera pas en correspondance directe avec les adresses des cellules surveillées...

On laisse donc à l'utilisateur toute latitude pour opérer comme il l'entend. Toute modification qu'il apportera dans la feuille va déclencher la procédure : on commence donc à définir l'intersection de la plage modifiée par lui avec la plage (multizone) constituée par les cellules surveillées E6 et B16. Si cette intersection n'existe pas, on s'arrête là. Si elle existe, on parcourt les cellules de l'intersection (au cas où il y en aurait plusieurs) et si on tombe sur E6 ou sur B16, on intervient :

E6: on efface E7 et B16, choix dépendants de E6, on efface les listes List2 et List3, sauf le premier élément qu'on remplace par un caractère invisible (espace insécable).

(NB- on n'intervient pas sur B17, dépendant de B16, lors de modif. de E6, c'est l'effacement de B16 qui relance la procédure qui interviendra alors sur cette nouvelle modif.)

Si un choix ou un nouveau choix a été fait dans E6, on lance la mise à jour de List2 et List3...

B16: on efface B17, choix dépendant de B16, on efface List4 en laissant un premier élément invisible, et si un choix est fait dans B16 on lance la mise à jour de List4...

Revenons sur paramètres : en F1 on a porté la mention choix1, intitulé de la 1re colonne de la base dont est issue la liste List1 dévolue au choix à faire en E6. en F2 on récupère la valeur de E6 (formule simple). La plage F1:F2 constitue notre zone de critère pour filtrer les éléments de la base correspondant au choix1 effectué et constituer les listes List2 et List3 correspondant à ce choix1.

On la nomme Crit pour l'utiliser plus commodément...

Sub MajLst2Lst3()
    With [Couv]
        .Resize(, 2).AdvancedFilter xlFilterCopy, [Crit], .Worksheet.Range("K1:L1"), True
        .Resize(, 3).AdvancedFilter xlFilterCopy, [Crit], .Worksheet.Range("N1:O1"), True
    End With
End Sub

Cette procédure, on l'a vu, est lancée lorsqu'un choix a été fait en E6. Elle se contente de filtrer la base (limitée à ses 2 premières colonnes, sur le critère du choix1 fait, et de coller le résultat : éléments uniques de ces 2 colonnes en K et L. List2 se trouve mise en jour en L.

Elle fait de même un second filtrage en l'étendant à la 3e colonne, sur le même critère, et colle le résultat composé alors d'éléments uniques des colonnes 1 et 3 en N et O. List3 se trouve mise à jour en O.

Les choses se passent semblablement pour List4 :

Sub MajLst4()
    With [Couv].Resize(, 4)
        .AdvancedFilter xlFilterCopy, [Crit].Resize(, 2), .Worksheet.Range("Q1:S1"), True
    End With
End Sub

Dans paramètres, en G1 on a placé la mention choix3, et en G2 on récupère le choix3 fait en B16. La plage F1:G2 constitue notre zone de critère pour définir la liste de choix4. (NB- on n'ajoute pas de nom : [Crit].Resize(, 2) redimensionne la zone de critère nommée sur 2 colonnes, définissant ainsi la zone de critère dont on a besoin). Le filtrage se fait sur la base entière, et l'extraction concerne les éléments uniques des colonne 1, 3 et 4 qui sont collés en colonnes Q, R et S. List4 se trouve mise à jour en S.

Si tu as besoin d'informations supplémentaires, n'hésite pas !

Cordialement.

Hello Mferrand, un tout grand merci pour toutes ces explications et ton travail accompli!

ce fichier est tout ce dont j'avais besoin, je ne sais comment te remercie!

Milles fois merci!!