XL 2016 Listes déroulantes en cascade

[Jean_Valjean]

Bonjour,

Débutant en vba je bute sur un problème de liste déroulante dépendante. Je cherche à réaliser un chiffrage à partir d'un catalogue de matières (plusieurs milliers de réfs.) : le prix est composé d'une sélection de référence à laquelle correspond un prix pour une épaisseur donnée et une plus-value de ce prix pour finition donnée (parfois cette plus-value est à "0"). Il existe plusieurs épaisseurs et plusieurs finitions mais chaque référence existe seulement dans certaines épaisseurs et finitions.

Mon problème est le suivant : je sélectionne la matière dans une liste déroulante et j'ai besoin liste dépendante qui ne propose que les épaisseurs disponibles pour cette matière et enfin d'une dernière liste dépendante qui ne propose que les finitions disponibles pour cette même matière. Mon userform fonctionne mais je sors des listes qui contiennent toutes les épaisseurs / finitions et ce n'est pas très fonctionnel à l'usage. Je joins le fichier pour exemple.

Je ne sais pas si c'est possible, mais si quelqu'un a une idée de la faisabilité de la chose, je suis preneur.

Merci.

 

[Dranreb]

Bonjour
Pourriez vous établir un catalogue à 4 colonnes de toutes des combinaisons valides.
Il y a alors un jeu de modules de service qui se charge de tout. Il suffit d'indiquer dans la Sub UserForm_Initialize la base et chaque ComboBox concernée accompagnée de la spécification de la colonne qui lui correspond. Celle ci peut même être son titre si la base ait été mise sous forme de tableau, ayant ainsi crée un ListObject le couvrant.

 

[Dranreb]

Information: j'arrive à 9430 articles en constituant automatiquement cette base à partir de la feuille "Fournisseurs"
Remarque: S'il venait à y avoir plusieurs fournisseurs, il faudrait peut être aussi pouvoir le choisir dans une ComboBox, au cas où plusieurs proposaient le même article, ou pour réduire les choix possibles à l'un d'eux en particulier.

 

[Jean_Valjean]

Merci de votre réponse rapide. Si je comprends bien les 4 colonnes correspondent aux 4 combobox du userform ? Je vais chercher comment créer automatiquement ce catalogue.

 

[Dranreb]

C'est déjà fait chez moi :

Option Explicit
Private Sub CBnReconst_Click()
   Dim TDon(), TBase(), LD&, CÉp%, CFi%, LB&, LOt As ListObject
   TDon = Intersect(WshFou.[A3:AN1000000], WshFou.UsedRange).Value
   ReDim TBase(1 To UBound(TDon, 1) * 50, 1 To 6)
   For LD = 2 To UBound(TDon)
      For CÉp = 3 To 20: For CFi = 21 To 40
         If Not (IsEmpty(TDon(LD, CÉp)) Or IsEmpty(TDon(LD, CFi))) Then
         LB = LB + 1
         TBase(LB, 1) = TDon(LD, 1)
         TBase(LB, 2) = TDon(LD, 2)
         TBase(LB, 3) = TDon(1, CÉp)
         TBase(LB, 4) = TDon(1, CFi)
         TBase(LB, 5) = CCur(TDon(LD, CÉp))
         TBase(LB, 6) = CCur(TDon(LD, CFi))
         End If: Next CFi, CÉp, LD
   Set LOt = Me.ListObjects(1)
   TableauRetaillé(LOt, LMax:=LB) = TBase
   LOt.Range.Columns.AutoFit
   LOt.Sort.Apply
   End Sub

Property Let TableauRetaillé(ByVal LOt As ListObject, Optional ByVal LMax As Long, TVals())
   Dim Trop As Long, CMax As Long, TFml(), F As Long
   If LMax = 0 Then LMax = UBound(TVals, 1)
   Trop = LOt.ListRows.Count - LMax
   If Trop > 0 Then
      LOt.ListRows(LMax + 1).Range.Resize(Trop).Delete xlShiftUp
   ElseIf Trop < 0 And LMax + Trop > 1 Then
      LOt.ListRows(LMax + Trop).Range.Resize(-Trop).Insert xlShiftDown, xlFormatFromLeftOrAbove
      End If
   If LMax = 0 Then Exit Property
   ReDim TFml(1 To LOt.ListColumns.Count)
   For F = 1 To UBound(TFml)
      With LOt.HeaderRowRange(2, F)
         If .HasFormula Then TFml(F) = .Formula2R1C1 Else TFml(F) = Null
         End With: Next F
   LOt.HeaderRowRange.Offset(1).Resize(LMax).Value = TVals
   For F = 1 To UBound(TFml)
      If Not IsNull(TFml(F)) Then LOt.ListColumns(F).DataBodyRange.Formula2R1C1 = TFml(F)
      Next F
   End Property

Je joindrai le classeur si vous êtes d'accord pour utiliser un ComboBoxLiées dans l'UFmChoixMat.

 

[Jean_Valjean]

Ah oui bien volontiers car j'ai une erreur d'exécution objet non défini "WshFou"

 

[Dranreb]

Voila la chose.

Pour la référence, tapez le début car il y en a trop (plus de 75) pour que la liste soit proposée d'emblée.

 

[AtTheOne]

Bonjour à tous, Bonjour @Jean_Valjean, Bonjour @Dranreb

En conservant le tableau originel des fournisseurs, je suis passé par des tableaux structurés et un dictionnaire (Scripting.Dictionary) pour alimenter les comboboxes.
Voir le fichier joint .
Amicalement
Alain

Modif
PS (Mon post a croisé le dernier de Dranreb)

 

[Jean_Valjean]

Merci infiniment Dranreb, ça fonctionne parfaitement, je vais prendre du temps pour me plonger dans votre code car il va m'aider à progresser.

 

[Jean_Valjean]

Bonjour à tous, Bonjour @Jean_Valjean, Bonjour @Dranreb

En conservant le tableau originel des fournisseurs, je suis passé par des tableaux structurés et un dictionnaire (Scripting.Dictionary) pour alimenter les comboboxes.
Voir le fichier joint .
Amicalement
Alain

Merci Alain c'est très efficace aussi, moi qui pensais le problème insoluble... ;-) merci pour votre rapidité

 

[Jean_Valjean]

Bonjour à tous, Bonjour @Jean_Valjean, Bonjour @Dranreb

En conservant le tableau originel des fournisseurs, je suis passé par des tableaux structurés et un dictionnaire (Scripting.Dictionary) pour alimenter les comboboxes.
Voir le fichier joint .
Amicalement
Alain

Modif
PS (Mon post a croisé le dernier de Dranreb)

Bonjour Alain,
Je suis encore coincé sur un détail, la macro plante lorsque fais une saisie-auto de référence sur une lettre qui n'existe pas : la saisie AMA est ok car la référence AMARONE existe dans le tableau, mais si je saisis par inadvertance AMARI, ça coince, idem pour la saisie Y car aucune référence ne commence par cette lettre).
J'ai essayé de modifier les paramètres MatchEntry ou MatchRequired, mais je perd la saisie semi-auto.
Auriez-vous une idée que je pourrais tester ?
Amicalement.

 

[Dranreb]

Avez vous remarqué qu'avec mon système, grâce à la méthode CLs.CouleurSympa à l'initialisation, lorsque vous tapez quelque chose qui n'existe pas la ComboBox se met en rose saumon ?
Il serait possible d'ajouter une procédure permettant de trouver aussi un groupe de lettres pas forcément au début, si ça vous intéresse.

 

[Jean_Valjean]

Avez vous remarqué qu'avec mon système, grâce à la méthode CLs.CouleurSympa à l'initialisation, lorsque vous tapez quelque chose qui n'existe pas la ComboBox se met en rose saumon ?
Il serait possible d'ajouter une procédure permettant de trouver aussi un groupe de lettres pas forcément au début, si ça vous intéresse.

Merci de votre réponde Danreb, je n'avais pas vu votre astuce, je vais regarder comment vous avez procédé.

 

[Dranreb]

C'est interne au module de service et de classe ComboBoxLiées. Inutile pour l'utiliser, d'étudier comment c'est fait, l'effet résulte bien évidemment d'un changement de la propriété BackColor de la ComboBox effectué dans sa méthode CBM_Change. La seule chose à savoir c'est que c'est activé en invoquant la méthode CouleurSympa de l'objet ComboBoxLiées.

 

[AtTheOne]

Bonne nuit à tous, bonne nuit @Jean_Valjean
Je rentre un peu tard, je vois que les choses ont un peu changé...

AMARI, ça coince, idem pour la saisie Y car aucune référence ne commence par cette lettre)

Ce point est réglé, il fallait s'inquiéter du ListIndex, j'étais allé un peu vite en besogne.

Il serait possible d'ajouter une procédure permettant de trouver aussi un groupe de lettres pas forcément au début, si ça vous intéresse.

Ce point est également réglé avec une expression rationnelle. dans

lorsque vous tapez quelque chose qui n'existe pas la ComboBox se met en rose saumon

J'ai également ajouté cette fonctionnalité.

Tu as maintenant le choix de la méthode à utiliser, celle de Dranreb est très aboutie, la mienne est plus une application de démo pour les Dictionary et les RegExp. Un peu de variété ne peu pas nuire à la connaissance...

A toi de voir

Amicalement
Alain