Relier les données d'une même Feuille avec 2 Combobox

Signaler
Messages postés
4
Date d'inscription
mardi 16 octobre 2012
Statut
Membre
Dernière intervention
1 octobre 2013
-
Messages postés
9
Date d'inscription
mercredi 9 octobre 2013
Statut
Membre
Dernière intervention
24 janvier 2014
-
Bonjour,
J'ai une feuille (Feuill_Test) Excel dans la quelle je renseigne des données dans un tableau [Nlignes X 10 col].
Je souhaite créer 2 comboBox. La 1ère renvoyant les données contenues dans une colonne (disons A, sans doublons si possible) et la 2ème reliée à la 1ère qui renvoie tout les éléments/enregistrements possibles associés à (aux) ligne(s) où se trouvent les différentes valeurs du 1er Choix.
Cette 2ème combobox devra être une concaténation de 2 champs (pas forcément adjacent).
Ci-dessus un début de code que j'ai adapté :
Private Sub UserForm_Initialize():
ComboBox1.Text = " "
    ValeurUne = Range("A2").End(xlDown).Row
    ComboBox1.RowSource = "A2:A" & ValeurUne
End Sub

Le premier code fonctionne mais je ne sais pas comment relier la 2ème combobox à la 1ère et faire apparaitre les enregistrements corrspondants.

Vous avez une idée ou alternative aux combobx ( listbox peut être pour le 2ème Controle)?
Merci

7 réponses

Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Bonjour,
J'ai des difficultés à "cerner" ce que tu cherches à faire.
Peux-tu tenter de l'expliquer plus clairement ?
Que veut par exemple dire : "où se trouvent les différentes valeurs du 1er Choix" ? De quel choix (sorti d'où ?) parles-tu ?
Sans précisions exprimées techniquement, impossible de te répondre en parfaite connaissance de cause.

PS : Ta combobox se trouve donc (bien que n'en aies pas soufflé un mot) sur un userform. Le révèle la propriété RowSource utilisée (pas ton exposé).
Plusieurs points m'interpellent alors :
1) tu ne définis nulle part dans ton code la feuille utilisée. Or, ton code, placé sur un userform, considèrera qu'il ne peut s'agir que de la feuille active (dangereux si l'utilisateur active une autre feuille !)
2) je ne comprends pas le reste (la nécessité d'aller à nouveau "fouiller" pour trouver les correspondances d'une sélection faite dans cette combo (car j'imagine que c'est ce qu'a voulu dire ton exposé ambigü)
Je te rappelle :
- que sous VBA, une combo peut comporter autant de colonnes que celles de la plage à laquelle on la lie
- que tu peux en dimensionner à ton gré chacune des colonnes (ouvre ton aide VBA sur le mot columnwidth et lis)
- que dès lors une fois fait le choix en sa première colonne, tu peux lire dans ses autres colonnes les autres "valeurs" qui t'intéressent (y compris si, la largeur de ces autres colonnes = 0 et qu'elles ne sont alors pas visibles).
Voilà ...
Messages postés
30165
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
16 novembre 2020
338
Bonjour,

Je pense que le code ci-dessous devrait correspondre à votre demande :

Private Sub ComboBox1_Change()
'------------------------------------------------------------
' Au changement de "value" de la combobox1
' On recherche tous les enregistrements qui correspondent
' et on les affiche dans la comboBox2
'------------------------------------------------------------
Debug.Print "Valeur sélectionnée :" & Me.ComboBox1.Value

' Variables [ne pas changer]
Dim arrResult() As String

' Initialisation des Variables [ A MODIFIER ]
Dim MaFeuille As Worksheet
    Set MaFeuille = ThisWorkbook.Sheets(1)
Dim MaPlage As String
    MaPlage = "G2:G20"
Dim colonneValeurs As Integer
    colonneValeurs = 8
Dim colonneValeurs2 As Integer
    colonneValeurs2 = 9
    
'On remet la combobox2 à "vide"
Me.ComboBox2.Clear
    
'Recherche des valeurs correspondantes
    x = FindAll(Me.ComboBox1.Value, MaFeuille, MaPlage, arrResult())
    If x = True Then
        nbC = UBound(arrResult)
        Debug.Print nbC & " resultats trouvés "
        For i = 1 To nbC
            Me.ComboBox2.AddItem MaFeuille.Cells((arrResult(i)), colonneValeurs) & "-" & MaFeuille.Cells((arrResult(i)), colonneValeurs2)
        Next
    Else
        Debug.Print "Aucune correspondance trouvée !"
    End If


End Sub





Function FindAll(ByVal sText As String, ByRef oSht As Worksheet, ByRef sRange As String, ByRef arMatches() As String) As Boolean
' --------------------------------------------------------------------------------------------------------------
' FindAll - To find all instances of the1 given string and return the row numbers.
' If there are not any matches the function will return false
' --------------------------------------------------------------------------------------------------------------
On Error GoTo Err_Trap
Dim rFnd As Range ' Range Object
Dim iArr As Integer ' Counter for Array
Dim rFirstAddress ' Address of the First Find
' -----------------
' Clear the Array
' -----------------
Erase arMatches
Set rFnd = oSht.Range(sRange).Find(what:=sText, LookIn:=xlValues, lookAt:=xlPart)

If Not rFnd Is Nothing Then
 rFirstAddress = rFnd.Address
 Do Until rFnd Is Nothing
 iArr = iArr + 1
 ReDim Preserve arMatches(iArr)
 arMatches(iArr) = rFnd.Row 'rFnd.Address pour adresse complete ' rFnd.Row Pour N° de ligne
 Set rFnd = oSht.Range(sRange).FindNext(rFnd)
 If rFnd.Address = rFirstAddress Then Exit Do ' Do not allow wrapped search
 Loop
 FindAll = True
Else
 ' ----------------------
 ' No Value is Found
 ' ----------------------
 FindAll = False
End If
' -----------------------
' Error Handling
' -----------------------
Err_Trap:
If Err <> 0 Then
 MsgBox Err.Number & " " & Err.Description, vbInformation, "Find All"
 Err.Clear
 FindAll = False
 Exit Function
End If
End Function

Private Sub UserForm_Initialize():
ComboBox1.Text = " "
    ValeurUne = Range("A2").End(xlDown).Row
    ComboBox1.RowSource = "A2:A" & ValeurUne
End Sub




Il faudra, bien entendu, faire correspondre les variables à vos besoins :
- MaFeuille
- MaPlage
( et pour les colonnes contenant les valeurs à récupérer :
- colonneValeurs
- colonneValeurs2 ...

Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Bonjour, Jordane45,
Entre autres :
- Eviter d'utiliser l'évènement Change, qui se déclenchera également à chaque pression d'une touche dans la zone d'édition de la combo. Lui préférer l'évènement Click. L'index cliqué est alors tout simplement combobox1.listindex, l'article cliqué (à chercher, donc) étant combobox1.list(combobox1.listIndex), sans de cette manière, le moindre risque.
- l'article cliqué existe forcément sur la feuille (nul besoin de traiter une absence éventuelle) puisque nous avons une combobox liée

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviendrai que si nécessité de la compléter.
Messages postés
30165
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
16 novembre 2020
338
Salut ucfou.
Ce n'est qu'un exemple. ..
T puis peut être souhaite t'il aussi avoir le déclenchement via le clavier...
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Le "déclenchement via le clavier"' est une chose. Celui via la frappe dans la zone d'édition en est une autre.
Attendons-le donc, mais (en dépit de certaines ambigüités) il ne me parait pas souhaiter rechercher un texte qu'il frapperait dans la zone d'édition, mais celui correspondant à un article sélectionné parmi ceux présents dans la combo.
Par ailleurs et surtout : imagine qu'il s'amuse à frapper "toto" dans la zone d'édition ===>> l'évènement Change serait déclenché à la frappe de "t" (pour rechercher ... "t"), puis de "o" (pour rechercher "to"), puis à la frappe du 2ème "t" (pour rechercher "tot"), etc ...
Essaye (frappe quelque-chose dans la zone d'édition de la combo) et constates (avec ton code).
Amitiés.
Messages postés
30165
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
16 novembre 2020
338
Essaye (frappe quelque-chose dans la zone d'édition de la combo) et constates (avec ton code).
Je sais parfaitement ce que fait mon code... pas de soucis...
Je m'en servais.. entre autre.. pour faire de l'auto complétion sur mes contrôles..

Par ailleurs et surtout : imagine qu'il s'amuse à frapper "toto" dans la zone d'édition dans la zone d'édition l'évènement Change serait déclenché à la frappe de "t" (pour rechercher ... "t"), puis de "o"....
On peut ajouter un control sur le nombre de caractères..
Mais bon, ce n'est pas l'idéal.. je ne dis pas le contraire..

Mais je persiste.. ce n'est qu'un exemple...

Mais je ne doute pas que d'autres personnes auront d'autres solutions à proposer.. mais bon.. en attendant de les voir écrites ici...ça permet déjà de donner matière à réflexion à l'utilisateur et peut-être, si cette réponse ne lui convient pas (totalement) de pouvoir, maintenant qu'il a pu explorer de nouveaux horizons, reformuler sa question et/ou redéfinir plus précisément son besoin.
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Bon.
Si (et seulement si) la traduction technique de la question posée est celle-ci :
A partir des données d'une plage P (en colonne A) d'une feuille : alimenter sans doublons une combobox combobox1
Lorsque l'on sélectionne un article dans combobox1 : alimenter une seconde combobox combobox2 par des données relevées sur toutes les lignes de la plage P contenant, en colonne A, la valeur sélectionnée en combobox1
===>>
il faut alors changer totalement son fusil d'épaule :
- la combobox1 ne doit pas être liée
- il va lui falloir penser à une autre stratégie, pouvant faire appel à une feuille de calcul tremplin. Le tri sur cette feuille-là facilitera et agilisera tant l'alimentation sans doublon de la combobox1 que la recherche des lignes correspondant à la sélection faite.
Voilà ===>> il n'y a plus qu'à ...

PS : il est également possible d'envisager 2 colonnes (dont la seconde cachée) pour le combobox1. L'utilisation d'un dictionnaire ou d'une collection permettra d'inscrire en colonne 2 les numéros de lignes (la première, puis les autres si doublons, séparés par des virgules). Dans ce cas : même plus besoin d'une feuille tremplin.
A la sélection dans Combobox1 ===>> on connait (voir plus haut) l'index sélectionné ===>> on lit ce que l'on a en colonne cachée 2 ===>> un simple split sur virgule ===>> on a d'emblée toutes les lignes de la plage concernée contenant, en colonne A; la valeur sélectionnée. Simple, ultra-rapide et efficace
Messages postés
4
Date d'inscription
mardi 16 octobre 2012
Statut
Membre
Dernière intervention
1 octobre 2013

Bonjour ucfoutu, Jordane45,

Merci pour vos réponses. Le code fonctionne quand il est exécuté dans un classeur indépendant. Cependant quand je l'exécute dans un classeur contenant un autre userform (qui permeT d'enregistrer les données sur la Feuille1 en question) il ne fonctionne pas.
Messages postés
30165
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
16 novembre 2020
338
Bonjour,
Le code fonctionne quand il est exécuté dans un classeur indépendant
c'est l'avantage de ce code.


quand je l'exécute dans un classeur contenant un autre userform
- Est-ce que ce userform est affiché également à l'écran ?
- Dans l'exemple que j'ai donné, j'ai utilisé "ME".
Vous pouvez le remplacer par le nom de vos userform
exemple:
me.show
'c'est la même chose que :
userform1.show


il ne fonctionne pas.

Pourriez-vous préciser ?
Message d'erreur ? en Mode pas à pas ça donne quoi ? etc..
Messages postés
4
Date d'inscription
mardi 16 octobre 2012
Statut
Membre
Dernière intervention
1 octobre 2013

les 2 userform 1 et 2 sont lancés via 2 boutons par macros dans 2 modules différents
UserForm1.Show vbModeles
UserForm2.Show vbModeles


- Est-ce que ce userform est affiché également à l'écran ?
- Dans l'exemple que j'ai donné, j'ai utilisé "ME".
Vous pouvez le remplacer par le nom de vos userform
exemple:

Non il n'est pas affiché à l'écran. Il faut quitter le premier userform1 , ensuite lancer le 2ème. Je viens de constater que çà marche quand j'écrase un enregistrement. Je dois rafraichir les informations? Et dans les 2 Userforms j'ai ceci:
Dim Feuil1 As Worksheet
    Set Feuil1 = ThisWorkbook.Sheets(1)

Feuil1 étant celle dans laquelle on enregistre (via bouton 1) et on filtre via bouton 2 qui lance userform 2

Une question, avant de poursuivre je peux utiliser la même méthode dans le module de récupération des variables combox2, pour faire récuperer des valeurs dans des textBox et faire des calculs? (les données de calculs sont aussi sur les même champs)

cdlt
Messages postés
30165
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
16 novembre 2020
338
Une question, avant de poursuivre je peux utiliser la même méthode dans le module de récupération des variables combox2, pour faire récuperer des valeurs dans des textBox et faire des calculs
Oui bien sur.
La fonction FindAll peut être appelée par n'importe quelle macro.
Cette fonction te retourne un array contenant la liste des lignes où se trouvent les valeurs recherchées.
Il te suffit ensuite de boucler sur ce tableau pour alimenter tes textbox en leur attribuant la valeur des cellules qui se trouvent à l'intersection de chaque ligne de l'array et de la colonne où se trouvent les données à récupérer.
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Je crois que je vais vous laisser continuer sur vos lancées et attendre leur aboutissement pour venir (sur la base que j'ai indiquée plus haut) avec une manière bien plus agile et rapide de traiter ...(y compris les non doublons dans la 1ère combo)
A bientôt.
Messages postés
4
Date d'inscription
mardi 16 octobre 2012
Statut
Membre
Dernière intervention
1 octobre 2013

Jordane, ucfoutu
ucfoutu -
Je crois que je vais vous laisser continuer sur vos lancées et attendre leur aboutissement pour venir (sur la base que j'ai indiquée plus haut) avec une manière bien plus agile et rapide de traiter ...(y compris les non doublons dans la 1ère combo)
A bientôt.

Toutes les solution sont les bienvenues. Concernant ceci:
Bon.
Si (et seulement si) la traduction technique de la question posée est celle-ci :
A partir des données d'une plage P (en colonne A) d'une feuille : alimenter sans doublons une combobox combobox1
Lorsque l'on sélectionne un article dans combobox1 : alimenter une seconde combobox combobox2 par des données relevées sur toutes les lignes de la plage P contenant, en colonne A, la valeur sélectionnée en combobox1 ===>>


C'est exactement ce que je souhaite faire. Le code de jordane45 répond a cette demande. Mais Si vous pensez tout les 2 qu'il y a une manière de faire qui m'évitera de reprendre le code quand je devrai faire des améliorations (plusieurs textbox avec calculs, dans certains cas sous conditions (différence entre 2 valeurs récupérées via find all par exemple )....
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Moi, ami, je ne pense rien, en ce qui concerne :
"qui m'évitera de reprendre le code quand je devrai faire des améliorations (plusieurs textbox avec calculs, dans certains cas sous conditions (différence entre 2 valeurs récupérées via find all par exemple )...."
qui s''écarte de la question et ne dépend que de tes aptitudes à "assurer" ou non une suite.
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Bon...
Je n'ai pas oublié ma promesse, hein ... j'attendais simplement vos évolutions éventuelles.
Qu'ai-je fait pendant tout ce temps ?
J'ai traité (y compris sans les doublons dans la combobox1) par différentes méthodes (avec collection, avec dictionnaire, avec un pseudo recordset, etc ...). Pour chacune de ces méthodes, comme pour celle que je vais exposer plus bas, je me suis rigoureusement abstenu de revenir "fouiller" (les Find) sur la feuille de calcul.

Toutes sont très rapides.

J'ai toutefois retenu, pour vous l'exposer, la solution qui va suivre et qui, elle, ne fait appel qu'à des connaissances classiques (pas de collection, pas de dictionnaire, pas de pseudo recordet) de VBA/Excel.
Pas de connaissances VBA spéciales nécessaires, donc, mais par contre des dispositions relevant du goût pour certaines disciplines sportives (travail aux agrès, acrobatie, etc...). Cela ne s'apprend pas (nulle part) et ne relève que de l'envie et la possibilité de créer, inventer, construire.

Je vais, dans le code qui suit, profiter du parcours qu'impose le traitement des doublons pour "relever" dans la foulée ce dont nous avons besoin pour la suite (et donc éviter des Find ultérieurs). Pour éviter un dictionnaire, je vais par ailleurs profiter des particularités des comboboxes de VBA (elles sont différentes de celles de VB6)

Sur notre userform :
- une combobox combobox1, où seront affichés, sans doublons, les choix possibles de la plage spécifiée
- une comboibox combobox2, où seront affichées les valeurs correspondantes de toutes les lignes répondant à ce critère

on lance ===>> on choisit dans combobox1 ===>> on constate le résultat obtenu dans combobox2

Code :

Option Explicit
Private fait As Boolean, cols_a_afficher, a
Private Sub ComboBox1_Click()
  Dim i As Long, j As Long, toto
  If fait Then
    With ComboBox2
      .Clear
      .ColumnCount = UBound(cols_a_afficher) + 2
      toto = Split(ComboBox1.List(ComboBox1.ListIndex, 1), Chr(1))
      For i = 0 To UBound(toto)
        .AddItem ComboBox1.List(ComboBox1.ListIndex, 0)
        For j = 0 To UBound(cols_a_afficher)
          .List(i, j + 1) = a(toto(i), Columns(cols_a_afficher(j)).Column)
        Next
      Next
    End With
  End If
  ComboBox1.ListIndex = -1
End Sub


Private Sub UserForm_Activate()
 Dim sh As Worksheet, plage As Range, derlig As Long, premlig As Long, dercol As String, premcol As String
 cols_a_afficher = Array("B", "D", "E")
 Dim i As Long, ou As Long
 dercol = cols_a_afficher(UBound(cols_a_afficher))
 premlig = 4
 premcol = "A"
 With ComboBox1
   .ColumnCount = 2
  .ColumnWidths = "20;0"
   Set sh = Workbooks("jordane.xlsm").Sheets("Feuil1")
   derlig = sh.Range("A" & Rows.Count).End(xlUp).Row
   Set plage = sh.Range(premcol & premlig & ":" & dercol & derlig)
   a = plage
   ComboBox2.ColumnCount = 1
   ComboBox2.Clear
   For i = 1 To UBound(a)
     ComboBox2.Text = a(i, 1)
     ou = ComboBox2.ListIndex
     ComboBox2.Text = ""
     If ou = -1 Then
       .AddItem a(i, 1)
       ComboBox2.AddItem a(i, 1)
       DoEvents
       .List(.ListCount - 1, 1) = i
     Else
       .List(ou, 1) = .List(ou, 1) & Chr(1) & i
   End If
   Next
   fait = True
 End With
End Sub


Voilà : ne pas oublier de remplacer :
- jordane.xlsm par le nom du classeur (qui doit être ouvert) où sont lues les données
- Feuil1 par le nom de la feuille où sont lues les données
dans la variable
- premlig = 2 par le numéro de la première ligne de la plage lue
- premcol = "A" par la lettre de la première colonne de cette plage
dans cols_a_afficher = Array("B", "D", "E") : remplacer B,D et E par les colonnes dont on veut afficher les valeurs en combobox2. Le nombre de ces colonnes est flexible (ex : "C","K","M,"N") mais elles doivent être mises par ordre alphabétique ascendant.
Bonne soirée

PS : Ah oui ! J'ai oublié de mentionner une autre discipline : le ping-pong (joué entre les deux combos au moment même où l'on remplit la 1ère).

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviendrai que si nécessité de la compléter.
Messages postés
9
Date d'inscription
mercredi 9 octobre 2013
Statut
Membre
Dernière intervention
24 janvier 2014

slt,
jai recopié et collé votre code en changeant bien sur le nom du classeur, le nom de feuille, les colonnes et lignes.
mais lorsque je clique sur une valeur de la combobox1, un message d'erreur de compilation apparait et il est ecrit sub ou fonction non definit puis la lettre "a" de la ligne suivante est selectionnée

.list(i, j + 1) = a(toto(i), Columns(cols_a_afficher(j)).Column)