Valeurs qui s'affiche automatiquement dans menu déroulant 2 à niveaux
Bonjour à tous
Je suis actuellement en train de réaliser un configurateur et je bloque sur un point concernant mes menus déroulants à 2 niveaux (Niveau 1 = Fournisseur / Niveau 2 = Référence).
Actuellement : Lorsque sur mon menu déroulant de niveau 1 je sélectionne un fournisseur, mon menu déroulant de niveau 2 des références ne bouge pas.
Ce que je souhaiterais : Je souhaiterais que lorsque sur mon menu déroulant de niveau 1 je sélectionne un fournisseur, je souhaiterais que mon menu déroulant de niveau 2 affiche automatiquement la première référence du fournisseur choisis.
Exemple :
- Si je choisis «Fournisseur B » en D6, je souhaiterais que « Fournisseur B – Référence 1 » s’affiche automatiquement en D7.
- Si je choisis « Aucune sélection » en D9, je souhaiterais que « Aucune sélection » s’affiche automatiquement en D10.
- Si je choisis « Fournisseur A » en E6, je souhaiterais que « Fournisseur A – Référence 1 » s’affiche automatiquement en E7.
J'ai trouvé des pistes mais encore aucune solutions dans les différents tutos sur la création de menu déroulant donc j’ai cherché en VBA sur différents tutos et forum trouvés sur internet, mais après y avoir passé ma soirée je ne parviens toujours pas à trouver la bonne formule.
J’ai essayé un tas de codes différents, voici ci-dessous le dernier en date que j’ai fais ce matin, il fonctionne sur la première cellule mais me fais planter mon excel juste après.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Fournisseur_A As String, Fournisseur_B As String, Fournisseur_C As String, Fournisseur_D As String, Reference_A1 As String, Reference_B1 As String, Reference_C1 As String, Reference_D1 As String, Plage As Range, Cell As Range
Fournisseur_A = "Fournisseur A"
Fournisseur_B = "Fournisseur B"
Fournisseur_C = "Fournisseur C"
Fournisseur_D = "Fournisseur D"
Reference_A1 = "Fournisseur A - Référence 1"
Reference_B1 = "Fournisseur B - Référence 1"
Reference_C1 = "Fournisseur C - Référence 1"
Reference_D1 = "Fournisseur D - Référence 1"
Set Plage = Range("D6:K8,D9:K9,D12:K12,D15:K15")
For Each Cell In Plage
If Cell.Value = Fournisseur_A Then Cell.Offset(1, 0).Value = Reference_A1
If Cell.Value = Fournisseur_B Then Cell.Offset(1, 0).Value = Reference_B1
If Cell.Value = Fournisseur_C Then Cell.Offset(1, 0).Value = Reference_C1
If Cell.Value = Fournisseur_D Then Cell.Offset(1, 0).Value = Reference_D1
Next
End Sub
(oui je sais ca pique les yeux
Je pense que le « For Each cell in plage » est une bonne piste mais je n’arrive pas à aller plus loin (le cell.offset(1,0).value qui dois changer la valeur sur la cellule du dessous me fait planter mon excel ) et je souhaiterais savoir si vous auriez la patience de m’aider à trouver la solution qui me bloque dans mon projet s’il vous plait ?
Si vous arriveriez à me trouver une solution, je pourrais alors comprendre le code proposé et aller plus loin dans mon projet.
Vous trouverez en pièce jointe un extrait de mon configurateur (sans infos confidentielles) si cela est nécessaire.
Je vous souhaite une bonne journée et je vous remercie d’avoir prêté attention à mon post.
Bonjour,
Plutôt que de débuguer ton programme je l'ai repris de zéro
Tu trouveras donc un programme événementiel dans ta feuille Prix :
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D6:K6,D9:K9,D12:K12,D15:K15")) Is Nothing Then
Call ChargeRef(Target)
End If
End Sub
Et un code dans un module qui permet d'utiliser plusieurs feuilles :
Sub ChargeRef(Cellule As Range)
Application.EnableEvents = False
Dim CelDeb As Range
With Sheets("Menu déroulant Produits Ext")
Set CelDeb = .Range("C2")
On Error GoTo Erreur
Cellule.Offset(1, 0) = .Range(CelDeb, CelDeb.End(xlToRight)).Find(Cellule).Offset(1, 0)
End With
Application.EnableEvents = True
Exit Sub
Erreur:
If Cellule <> "" And Cellule <> "Aucune sélection" Then
MsgBox ("Le fournisseur choisi n'a pas été trouvé.")
End If
Cellule.Offset(1, 0) = "Aucune sélection"
Application.EnableEvents = True
End Sub
Et voici le fichier qui va avec :
Si jamais tu as besoin d'explications...
Bonjour Ausecour,
Je te remercie beaucoup pour le temps que tu m'as consacré, sur le fichier que tu m'as envoyé c'est exactement le résultat que je souhaitais !!!
Cependant j'ai du mal à comprendre comment fonctionne ton code afin de l'adapter à une autre version de mon configurateur contenant plus de lignes.
Notament cette partie :
Dim CelDeb As Range
With Sheets("Menu déroulant Produits Ext")
Set CelDeb = .Range("C2")
On Error GoTo Erreur
Cellule.Offset(1, 0) = .Range(CelDeb, CelDeb.End(xlToRight)).Find(Cellule).Offset(1, 0)
End With
Application.EnableEvents = True
Exit Sub
Ci-joint la Version 2 de mon configurateur sur lequel j'ai rajouté plusieurs lignes (certaines sont cachés pour plus de clarté) mais je n'arrive pas à adapter ton code sur cette version 2
J'ai bien mis à jour les plages dans
If Not Intersect(Target, Range("D100:K100,D109:K109,D118:K118,D127:K127,D136:K136")) Is Nothing Then
mais je n'arrive malgrès cela pas à avoir le résultat que tu arrive à avoir sur le fichier excel que tu m'as renvoyé.
Pourrais-tu m'aider à voir où cela coince afin que le fichier V2 qui est présent en pièce jointe puisse également bénéficier de ce que tu as fait sur la V1 s'il te plait ?
Oups !!!
Je retire ce que j'ai dis sur mon poste plus haut, j'ai réussi à parfaitement adapter ton code sur mon fichier en V2, j'avais vérifié précipitamment.
Cependant je souhaiterais savoir quelle est la mécanique de cette partie du code que j'ai du mal à bien comprendre s'il te plait ?
Dim CelDeb As Range
With Sheets("Menu déroulant Produits Ext")
Set CelDeb = .Range("C2")
On Error GoTo Erreur
Cellule.Offset(1, 0) = .Range(CelDeb, CelDeb.End(xlToRight)).Find(Cellule).Offset(1, 0)
End With
Milles merci encore pour avoir pu résoudre mon problème en tout cas !
Bonjour,
de rien tout d'abord
Alors petite explication et amélioration du programme que je t'avais fournis.
Tout d'abord l'evènement qui appelle ou non le programme dans le module1
Private Sub Worksheet_Change(ByVal Target As Range)
La macro se lance quand il y a un changement dans la feuille
If Not Intersect(Target, Range("D:K")) Is Nothing And Cells(Target.Row, 3) = "Fournisseur" Then
Intersect est une fonction qui renvoie la cellule qui est l'intersection entre des plages de cellules que tu lui fournis, si tu prends la cellule A1 et que tu vérifies via intersect que la cellule croise bien la colonne A, la fonction renverra quelque chose, et nous on vérifie que la fonction ne renvoie pas quelque chose de vide via not is nothing.
J'ai rajouté And Cells(Target.row,3) = "Fournisseur", le And signifie ET, en clair tu ne vas dans les instructions de ta condition que si les deux tests renvoient vrai, le cells(Target.row,3) permet de récupérer la cellule en colonne C de la ligne où tu viens de faire une modification, je vais voir si c'est une ligne contenant un Fournisseur ou non, c'est plus simple que de mettre toutes les lignes où tu peux en avoir
Call ChargeRef(Target)
On appelle un programme qui est extérieur à la feuille, le soucis avec les programmes qui sont mis dans une feuille, c'est qu'ils ne peuvent pas accéder à d'autres feuilles, ils travaillent en local, les programmes écrits dans des Modules n'ont pas ces contraintes, je lui passe en paramettre Target, qui est la cellule où le changement vient d'avoir lieu (je ne gère pas le cas où tu as une sélection de plusieurs cellules, ça peut être source de soucis à l'avenir selon l'utilisation que tu en fait, il faudra me dire si tu veux que je gère ça ou non.)
End If
End Sub
Ensuite le programme dans le module:
Sub ChargeRef(Cellule As Range)
Application.EnableEvents = False
On désactive les évènements car on ne veut pas en redéclencher pendant qu'on excécute le programme
Dim CelDeb As Range
With Sheets("Menu déroulant Produits Ext")
On utilise l'instruction With pour ne pas avoir à réécrire Sheets... partout
Set CelDeb = .Range("C2")
Ici tu déclares un objet qui va contenir la cellule C2 de la feuille Menu déroulant, je pourrai rappeler cet objet sans avoir sans cesse à aller le chercher dans la feuille
On Error GoTo Erreur
ça c'est pour la gestion d'erreur, on va à Erreur: si on rencontre un problème
Cellule.Offset(1, 0) = .Range(CelDeb, CelDeb.End(xlToRight)).Find(Cellule).Offset(1, 0)
Ici on va utiliser la fonction find pour trouver quel fournisseur on a mis, et où il se trouve dans la plage de cellule qu'on donne, on doit mettre une gestion d'erreur avec une telle fonction, parce qu'elle peut faire tout planter si elle ne trouve pas le fournisseur en question
End With
Application.EnableEvents = True
Exit Sub
Erreur:
Là on gère les erreurs
If Cellule <> "" And Cellule <> "Aucune sélection" Then
Ici je vérifie que la cellule contienne quelque chose qui soit autre que Aucune sélection
MsgBox ("Le fournisseur choisi n'a pas été trouvé.")
J'affiche un message si c'est le cas
End If
Cellule.Offset(1, 0) = "Aucune sélection"
Et je réinitialise la valeur de la cellule référence
Application.EnableEvents = True
End Sub
Avec ce code ça à l'air de marcher, je te laisse voir ça
Voici le fichier avec le code modifié:
EDIT: Petit bonus
donc pour expliquer juste ça :
Dim CelDeb As Range
With Sheets("Menu déroulant Produits Ext")
Set CelDeb = .Range("C2")
On Error GoTo Erreur
Cellule.Offset(1, 0) = .Range(CelDeb, CelDeb.End(xlToRight)).Find(Cellule).Offset(1, 0)
End With
Voici un détail :
Dim CelDeb As Range
With Sheets("Menu déroulant Produits Ext") 'Si on fait .range par exemple, c'est comme si on écrivait _
Sheets("Menu déroulant Produits Ext") .range , et ce jusqu'au end with
Set CelDeb = .Range("C2") 'ici je déclare la cellule C2 qui est le début de ta liste de fournisseur dans _
une variable, comme ça je n'ai pas à me trimbaler le .Range("c2") partout dans mon code
On Error GoTo Erreur
Cellule.Offset(1, 0) = .Range(CelDeb, CelDeb.End(xlToRight)).Find(Cellule).Offset(1, 0)
'Cellule.offset(1,0) = Target.offset(1,0) = La cellule qui est 1 ligne en dessous et 0 colonne à droite _
de la cellule modifiée
'.range = la plage de cellule dans la feuille du With plus haut
'CelDeb.end(xltoright) = On part de la cellule c2 et on va à droite jusqu'à trouver une cellule _
vide/remplie (dépend de si c2 est vide ou non, si c2 est vide, on s'arête à la première cellule remplie _
en partant à droite, ça équivaut au raccourcit Ctrl + flèche de droite
'Cette ligne peut se lire : Target +1 ligne +0 colonne = _
dans la feuille menu déroulant, entre la cellule c2 et la dernière cellule à droite de la même ligne, _
on cherche la cellule qui contient le nom du fournisseur, et on prend la cellule du dessous _
pour la référence
End With
Super !
Je te remercie beaucoup pour tes explications claires qui m'aident vraiment à comprendre ta solution.
C'est impeccable, encore un grand merci pour ta précieuse aide et pour le fichier Excel
Bonne soirée à tous !
Bonjour à tous et bonjour Ausecour si tu es ici
Le fichier de Ausecour marche parfaitement, cependant je souhaiterais y rajouter une petite fonctionnalité mais mes différents essais font planter mon fichier Excel.
Je souhaiterais que les menus déroulants qui sont dans la colonne B puissent commander les valeurs des cellules des lignes fournisseur associés.
Par exemple pour la séléction du "Produit 1", je souhaiterais que les valeurs "Fournisseur" situés entre D100 et K100 puisse dépendre de la valeur du menu déroulant situé en B100.
C'est à dire que si dans le menu déroulant de B100, je choisis "Fournisseur A", alors les valeurs des cellules situés entre D100 et K100 devra être "Fournisseur A".
Et évidement je souhaiterais que cela n'empêche pas le bon fonctionnement de la super macro de Ausecour
J'ai essayé plusieurs méthodes mais à chaque fois, cela fait planter mon fichier Excel, ou bien cela ne fait pas fonctionner la macro Sub ChargeRef que m'avais gentillement fait Ausecour.
Par exemple lorsque je rajoute dans Private Sub Worksheet_Change(ByVal Target As Range) :
Range("D100:K100").Value = Range("B100").Value
Range("D109:K109").Value = Range("B109").Value
Range("D118:K118").Value = Range("B118").Value
Range("D127:K127").Value = Range("B127").Value
Range("D136:K136").Value = Range("B136").Value
Et que je choisis "Fournisseur A" en B100, cela fonctionne mais juste après j'ai un message d'erreur.
Or je ne comprends pas pourquoi cela bloque à ce niveau, les valeurs que je force dans les plages (D100:K100), (D109, K109), (D118:K118), (D127:K125) et (D136, K136) sont identiques à celles que je sélectionnerais dans les menus déroulants situés dans ces plages répsectives.
Cela me frustre car je pensais qu'avoir cette fonctionnalité serait du gâteau mais tous mes essais se soldent par un echec.
Je souhaiterais donc savoir si vous auriez encore la patience de me donner un coup de main afin que je puisse finaliser cela s'il vous plait ?
Bonne journée à tous.
Bonjour,
Il fallait reprendre plusieurs choses, déjà j'ai repris le worksheet change pour ajouter la colonne B, et mis en place quelque chose pour gérer les cellules fusionnées (comme tu fusionnes).
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D:K")) Is Nothing And Cells(Target.Row, 3) = "Fournisseur" Then
Call ChargeRef(Target)
Else
For Each cel In Target.Cells
If Not Intersect(cel, Range("b:b")) Is Nothing And cel <> "" Then
Range("D" & cel.Row & ":K" & cel.Row).Value = cel
End If
Next cel
End If
End Sub
J'ai également changé un détail, dans le chargeref je prends le cas où tu peux avoir plusieurs cellules dans ton worksheet change, comme on change la valeur de d à k d'un coup :
For Each cel In Cellule.Cells
Cellule.Offset(1, 0) = .Range(CelDeb, CelDeb.End(xlToRight)).Find(Cellule).Offset(1, 0)
Next cel
Voici le résultat final :
Merci infiniment, c'est impeccable et c'est parfaitement ce que je recherchais à faire
Bonjour à tous,
Je viens de parcourir le dernier fichier partagé et je suis aux anges ! c'est exactement le même principe du menu déroulant sur 2 niveaux qu'il me faut pour compléter mon fichier planning !
En effet, je voudrais l'intégrer dans mon système pour gérer les métiers et les salariés dans la feuille personnel de mon classeur.
Lorsque l'on sélectionnera un métiers dans le menu déroulant de la feuille planning, on n'affichera dans le menu déroulant à côté que les salariés exerçant ce métier.
Auriez-vous la gentillesse de m'aider à le mettre en place dans mon fichier ?
Ci-joint mon planning.
Merci par avance les ami(e)s !