Excel Menu déroulant élimination des utilisés
Résolu
mic13710
Messages postés
1165
Statut
Membre
-
mic13710 Messages postés 1165 Statut Membre -
mic13710 Messages postés 1165 Statut Membre -
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.
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.
A voir également:
- Excel Menu déroulant élimination des utilisés
- Menu déroulant excel - Guide
- Excel menu déroulant en cascade - Guide
- Menu déroulant google sheet - Accueil - Guide bureautique
- Word et excel gratuit - Guide
- Supprimer menu déroulant excel - Forum Word
8 réponses
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.
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.
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 :
A+
Cordialement,
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,
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 :
Il faut initialiser la liste avec une simple virgule (liste vide) et imposer une validation même quand la liste est vide.
A+
Cordialement,
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,
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
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
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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
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
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.
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.
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.
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.
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) :
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.
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.