Amélioration listes déroulantes (supprimer blancs/vides + cascade + ...)

Bonjour,

Je réalise le suivi de mes comptes sur Excel. Vous trouverez ci-joint un "extrait" que j'ai très largement simplifié pour l'exemple.

Sur la 1ère feuille ("Livre de comptes"), où sont regroupés toutes les opérations, je renseigne une rubrique sur plusieurs niveaux (catégorie, division, groupe, classe et sous-classe), via des listes déroulantes.

La "source" de ces listes déroulantes se trouve sur la 2ème feuille ("Rubriques"), sous forme de tableau.

J'aimerais améliorer cette saisie des rubriques, du coup voici mes questions :

1) Premièrement, la "disposition" pour lister les différentes rubriques est-elle "optimale" ? J'entends par là : est-ce utile la mise sous forme de tableau ? faut-il répéter la catégorie, division, etc. sur chaque ligne ? etc.

2) Comment supprimer les blancs/vides dans les listes déroulantes ?

3) Avec cette disposition (ou une autre), est-il possible de réaliser des listes déroulantes en cascade (afin de faciliter la saisie) ?

4) Est-ce possible que les choix effectués via les listes déroulantes (c'est à dire la saisie sur la 1ère feuille), se mettent à jour si on modifie la source (les rubriques sur la 2ème feuille).

Si je prends par exemple la rubrique "Loyers effectifs", que je trouve ça trop long et que je veux la changer par "Loyers". Si je le modifie dans la feuille "Rubriques", ça ne s'actualise pas automatiquement sur la 1ère feuille. Comment faire ?

Merci d'avance.

Bonjour,

Pour répondre à vos questions :

1) Non, je pense qu'il vaut mieux répéter les catégories parentes.

2) Pour supprimer les vides, on saisit dans la formule de validation :

=DECALER(Source;;;NBVAL(Source))

C'est un exemple simplifié car dans votre cas, ce serait plus complexe...

3) Avec cette disposition, mais surtout avec une autre, il est possible d'avoir des listes en cascade. Cela suggère quand même d'utiliser une petite macro pour automatiser la remise à blanc des cellules lorsqu'une catégorie parente est modifié (Excel seul ne gère pas ça).

4) Ca s'actualise bien sûr (ça dépend des valeurs des cellules entrées en source de validation). Cependant, comme je l'ai dit sur le point 3, lorsqu'une valeur est sélectionnée, elle demeure jusqu'à ce qu'on la change.

MAIS, selon moi, ce n'est peut-être pas la meilleure façon de procéder. Vous avez beaucoup de subdivisions et sur l'exemple pas tant de valeurs que ça. Vous pourriez éliminer certaines catégories ou partir directement de la dernière sous-catégorie pour sélectionner une valeur et vous pourriez obtenir les autres valeurs par formule.

Cdlt,

Merci pour votre réponse

1) Le problème en répétant les catégories parentes, c'est que je trouve ça beaucoup moins visuel et plus "encombrant", et surtout je vais me retrouver avec plusieurs fois la même occurrence dans mes listes déroulantes. Par contre, en restant sur ma solution, on ne peut pas vraiment se servir des filtres.

2) Effectivement je n'ai pas réussi à appliquer la formule pour mon cas..

3) Comment faudrait-il que je fasse ?

4) C'est justement le fait qu'une fois la valeur sélectionnée dans la liste, elle ne se met pas à jour (elle ne s'actualise pas) si on modifie la source (modifier le texte par exemple).

Je pense avoir compris ce que vous voulez dire sur votre dernier paragraphe, c'est à dire effectuer uniquement le choix/la saisie sur le dernier niveau de rubrique, et les autres niveaux seraient obtenus par formule, c'est bien ça ? Le problème avec ça, contrairement à l'exemple que j'ai énormément simplifié, je vais me retrouver avec environ 500 valeurs sur le dernier "niveau", donc ça risque d'être compliqué à s'y retrouver.

Bonjour nyko, hello 3GB

En effet, la proposition de 3GB est la plus simple.

On peut aller plus loin en effet en cascade comme le canal du midi et les 9 Écluses de Fonseranes ... pour cela j'avais fait un programme prêt-à-poser (jusque 8 niveaux). Avec quelques conditions, notamment que toutes les cases de la base soient renseignées

• J'ai donc reporté les différentes valeurs et rempli ensuite avec des espaces soulignés).
• J'ai mis 5 niveaux

Const nbZones = 5

• j'ai identifié le tableau des choix comme étant

[TabData]

• j'ai du supprimer la première colonne pour commencer directement par le choix le plus élevé (dépense/recette)
• j'ai ensuite affecté les noms zone1, zone2, ... zone 5 aux en-têtes des colonnes du livre de compte concernées

Const nbZones = 5

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Exit Sub

Dim data() As Variant
Dim choix() As Variant
Dim dico As Object
Dim i&, iData&, iZone&, plus&

    If Target.Count <> 1 Then Exit Sub
    plus = Target.Row - 1
    If plus = 0 Then Exit Sub

    ReDim choix(1 To nbZones)
    For i = 1 To nbZones
        choix(i) = Range("zone" & i).Offset(plus, 0).Value
        If Not Intersect(Range("zone" & i).Offset(plus, 0), Target) Is Nothing Then
            data = [TabData].Value
            Set dico = CreateObject("Scripting.Dictionary")
            For iData = 1 To UBound(data)
                flag = True
                If i > 1 Then
                    For iZone = 1 To i - 1
                        If choix(iZone) <> CStr(data(iData, iZone)) Then flag = False
                    Next
                End If
                If flag Then dico(CStr(data(iData, i))) = ""
            Next iData
            If dico.Count > 0 Then
                Target.Validation.Delete
                Target.Validation.Add xlValidateList, Formula1:=Join(dico.keys, ",")
            End If
            Exit For
        End If
    Next i
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Exit Sub

    If Target.Count <> 1 Then Exit Sub
    plus = Target.Row - 1
    If plus = 0 Then Exit Sub

    For i = 1 To nbZones
        If Not Intersect(Range("zone" & i).Offset(plus, 0), Target) Is Nothing Then
            If i < nbZones Then
                Application.EnableEvents = False
                For iZone = i + 1 To nbZones
                    With Range("zone" & iZone).Offset(plus, 0)
                        .Value = ""
                        .Validation.Delete
                    End With
                Next
                Application.EnableEvents = True
            End If
            Exit For
        End If
    Next

End Sub

@nyko, est-ce que cette proposition te convient ? merci pour ton retour ...

Bonjour,

Merci pour ta réponse @Steelson, et désolé pour mon retour tardif (j'étais indisponible car en congé).

Je ne suis pas sûr d'avoir compris la rapport avec les écluses, tu penses qu'il y a trop de niveaux et que ça risque d'être contraignant/fastidieux de tout renseigner à chaque fois ?

Cependant comme dit plus haut, le problème avec la proposition de 3GP, c'est à dire renseigner uniquement le niveau le plus bas (si c'est bien ça dont il parle ?), ça risque d'être compliqué de retrouver la bonne valeur dans la liste, car le dernier niveau sera composé de beaucoup de valeurs contrairement à ce fichier exemple (peut être 500 valeurs) ..

• OK pour le report des valeurs (je m'en doutais un peu), mais ça ne marcherait pas sans remplir le reste avec des espaces soulignés ?
• Est-ce obligatoire de supprimer la 1ère colonne ? Pourrait-on pas plutôt la déplacer en dernier ? Car cette colonne me permet de conserver un ordre.

De plus, si je veux passer à 7 niveaux, j'imagine qu'il faut :

• Remplacer nbZones = 5 par 7.
• Ajouter les 2 colonnes correspondantes sur les 2 feuilles.
• Créer les noms zone6 et zone7 avec les en-têtes.
• ... ?

Enfin, j'ai également constater ces "erreurs" :

• La rubrique "Logement, eau, gaz, électricité et autres combustibles" est "décomposée" sur plusieurs lignes dans la liste (certainement à causes des virgules ?).
• A chaque fois que l'on effectue un choix sur une liste, ça créer une erreur sur la cellule adjacente de droite. Erreur qui disparaît si on effectue un "suppr" sur la cellule ou si on fait un choix parmi la liste (par contre si on fait un choix, même problème sur la cellule adjacente de droite, etc.). Le fait de "laisser" l'erreur pose un problème ensuite quand je veux faire un tableau croisé dynamique par exemple.
• J'ai également eu un message d'erreur Excel à 2 reprises (je sais pas si c'est lié à ce fichier ou mon poste) :

Bonjour,

OK pour le report des valeurs (je m'en doutais un peu), mais ça ne marcherait pas sans remplir le reste avec des espaces soulignés ?

Est-ce obligatoire de supprimer la 1ère colonne ? Pourrait-on pas plutôt la déplacer en dernier ? Car cette colonne me permet de conserver un ordre.

ok, je regarde ces 2 points

La rubrique "Logement, eau, gaz, électricité et autres combustibles" est "décomposée" sur plusieurs lignes dans la liste (certainement à causes des virgules ?).

oui je confirme ! remplace la virgule par autre chose, un point, un / ? je n'ai pas de solution, la virgule st un séparateur VBA réservé

A chaque fois que l'on effectue un choix sur une liste, ça créer une erreur sur la cellule adjacente de droite. Erreur qui disparaît si on effectue un "suppr" sur la cellule ou si on fait un choix parmi la liste (par contre si on fait un choix, même problème sur la cellule adjacente de droite, etc.). Le fait de "laisser" l'erreur pose un problème ensuite quand je veux faire un tableau croisé dynamique par exemple.

je pensais avoir effacé la zone, je regarde

1. première colonne remise en place et macro adaptée en conséquence
2. il n'est plus nécessaire de compléter les rubriques par _, tu peux laisser vide
3. j'ai remplacé la virgule par un tiret
4. et je confirme que lors d'un nouveau choix, toutes les zones à droite sont effacées jusqu'au dernier rang, donc je ne comprends pas bien comment cela se passe chez toi ! voir vidéo ci-après
5. je n'ai pas d'erreur comme celle que tu trouves

Merci pour les modifs.

Par contre c'est bizarre, j'ai fait exactement la même manipulation que toi sur la vidéo, c'est à dire remplacer "Dépense" par "Recette" sur la 1ère ligne, puis quand je clique sur la cellule de droite, voilà ce que j'obtiens :

Tu peux également t'apercevoir que j'ai déjà cette erreur sur la 2ème ligne dès que j'ouvre le fichier.

Est-ce un réglage à faire, une case à cocher dans les options d'Excel ?

hum ... je ne connais pas la version 2019 !!

ce qui est sûr, c'est que j'ai bien mis dans la macro ceci

                    With Range("zone" & iZone).Offset(plus, 0)
                        .Value = ""
                        .Validation.Delete
                    End With

remise à blanc des zones suivantes et suppression de la validation

donc je suis sec sur ce coup, à moins de faire juste un essai très ciblé que je vais préparer ...

essaie ceci avec XL2019, cela pourrait permettre de cerner le problème

Sub test()
    Range("A2").Select
    With Range("B2")
        .Value = ""
        .Validation.Delete
        .Select
    End With
End Sub

Ca fonctionne avec ce fichier, ça vient de quoi du coup ?

ok, on va corser un peu l'affaire alors !

essai plus complet ... change la première donnée et regarde ce qui se passe sur la seconde

Au lancement du fichier, j'ai ce message :

Ensuite, une fois le fichier ouvert, j'ai celui-ci :

Par contre, lors de l'utilisation, je ne constate aucun problème (si je modifie la première liste, la seconde s'efface correctement et se "met à jour" avec les bonnes données, le tout sans aucune erreur).

Et celui-ci ?

Pour l'erreur, c'est normal, j'avais bricolé à partir d'un autre fichier !

ou alors https://forum.excel-pratique.com/excel/amelioration-listes-deroulantes-supprimer-blancs-vides-cascad... est-ce qu'il plantait ?

Ce fichier fonctionne également.

Par contre, j'ai toujours des erreurs concernant le fichier correspondant au lien.

et celui juste avant la modification que as demandée pour conserver la première colonne et ne pas avoir à compléter avec des _ il se trouve ici : https://forum.excel-pratique.com/excel/amelioration-listes-deroulantes-supprimer-blancs-vides-cascad...

Egalement des erreurs sur ce fichier.

Par contre, je viens juste de remarquer que je n'ai aucune erreur si j'effectue les choix sur des cellules en dehors du tableau mis en forme, que ce soit avec les 2 versions (celle sans première colonne et avec les "_", ou la version avec première colonne et sans les "_").

Je viens de voir une MFC que tu pourrais supprimer en mettant comme format des cellules de la colonne H

[Vert]0,00;[Rouge]0,00

mais cela n'a rien à voir avec ce que tu viens de découvrir !! un écart entre 2013 et 2019 ? je vais essayer le fichier sur 2016.