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 !