Liste déroulante et report de valeur dans la même cellule
Bonjour a tous,
Je travaille sur un formulaire pour trouver les données technique d'un produit concurent en le selectionnant via 3 critères en C8, C10 et C12.
Cela va chercher dans une BDD et affiche les informations disponibles en dessous (C14:C28). Cela je l'ai deja développé et c'est fonctionel :-).
Maintenant, je voudrais mettre en place en colonne F qqch de similaire pour faire apparait les données techniques d'un equivalent dans ma gamme de produit. J'ai dans ce cas deux cas de figure:
- Je connais deja l'equivalent : Dans ma base de donnée, j'ai une colonne equivalent et pour certain des produits concurents, j'ai un equivalent direct. Dans ce cas, la manip pourrait etre relativement simple. En reportant en F12 le nom du produit equivalent identifié, je pourrais faire afficher en dessous ses données techniques.
- Je ne connais pas l'equivalent ou je n'ai pas d'equivalent dans ma gamme. Dans ce cas, il va falloir recherche "a taton" un equivalent. Pour cela, on pourrait envisager deux listes déroulantes (vcalidation de données) en F10 et F12 pour selectionner le type de produit et ensuite selectionner les reference commerciales pour afficher en dessous leur specification et comparer avec le produit concurent.
Je saurais faire les deux approche séparement mais je ne sais pas si elle pourrait etre combiner.
Est-il possible a la fois de :
- Définir une listes déroulantes en F12 qui a la selection, via des macro, permettrait d'afficher les données correspondantes en dessous
- Reporter en F12 la référence du produit dans le cas ou un equivalent serait deja identifié, ce qui enclencherais ensuite la macro pour afficher les données correspondantes en dessous
Je vous met ci-dessous un visuel mais je peux vous envoyer un eversion anonymiser de mon fichier excel si vous le souhaitez.
En vous remerciant par avance
Bastien
Bonjour,
oui, je pense qu'une version anonymisée est nécessaire (d'autant que le visuel n'est pas passé !)
Hello
Le voici
Petite précision, a l'heure actuellen la cellule F12 se remplie automatiquement selon la valeur en C29 qui correspond au produit de ma gamme si j'avais je connais l'equivalent avec le produit du concurent.
L'idée serait d'avoir par defaut une liste déroulante avec l'ensemble des mes produits et si la cellule C29 est remplie (ce qui signifie que je connais l'equient du produit concurent dans ma gamme), alors la liste déroulante se "selectionne d'elle même" à la même valeur que C29.
En d'autre terme, idéalement, la valeur en C29 s'incrit dans la cellule F12 mais la liste deroulante de mes porduit reste toujours accessible.
Une question, quand C29 se met-elle à jour ? en d'autres termes,
- est-ce que lors de l'affichage de l'onglet on sait si C29 est vierge ou pas ?
- sinon, quel événement déclenche la mise à jour de C29 ?
.
17 pages de code ! 678 lignes .... wahou
Hello
Merci pour ta réponse.
En y a beaucoup de choses qui vont disparaître in fine dans le code.
La case C29 se remplit via la macro “copie_conc” qui est appeler via la macro “extraire” quand la condition K6 est remplie (module 12)
Une proposition sur un code déjà bien trop long ...
Sub copie_conc()
Dim colonnec As Integer
Dim lineh As Integer
For colonnec = 9 To 25
ligneh = 14
While Sheets("Home").Cells(ligneh, 2).Value <> ""
If Sheets("Home").Cells(ligneh, 2).Value = Sheets("construction").Cells(9, colonnec).Value Then
Sheets("Home").Cells(ligneh, 3).Value = Sheets("construction").Cells(10, colonnec).Value
End If
ligneh = ligneh + 1
Wend
Next colonnec
If Sheets("Home").Range("C29").Value = "" Then
Sheets("Home").Range("F12").Validation.Delete
Sheets("Home").Range("F12").Validation.Add xlValidateList, _
Formula1:=Join(Application.Transpose(Sheets("Database").ListObjects(1).ListColumns("Reference").DataBodyRange.Value), ",")
Else
Sheets("Home").Range("F12").Value = Sheets("Home").Range("C29").Value
End If
End Subje te laisse renseigner les cases par des RechercheV
Merci :-)
Je vais tester ça
Il faut peut-être aussi ajouter l'effacement de la validation quand la référence est connue, à toi de voir
If Sheets("Home").Range("C29").Value = "" Then
Sheets("Home").Range("F12").Validation.Delete
Sheets("Home").Range("F12").Validation.Add xlValidateList, _
Formula1:=Join(Application.Transpose(Sheets("Database").ListObjects(1).ListColumns("Reference").DataBodyRange.Value), ",")
Else
Sheets("Home").Range("F12").Validation.Delete
Sheets("Home").Range("F12").Value = Sheets("Home").Range("C29").Value
End Ifau-delà de cela, tu maîtrises bien VBA, pour autant mon principe est souvent de ne pas dépasser une page A4 ! on en est loin ...
Hello
C'est tout recent pour moi le VBA (mars). Merci pour le compliment.
Pour le moment, y a beaucoup de code qui sert a rien car au départ, ce n'était pas moi qui était en charge de ça et je vais épurer a la fin.
Essaie de ne pas dépasser 1 page A4 (il y a du boulot)
Je redemanderais sûrement de l’aide à ce moment pour optimiser le code.
Salut Bastien,
Salut Steelson,
comme je comprends l'affaire, la feuille 'Construction' ne sert à rien d'autre qu'alimenter les listes de validation...
J'ai donc pris la liberté de la contourner. Seules les listes de validation de la colonne [C:C] sont ici codées : à chaque nuit suffit sa peine !
Je te laisse découvrir le fonctionnement : bon dimanche !
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim iRow%, iRow1%, iRow2%, iIdx%, sCol$, sItem$
'
Application.EnableEvents = False
Application.ScreenUpdating = False
'
If Not Intersect(Target, Range("C8:C12")) Is Nothing Then
If Target.Row Mod 2 = 0 Then
Range("C" & Target.Row & ":D30").Value = ""
iIdx = CInt(Range("A" & Target.Row).Value)
Target.Validation.Delete
With Worksheets("Database")
sCol = Choose(iIdx, "B", "C", "D")
iRow1 = Choose(iIdx, 2, [D8], [D10])
iRow2 = Choose(iIdx, .Range("B" & Rows.Count).End(xlUp).Row, [D9], [D11])
If iIdx = 1 Then _
.Range("A1:Q" & .Range("A" & Rows.Count).End(xlUp).Row).Sort _
key1:=.[B2], order1:=xlAscending, _
key2:=.[C2], order2:=xlAscending, _
key3:=.[D2], order3:=xlAscending, _
Orientation:=xlTopToBottom, Header:=xlYes
For iRow = iRow1 To iRow2
sItem = sItem & IIf(sItem = "", _
"Sélectionnez " & Choose(iIdx, "une compagnie", " un type de gomme", "une référence"), "") & _
"," & .Range(sCol & iRow).Value
iRow = .Range(sCol & iRow1 & ":" & sCol & iRow2).Find(what:=.Range(sCol & iRow).Value, lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlPrevious).Row
Next
End With
Target.Validation.Add Type:=xlValidateList, Formula1:=sItem
End If
End If
'
Application.ScreenUpdating = True
Application.EnableEvents = True
'
End Sub
A+
Salut Bastien,
Salut Steelson,
quelques heures plus tard... Á quoi on passe son dimanche, je te jure...
Quelques lignes suffisent pour alimenter les listes de validation en [C:C] ou [I:I] créées à la demande pour autant que l'étape précédente soit complétée (Company ouType).
Ne connaissant pas le fonctionnement exact de ta recherche, j'ai postulé que toutes les recherches se faisaient uniquement dans 'Database' : on changera si nécessaire !
J'y ai "créé" quelques références "Nexira", histoire de tester la recherche des équivalents.
If Not Intersect(Target, Range("C8:C12")) Is Nothing Or Not Intersect(Target, Range("I8:I12")) Is Nothing Then
If Target.Row Mod 2 = 0 And Target.Offset(-2, 0) <> "" Then
iCol = Target.Column
Call Couleurs(Target.Row, iCol - 1)
iIdx = CInt(Range("A" & Target.Row).Value)
Target.Validation.Delete
With Worksheets("Database")
sCol = Choose(iIdx, "B", "C", "D")
iRow1 = Choose(iIdx, 2, Range("A8").Offset(0, iCol).Value, Range("A10").Offset(0, iCol).Value)
iRow2 = Choose(iIdx, .Range("B" & Rows.Count).End(xlUp).Row, Range("A9").Offset(0, iCol).Value, Range("A11").Offset(0, iCol).Value)
For iRow = iRow1 To iRow2
If .Range(sCol & iRow).Value <> "Nexira" Then
sItem = sItem & IIf(sItem = "", _
"Sélectionnez " & Choose(iIdx, "une compagnie", " un type de gomme", "une référence"), "") & _
"," & .Range(sCol & iRow).Value
iRow = .Range(sCol & iRow1 & ":" & sCol & iRow2).Find(what:=.Range(sCol & iRow).Value, lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlPrevious).Row
End If
Next
End With
Target.Validation.Add Type:=xlValidateList, Formula1:=sItem
End If
End IfEn 'HOME'
- l'activation de cette feuille entraîne systématiquement un tri spécifique de 'Database' Key1, 2, 3 - [B:C:D] pour faciliter les recherches ;
- l'affichage de la feuille se cale entre [A1:J1] ;
- Les blocs "Company, Type, Reference" en [B:B] et [H:H] se colorent pour signaler le côté de la recherche en cours ;
- Sitôt la "Ref" encodée, la macro recherche l'éventuel équivalent Nexira et affiche le résultat en [F:F] ;
- [E12:F12] se colore de vert ou rouge selon que la référence de l'équivalent Nexira a été trouvée ou non dans les produits Nexira en 'Database' ;
- un rappel de même couleur s'affiche en [C16] ou [I16], mais reste en noir en cas d'absence d'équivalence pour la ref concurrente encodée ;
- une étoile rouge désigne le bloc correspondant à la recherche de l'équivalent Nexira ;
- des valeurs sont cachées en [A-D-G-J] : autant ne pas y chipoter !
- donc, deux recherches possibles en [C:C] et [I:I]. Tu peux alors "passer" de l'analyse d'un équivalent Nexira à l'autre en cliquant sur [C16] ou [I16].
En 'Database'
- tu as deux méthodes de tri :
* un clic sur une en-tête trie le tableau sur base de cette colonne ;
* une sélection de plusieurs colonnes sur les en-têtes entraîne un tri avec 2 Key : Key1 = la 1ère colonne de la sélection - Key2 = la dernière colonne de la sélection.
Ai-je loupé quelque chose ? Sans doute... Attendons de voir les premiers tests et tes indications.
A+
Hello
Merci beaucoup d'avoir passé une partie de ton dimanche pour m'aider.
Je regarde ça a été reposer pour bien comprendre ton code proposé et l'adapté si besoin.
En fait la feuille construction, sert a :
1) extraire des données de la base de données pour remplir les listes déroulantes en C8, C10 et C12. La première à sélectionner c'est C8 qui définit le producteur. Selon cette sélection, les types de produit et les références correspondant a ce producteur sont définis sur la feuille construction et ensuite chargé dans C10 et C12. on peut alors sélectionner soit le type de produit pour affiner la liste des références correspondant au producteur et du coup au type de produit ou soit directement sélectionné une référence.
2) extraire les caractéristiques des ingrédients au fur et à mesure que l'on affine la recherche. Tel que c'est actuellement, l'extraction se fait a chaque étape, mais je pense qu'on pourrait simplifier avec une extraction que si une référence est sélectionnée, car au final l'extraction ne sert qu'a remplir le tableau en C14:C29. les extractions préalables sont accessoire et je les avais codé initialement pour m'assurer que le processus fonctionnait bien. ça fait partie des améliorations que je suis en train de faire
Par contre, je rencontre un gros problème avec les listes déroulantes en activeX. Ça reste un mystère pour moi à l'heure actuelle, mais elle se redimensionne d'elle-même quand on les utilise ce qui est assez pénible. Du coup, j'ai tendance de plus en plus à m'orienter vers des listes déroulantes via une validation de donnée qui, sauf erreur de ma part, nécessite d'avoir une extraction "physqiue" de valeur pour être chargées.
Encore merci pour ton aide et tout le travail que tu as fait.
J'y jette un oeil rapidement et je reviens vers vous deux.
Bonne semaine
Bastien
Hello,
J'ai repris a tête reposée le code que tu proposes curulis mais je pense que cela va me prendre plus fde temps que prévu pour l'integrer et le tester car j'avoir que je ne le comprends pas :-(. Il me manque des elements de vocabulaire pour comprendre ce qui est fait et je vais prendre un peut de temps pour comprendre.
je voulais juste dire ça pour ne pas que tu (vous) pensiez que je ne répondais pas et que je ne donnais aps suite a vos message.
J'apprecie énormement le temps que vous avez deja passé pour m'aider.