Excel Menu déroulant élimination des utilisés Résolu/Fermé

Question

Bonjour, 

J'ai réalisé un fichier avec choix par menus déroulants et suppression des utilisés.
Pour chaque mois je sélectionne chaque dimanche par un chiffre de 1 à 4 ou 5 suivant le nombre de dimanches du mois.
Chaque dimanche sélectionné est sorti de la liste pour les choix suivants.
J'ai mis un extrait de mon tableau sur le lien ci-dessous.
http://www.cijoint.fr/cjlink.php?file=cj201109/cijzx7sMky.xls

En dessous de chaque colonne des mois, je recherche les dimanches non programmés, et à partir de cette liste, je crée une deuxième liste en dessous de la première qui classe les résultats de la première liste dans l'ordre chronologique. C'est cette liste que j'utilise pour les menus déroulants. Lorsque cette liste est vide, le menu renvoi vers une cellule vide.

Le système fonctionne bien, mais je trouve le procédé un peu lourd, et je me demande s'il n'y aurait pas plus simple ou plus élégant.
Je pense aussi que les formules utilisées pourraient être optimisées mais je ne vois pas bien comment.
Mon tableau final comporte une programmation sur 5 ans avec 150 lignes par mois et cela 2 fois. Cela représente 18000 menus déroulants, sans compter les menus utilisés par ailleurs. Je n'ai pas encore appliqué ces menus sur l'ensemble de mon tableau. Il y a t'il une limitation sur le nombre de menus déroulants qu'Excel peut gérer ?

Merci à ceux qui pourront m'aider.


Configuration: Windows XP / Firefox 6.0.1

mic13710 · Answer

Bonjour,

Ci-joint ne fonctionne pas très bien ces derniers temps. Beaucoup de problèmes de connexion.
Le même fichier sur cjoint :
https://www.cjoint.com/?AIelmxuBxCp

eriiic · Answer

Bonsoir, 

Pas beaucoup de réponse à ta question... :-) 
Déjà parce que ta solution n'est pas si mauvaise et l'optimiser, si optimisation il y a, demanderai un peu de temps de recherche. 
Qcq questions :  
- 1 à 4 dans les listes déroulantes c'est un choix ou tu préfèrerais la liste de dates ? 
- s'il y a des dates dans la liste déroulante les 5 lignes avec les jours qui se colorent restent indispensables ? 
- 150 lignes mais toujours 4 ou 5 sélections maxi sur ces 150 lignes ? 

Pour l'instant la seule amélioration que je vois ça serait de le gérer en vba.  
Un double-clic sur une cellule te proposerait les dates dispos 
Mais est-ce vraiment une amélioration puisque ta solution marche bien ? 
Ca allègerait la feuille des formules...

Il y a t'il une limitation sur le nombre de menus déroulants qu'Excel peut gérer ? 
Je pense que la seule limitation est celle de la mémoire disponible. 

eric

gbinforme · Answer

bonjour mic13710, Éric,

J'ai eu un peu de mal à comprendre ton tableau et je n'est sans doute pas été le seul. D'après ce que j'ai compris je te fait cette proposition.


http://www.cijoint.fr/cjlink.php?file=cj201109/cijSN48s2Q.xls

- J'ai gardé ta logique de listes, mais je les ai mises en début masquées pour permettre d'agrandir la saisie sans soucis.
- J'ai mis des formules pour gérer les dates et ainsi à partir de la saisie de l'année de départ tous tes dimanches s'affichent.
- Pour agrandir le tableau en bas et/ou à droite, il suffit de sélectionner la colonne ou la ligne pour tirer les formules dont j'ai nommé certaines pour faciliter les modifications.

Je n'ai peut-être pas compris mais il m'a semblé que tu avait beaucoup de saisies qui sont simplifiées ainsi : reste uniquement les listes déroulantes à valider.

mic13710 · Answer

Salut Eric, salut gbinforme,

D'abord un grand merci pour vos réponses. 
Mon tableau manquait sans doute d'explications mais vous avez bien compris de quoi il s'agissait.
Oui Eric, j'aurais pu utiliser les numéros des jours plutôt que l'ordre chrono, mais dans mon tableau, c'est plus parlant ainsi pour l'utilisateur. Et pour la question des 4 ou 5 choix pour 150, la réponse est oui. Il s'agit d'une programmation mensuelle de parcours dominicaux. 
Ce n'est bien entendu qu'un tout petit extrait du tableau final. Et les calculs des dates sont données par formules, celles de mon exemple n'étant que des copies des valeurs. 
Le choix pour le moment est manuel (saisie au clavier) avec validation des données et vérification des doublons. Ça marche bien comme ça, mais la solution par menu déroulant variable me semble plus élégante, car je n'ai plus besoin dans ce cas de vérifier les doublons puisque la liste le fait directement.

Ce qui importe dans le cas présent, c'est la manière de traiter les menus déroulants. Certes, mon tableau fonctionne mais je trouvais que ma méthode était un peu lourde, d'où ma demande. Il semble d'après vos réponses que le mode opératoire soit le bon., à savoir la création de 2 listes.

La proposition de gbinforme est exactement ce que je cherchais et me montre combien j'ai encore de progrès à faire dans la maitrise des formules. Je ne connaissais pas la fonction PETITE.VALEUR, d'où cette succession de SI qui plombaient mes formules.
Autre point qui me plait aussi beaucoup en terme de simplification, c'est de donner un nom à une formule. Je connaissais le truc sans jamais l'avoir utilisé. Là, il trouve effectivement toute son utilité, et je pense que je vais employer le principe dans mon fichier car j'ai une multitude de formules identiques dans de nombreuses cellules. 
Merci gbinforme pour votre réalisation qui va me permettre d'améliorer mes connaissances.

Concernant la solution par vba, c'est peut être à creuser. Est-ce que la validation des données par liste peut se faire par vba simplement en cliquant dans la cellule, c'est à dire sans avoir préalablement créé une validation classique des données pour cette cellule ? 
Ça me permettrait entre autre de ne pas faire apparaître de menu déroulant dans les cellules protégées (à chaque nouveau mois, les colonnes des mois précédents sont protégées et donc le menu est inutile), et de ne pas charger inutilement mon fichier, bien que les menus déroulants ne semblent pas être très gourmands de ce point de vue.
Je crois que l'excellent site de Boisgontier est assez fourni sur ce point. Je vais voir de ce côté, mais si vous avez des idées là dessus, je suis preneur.

pilas31 · Answer

Bonjour mic13710, eriiic et gbinforme, Je me suis essayé à traiter le problème en VBA. Voici le résultat. J'ai utilisé un principe de création de liste de choix « furtive » en calculant la liste de validation à la volée. L'exemple est ici : http://www.cijoint.fr/cjlink.php?file=cj201109/cijRtMqinH.xls Voila le code qui n'est sans doute pas parfait : Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim ListeDim As String Dim PlageRech As Range ' Modifier ici la plage des cases actives si nécéssaire Premligne = 10 DernLigne = 100 PremColonne = 2 DernColonne = 100 ' If Target.Row >= Premligne And Target.Column >= PremColonne Then ListeDim = "" With Range(Cells(Premligne, PremColonne), Cells(DernLigne, DernColonne)).Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween End With If Target.Row = Premligne Then Set PlageRech = Range(Cells(Premligne + 1, Target.Column), Cells(DernLigne, Target.Column)) Else Set PlageRech = Union(Range(Cells(Premligne, Target.Column), Cells(Target.Row - 1, Target.Column)), _ Range(Cells(Target.Row + 1, Target.Column), Cells(DernLigne, Target.Column))) End If With PlageRech If .Find(1, LookIn:=xlValues) Is Nothing Then ListeDim = "1" If .Find(2, LookIn:=xlValues) Is Nothing Then ListeDim = ListeDim & ",2" If .Find(3, LookIn:=xlValues) Is Nothing Then ListeDim = ListeDim & ",3" If .Find(4, LookIn:=xlValues) Is Nothing Then ListeDim = ListeDim & ",4" If .Find(5, LookIn:=xlValues) Is Nothing And _ Cells(Premligne - 2, Target.Column) <> "" Then ListeDim = ListeDim & ",5" End With If ListeDim <> "" Then With Target.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=ListeDim End With Set ViensDe = Target End If End If End Sub A+ Cordialement,

mic13710 · Answer

Salut Pilas,

Et bien là sur le coup, c'est pile poil ce que je voulais en vba : la création des listes sur sélection de la cellule. Bravo et merci pour cette macro qui me sauve des heures de recherche et de programmation. C'est du grand art ! 
Plus besoin de faire les validations pour chaque cellule, ce qui allège d'autant le fichier. De plus, il est possible de saisir les numéros au clavier, chose que je ne pouvais pas faire avec la solution classique.
Juste un petit problème détecté cependant, une fois la liste complétée avec les 4 ou 5 entrées, il est alors possible de saisir n'importe quel chiffre (5 et plus) dans les autres cellules. Y'a t'il une solution ? Sinon, je peux laisser les validations telles qu'elles existent dans mon fichier, en y ajoutant la macro pour la saisie. Ainsi, je peux utiliser les menus déroulants créés par vba, tout en surveillant les doublons et les saisies hors liste.
Me reste plus qu'a adapter cette macro à mon besoin et en y ajoutant une interdiction sur les mois protégés : pas de liste si la cellule est protégée.

pilas31 · Answer

Bonsoir, 

Oui c'est vrai que quand la liste est épuisée je n'avais pas mis de contrainte ce qui est une erreur. Une légère modification du code est possible pour corriger ça : 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

Dim ListeDim As String 
Dim PlageRech As Range 

' Modifier ici la plage des cases actives si nécéssaire 
Premligne = 10 
DernLigne = 100 
PremColonne = 2 
DernColonne = 100 
' 

If Target.Row >= Premligne And Target.Column >= PremColonne Then 
    ListeDim = "," 
    With Range(Cells(Premligne, PremColonne), Cells(DernLigne, DernColonne)).Validation 
        .Delete 
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ 
        :=xlBetween 
    End With 
    If Target.Row = Premligne Then 
        Set PlageRech = Range(Cells(Premligne + 1, Target.Column), Cells(DernLigne, Target.Column)) 
    Else 
        Set PlageRech = Union(Range(Cells(Premligne, Target.Column), Cells(Target.Row - 1, Target.Column)), _ 
                              Range(Cells(Target.Row + 1, Target.Column), Cells(DernLigne, Target.Column))) 
    End If 
    With PlageRech 
        If .Find(1, LookIn:=xlValues) Is Nothing Then ListeDim = "1" 
        If .Find(2, LookIn:=xlValues) Is Nothing Then ListeDim = ListeDim & ",2" 
        If .Find(3, LookIn:=xlValues) Is Nothing Then ListeDim = ListeDim & ",3" 
        If .Find(4, LookIn:=xlValues) Is Nothing Then ListeDim = ListeDim & ",4" 
        If .Find(5, LookIn:=xlValues) Is Nothing And _ 
            Cells(Premligne - 2, Target.Column) <> "" Then ListeDim = ListeDim & ",5" 
    End With 
    With Target.Validation 
        .Delete 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
        xlBetween, Formula1:=ListeDim 
    End With 
    Set ViensDe = Target 
  End If 
End Sub 



Il faut initialiser la liste avec une simple virgule (liste vide) et imposer une validation même quand la liste est vide. 

A+ 

Cordialement,

mic13710 · Answer

Merci Pilas pour cette petite virgule qui change tout.
J'ai finalement opté pour la solution vba que j'ai adaptée à mon besoin.
J'y ai rajouté aussi un contrôle de protection de la cellule pour ne pas avoir de menu dans des cellules protégées,
et j'ai dû aussi déprotéger / reprotéger la feuille sinon la validation n'est pas possible.
J'ai aussi enlevé le Set ViensDe = Target qui n'a pas d'utilité dans notre cas.

Voici mon code modifié que j'ai inséré dans un Private Sub Worksheet_SelectionChange(ByVal target As Range) :

If Not Intersect(target, Range("AA10:FR159")) Is Nothing Then
    If target.Cells.Locked = False Then
        Dim ListeDim As String
        Dim PlageRech As Range
        ListeDim = ","
        Range("AA10:FR159").Validation.Delete
        If LigSel = 10 Then
            Set PlageRech = Range(Cells(LigSel + 1, target.Column), Cells(159, target.Column))
        Else
            Set PlageRech = Union(Range(Cells(10, target.Column), Cells(LigSel - 1, target.Column)), _
                                  Range(Cells(LigSel + 1, target.Column), Cells(159, target.Column)))
        End If
        With PlageRech
            If .Find(1, LookIn:=xlValues) Is Nothing Then ListeDim = "1"
            If .Find(2, LookIn:=xlValues) Is Nothing Then ListeDim = ListeDim & ",2"
            If .Find(3, LookIn:=xlValues) Is Nothing Then ListeDim = ListeDim & ",3"
            If .Find(4, LookIn:=xlValues) Is Nothing Then ListeDim = ListeDim & ",4"
            If .Find(5, LookIn:=xlValues) Is Nothing And _
                Cells(8, target.Column) <> "" Then ListeDim = ListeDim & ",5"
        End With

        ActiveSheet.Unprotect Password:="xxxx"
        With target.Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=ListeDim
            .ErrorTitle = "Erreur de saisie"
            .ErrorMessage = "Votre saisie est invalide ou hors plage." _
             & Chr(10) & "Choisissez de préférence les valeurs données dans le menu déroulant"
            .ShowError = True

        End With
        ActiveSheet.Protect Password:="xxxx", UserInterfaceOnly:=True
    End If
End If

La variable LigSel est définie dans un code précédent et correspond à target.Row

Même si le principe que j'avais adopté au départ fonctionne bien et que la solution de gbinforme répond exactement à ma demande initiale, elle n'offre pas la souplesse et la simplicité de la macro qui permet de créer seulement le menu de la cellule sélectionnée. 

Encore merci à tous les 3 pour votre implication. J'ai encore beaucoup appris avec vos propositions.

Sujet résolu.

Excel Menu déroulant élimination des utilisés

8 réponses

Discussions similaires