Afficher les selections multiple d'une Listbox dans une seule cellule en séparant par une virgule

Version imprimable

Voir 40 message(s) de cette discussion en une page

2 pièce(s) jointe(s)

Afficher les selections multiple d'une Listbox dans une seule cellule en séparant par une virgule

Bonjour à tous,

Nouvellement en stage en entreprise afin de cloturer mon master en qualité des produits de santé, une nouvelle tâche des plus ardues (pour moi) m'a été confiée.
On m'a chargé de réaliser un formulaire afin de simplifier le listing d'arrêt de commercialisation.

Grâce à de multiples tutoriels j'ai réussi à réaliser mon formulaire avec des boutons commandes me permettant de créer une nouvelle fiche, d'éditer une existante et un autre pour de sortir de mon formulaire. De ce côté là tout fonctionne très bien !

Le problème qui se présente est le suivant : On me demande d'ajouter à mon tableau la liste des services concernés par cet arrêt de commercialisation. Pour se faire j'ai créée une Listbox à sélection multiple contenant les noms de mes différents services via RawSources. Je voudrais que les services sélectionnés apparaissent dans une seule et même cellule de la colonne H et que l'intéraction avec mes boutons Nouvelle produit (command button 1) et Editer (commande button 2) soit possible.
Jusqu'à présent je n'ai réussi qu'a faire apparaitre les selections multiples dans LES dernières cases vides de ma colonne H et je suis bloqué. J'ai commencé VBA hier et beaucoup de choses me dépassent. Je ne sais pas si j'ai été clair dans ma demande.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 Option Explicit Dim Ws As Worksheet 'Pour le formulaire Private Sub UserForm_Initialize() Dim J As Long Dim I As Integer Set Ws = Sheets("Produits") 'Correspond au nom de votre onglet dans le fichier Excel With Me.ComboBox1 For J = 2 To Ws.Range("A" & Rows.Count).End(xlUp).Row .AddItem Ws.Range("A" & J) Next J End With With Me.ComboBox2 For J = 2 To Ws.Range("B" & Rows.Count).End(xlUp).Row .AddItem Ws.Range("B" & J) Next J End With For I = 1 To 5 Me.Controls("TextBox" & I).Visible = True Next I End Sub 'Pour la liste d?roulante numéro d'arret de commercialisation Private Sub ComboBox1_Change() Dim Ligne As Long Dim I As Integer If Me.ComboBox1.ListIndex = -1 Then Exit Sub Ligne = Me.ComboBox1.ListIndex + 2 ComboBox2 = Ws.Cells(Ligne, "B") For I = 1 To 5 Me.Controls("TextBox" & I) = Ws.Cells(Ligne, I + 2) Next I End Sub 'Pour le bouton Nouveau produit Private Sub CommandButton1_Click() Dim L As Integer If MsgBox("Confirmez-vous l'insertion de ce nouveau produit?", vbYesNo, "Demande de confirmation d'ajout") = vbYes Then L = Sheets("Produits").Range("a65536").End(xlUp).Row + 1 'Pour placer le nouvel enregistrement ? la premi?re ligne de tableau non vide Range("A" & L).Value = ComboBox1 Range("B" & L).Value = ComboBox2 Range("C" & L).Value = TextBox1 Range("D" & L).Value = TextBox2 Range("E" & L).Value = TextBox3 Range("F" & L).Value = TextBox4 Range("G" & L).Value = TextBox5 End If End Sub 'Pour le bouton Modifier Private Sub CommandButton2_Click() Dim Ligne As Long Dim I As Integer If MsgBox("Confirmez-vous la modification de ce produit ?", vbYesNo, "Demande de confirmation de modification") = vbYes Then If Me.ComboBox1.ListIndex = -1 Then Exit Sub Ligne = Me.ComboBox1.ListIndex + 2 Ws.Cells(Ligne, "B") = ComboBox2 For I = 1 To 5 If Me.Controls("TextBox" & I).Visible = True Then Ws.Cells(Ligne, I + 2) = Me.Controls("TextBox" & I) End If Next I End If End Sub 'Pour le bouton Quitter Private Sub CommandButton3_Click() Unload Me End Sub
Mon formulaire ressemble à ca pour le moment Pièce jointe 475433
Je dois vraisemblablement utiliser mon command button 4 "OK" pour réaliser l'opération souhaitée (enfin je crois) mais malgré mes différentes recherches sur les forums je n'ai pas trouvé de topic me permettant de résoudre mon problème avec le peu de connaissance en VBA dont je dispose. Je pense être passé plusieurs fois devant la solution mais ma compréhension limité du langage VBA m'a empêché d'adapter les exemples vus à mon problème.

Mon tableau se présente comme ceci et l'objectif et de faire apparaitre chaque services concernés par l'arret dans la colonne H de la ligne correspondant à chaque produit.
Pièce jointe 475434

Je vous remercie par avance de l'aide que vous pourrez m'apporter.
Cordialement,
Erwan.

Bonjour,

Pour boucler sur les sélections d'une Listbox

(Issu du tutoriel de Silkyroad)

Code:

1
2
3
4
5
6
7
8
Private Sub commandButton1_Click()
    Dim i As Byte
 
    'boucle sur les éléments de la listbox
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then MsgBox ListBox1.List(i)
    Next i
End Sub

Tu peux donc coder

Code:

1
2
3
4
5
6
7
8
9
10
11
Dim i As Byte 'A adapter
 
With Worksheet(1).Range("A1")
  .Value = ""
  For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then .Value = .Value & ListBox1.List(i) &","
  Next i
  .Value =Left(.Value, Len(.Value)-1)
End With
 
End With

Code à adapter.

Bonjour Marcel, tout d'abord merci de t'être penché la dessus !
Alors, pour le tuto de Silkyroad j'ai eu l'occasion de passer pas mal de temps dessus mais comme je le disais ma méconnaissance de ce langage m'a empêché d'adapter l'exemple à mon cas, mes tentatives ayant résulté en l'affichage de mes sélections sur plusieurs cellules (H43 H44 et H45 par exemple pour 3 services concernés).

Si j'ai tout saisi (ce dont je doute hahaha) je dois coder
Code:

1 2 3 4 5 6 7 8 9 10 Private Sub commandButton4_Click() Dim i As Byte With Worksheet(Produits).Range("H1") .Value = "" For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then .Value = .Value & ListBox1.List(i) &"," Next i .Value =Left(.Value, Len(.Value)-1) End With
Pour ma feuille Produits contenant mon tableau et si je veux que mes sélections multiples apparaissent dans la colonne H ?

16/05/2019, 19h19
Sparfell29

J'essaye en arrivant au stage demain matin. Si ton activité professionnelle te permet de me répondre durant la journée je reviendrai surement vers toi en larme avec mes bouts de code dans les mains :aie::mouarf:
J'espère réussir à adapter ca quand même !

Bonjour,
La fonction Join, permet de regrouper tous les éléments d'un tableau (variable dimensionnée) en les concaténant et en les séparant par un délimitteur

Exemple pour tous les éléments sélectionnés dans un ListBox

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Private Sub CommandButton2_Click()
  'Dim i As Byte
  Dim Elem As Integer, Count As Integer
  Dim tbl()
  ' Charge les éléments sélectionnés dans une table
  With ListBox1
    For Elem = 0 To .ListCount - 1
      If .Selected(Elem) Then
         ReDim Preserve tbl(Count)
         tbl(Count) = .List(Elem)
         Count = Count + 1
      End If
    Next
  End With
  ' Ecrit tous les éléments de la table dans une cellule en séparant chaque élément par une virgule
  With Worksheets("Produits").Range("H1")
  .Value = Join(tbl, ",")
  End With
End Sub

A lire pour en savoir plus Utiliser les variables tableaux en VBA Excel

17/05/2019, 01h22
Sparfell29

Je commence à y voir plus clair il faut que je précise ma question. Je ne souhaite pas que cela soit dans une cellule précise de la colonne H (ici H1) mais que cela apparaisse dans la cellule de la colonne H sur la même ligne que celle remplie avec mes autres données lors de l'édition ou la création d'un nouveau produit. Ai-je simplement besoin de stipuler "H" tout court la où il y a H1 dans ton code Philippe ? Je dois aussi je pense changer le code de mon bouton Nouveau produit en ajoutant
Range("H" & L).Value = ListBox1 et changer tous mes For I = 1 to 5 en For I = 1 to 6

Merci beaucoup de l'aide que vous m'apportez tous deux,
Erwan.

Salut.

Perso, j'essaie que mon userform n'interagisse pas directement avec ma feuille, mais mette à disposition du code appelant les données à placer dans la feuille. Ca permet de respecter une architecture cohérente, de mieux séparer les responsabilités de chaque partie de code. Ca facilite la maintenance, la réutilisation du code, son évolution et son débogage.

Ainsi, un problème est de récupérer les données de la liste en les concaténant, un autre est de décider de ce que tu fais de cette valeur. Dès lors, la responsabilité du userform est de renvoyer la chaine concaténée, la responsabilité du code qui a ouvert le userform est de traiter les données saisies dans celui-ci.

Je propose le code suivant dans le userform. On y voit une propriété ajoutée au userform qui traite les données.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Public Result As String Private Sub btnValidate_Click() Result = "Validate" Me.Hide End Sub Property Get Choices() As String Dim Counter As Long With lboChoice For Counter = 0 To .ListCount - 1 If .Selected(Counter) Then If Choices = "" Then Choices = .List(Counter) Else Choices = Choices & ";" & .List(Counter) End If End If Next End With End Property
Le code appelant va traiter cela à la fermeture du userform, avant son déchargement. Tu vois que tu peux utiliser la valeur renvoyée par la propriété et en faire ce que tu veux (la mettre en H1, la placer dans une variable pour l'utiliser par la suite, tout autre traitement)
Code:

1 2 3 4 5 6 7 8 9 10 11 Sub Test() With UserForm1 .lboChoice.Clear .lboChoice.List = Array("Pomme", "Poire", "Abricot", "Prune", "Pêche") .Show If .Result = "Validate" Then Range("h1").Value = .Choices End If End With Unload UserForm1 End Sub

17/05/2019, 09h16
Sparfell29

Bonjour,
J'ai essayé ta solution Pierre en remplaçant les fruits présents dans Array par le nom de mes services et en allant dire à ma ListBox de ne plus aller chercher ses infos grâce à Rawsouces mais cela ne fonctionne pas. A quoi correspond btnValidate_Click() ? Si je réussis comment faire pour que les données traitées dans la formulaire n'apparaissent pas en H1 spécifiquement mais dans la bonne ligne et la colonne H lors de la création d'un nouvel arrêt de commercialisation ?

1 pièce(s) jointe(s)

il faut vider la propriété RowSource du listbox, puis renseigner la plage souhaitée à la place de l'array "en dur" dans la procédure appelante

Avec les fruits en A1:A7 de la feuille active au moment de l'affichage du userform, ça donne ceci
Code:

1 2 3 4 5 6 7 8 9 10 11 Sub Test() With UserForm1 .lboChoice.Clear .lboChoice.List = Range("a1:a7").Value .Show If .Result = "Validate" Then Range("h1").Value = .Choices End If End With Unload UserForm1 End Sub
Idéalement, tu travailleras avec une table de données (tableau structuré) pour ne pas devoir toucher à ton code lors du chargement des items de la liste. La référence structurée s'adaptant automatiquement à l'ajout ou la suppression des données dans la liste, ainsi qu'à son éventuel déplacement à un autre endroit du classeur. Avec ce genre de code, tu es "sur du velours", la maintenance et l'évolution du code étant assurées.
Code:

1 2 3 4 5 6 7 8 9 10 11 Sub Test() With UserForm1 .lboChoice.Clear .lboChoice.List = Range("t_Fruits[Fruits]").Value .Show If .Result = "Validate" Then Range("h1").Value = .Choices End If End With Unload UserForm1 End Sub
Dans le fichier joint, n'hésite pas à lancer la procédure appelante en pas à pas pour bien comprendre chaque ligne exécutée. Le btnValidate_Click est l'évènement de click du bouton de validation, et son seul rôle est de rendre la main au code appelant en masquant le userform et en permettant au code de savoir comment tu es sorti du userform. Le code appelant utilise la propriété publique Result (souvent appelée erronément variable publique) dès qu'il reprend la main pour décider de la suite du traitement.

Après vidage du rawsource et modification dans ARRAY je n'ai plus aucun élément qui apparait dans la listbox. Quand j'execute le formulaire j'ai aussi un message d'erreur de variable non définie au niveau de

Code:

1
2
3
4
Private Sub CommandButton4_Click()
  Result = "Validate"
  Me.Hide
End Sub

L'entièreté de mon code ressemble à cela maintenant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
Option Explicit
Dim Ws As Worksheet
 
 
'Pour le formulaire
Private Sub UserForm_Initialize()
    Dim J As Long
    Dim I As Integer
 
 
    Set Ws = Sheets("Produits") 'Correspond au nom de votre onglet dans le fichier Excel
    With Me.ComboBox1
        For J = 2 To Ws.Range("A" & Rows.Count).End(xlUp).Row
            .AddItem Ws.Range("A" & J)
        Next J
    End With
    With Me.ComboBox2
        For J = 2 To Ws.Range("B" & Rows.Count).End(xlUp).Row
            .AddItem Ws.Range("B" & J)
        Next J
    End With
    For I = 1 To 5
        Me.Controls("TextBox" & I).Visible = True
    Next I
End Sub
 
'Pour la liste d?roulante numéro d'arret de commercialisation
Private Sub ComboBox1_Change()
    Dim Ligne As Long
    Dim I As Integer
 
    If Me.ComboBox1.ListIndex = -1 Then Exit Sub
    Ligne = Me.ComboBox1.ListIndex + 2
    ComboBox2 = Ws.Cells(Ligne, "B")
    For I = 1 To 5
        Me.Controls("TextBox" & I) = Ws.Cells(Ligne, I + 2)
    Next I
End Sub
 
'Pour le bouton Nouveau produit
Private Sub CommandButton1_Click()
    Dim L As Integer
    If MsgBox("Confirmez-vous l'insertion de ce nouveau produit?", vbYesNo, "Demande de confirmation d'ajout") = vbYes Then
        L = Sheets("Produits").Range("a65536").End(xlUp).Row + 1 'Pour placer le nouvel enregistrement ? la premi?re ligne de tableau non vide
        Range("A" & L).Value = ComboBox1
        Range("B" & L).Value = ComboBox2
        Range("C" & L).Value = TextBox1
        Range("D" & L).Value = TextBox2
        Range("E" & L).Value = TextBox3
        Range("F" & L).Value = TextBox4
        Range("G" & L).Value = TextBox5
 
    End If
End Sub
 
'Pour le bouton Modifier
Private Sub CommandButton2_Click()
    Dim Ligne As Long
    Dim I As Integer
 
    If MsgBox("Confirmez-vous la modification de ce produit ?", vbYesNo, "Demande de confirmation de modification") = vbYes Then
        If Me.ComboBox1.ListIndex = -1 Then Exit Sub
        Ligne = Me.ComboBox1.ListIndex + 2
        Ws.Cells(Ligne, "B") = ComboBox2
        For I = 1 To 5
            If Me.Controls("TextBox" & I).Visible = True Then
                Ws.Cells(Ligne, I + 2) = Me.Controls("TextBox" & I)
            End If
        Next I
    End If
End Sub
 
'Pour le bouton Quitter
Private Sub CommandButton3_Click()
    Unload Me
End Sub
Private Sub CommandButton4_Click()
  Result = "Validate"
  Me.Hide
End Sub
 
Property Get Choices() As String
  Dim Counter As Long
 
 Sub Test()
  With UserForm1
    .lboChoice.Clear
    .lboChoice.List = Array("Service Réglementaire et Sécurité", "Service Développement R&D", "Service MKT FR", "Service MKT INT", "Service Achat", "Service Logistique", "PAYS CANADA", "Service Approvisionnement", "Service Qualité")
    .Show
    If .Result = "Validate" Then
      Range("h1").Value = .Choices
    End If
  End With
  Unload UserForm1
End Sub
Private Sub UserForm_Click()
 
End Sub

Je manque surement de pugnacité mais j'envisage de plus en plus de remplacer ma listbox par une textbox et laisser les gens se débrouiller pour remplir ça à la main plutot qu'en selectionnant :/. Je sais bien que plus on échoue plus on a de chances de réussir mais je patauge ^^'

17/05/2019, 10h08
Pierre Fauconnier
Pas de panique... ;)

Avec ton code, tu utilises l'évènement Initialize de ton userform pour charger ta listbox. Perso, je pense que c'est une mauvaise idée, mais si tu le fais ainsi, alors, tu ne dois pas charger la listbox dans la procédure d'appel. Je te conseille toutefois de procéder en chargeant la liste à l'appel plutôt que par Initialize. C'est pour moi la façon la plus propre de coder, elle permet une systématisation de l'approche en déterminant qui fait quoi dans le code? Normalement, un userform ne peut pas aller lire ou écrire directement dans une feuille de calcul^*

J'ai mis un fichier d'exemple dans mon dernier message. Si tu ouvres le fichier et que tu lances la procédure Test du module 1 en pas à pas (tu cliques dans la procédure puis tu presses F8 pour exécuter chaque ligne en jaune), tu comprendras chaque étape du process, qui n'est vraiment pas complexe:
1. le listbox reçoit une plage de données via sa propriété List (la plage est définie par une référence structurée);
2. Le listbox est affiché;
3. Après le choix des données, l'utilisateur clique sur OK (le userform est refermé et le code revient dans la procédure appelante);
4. La procédure appelant récupère la chaine des choix (créée par la propriété Choices du userform);
5. Cette chaine est poussée dans une cellule (ou une variable ou tout ce que tu veux);
6. Le userform est déchargé de la mémoire.
Cette architecture est en fait simple à mettre en place lorsque tu auras pris le temps de la faire fonctionner en pas à pas pour en comprendre chaque ligne. Mais il faut passer par là pour comprendre ;) (et on est tous passés par là, tu sais, ça ne nous est pas tombé du ciel ;) )

^*
Dans une bonne architecture de développement, le userform fait partie de la couche de présentation et cette couche ne peut pas dialoguer directement avec les données. Ce n'est pas son rôle et si tes données changent de place, tu vas devoir repasser toute ton appli en revue pour modifier chaque ligne de code qui référençait l'ancienne place. C'est notamment pour cela que, pour minimiser ce risque, je parle d'utiliser les références structurées et les tables de données, garantes d'un meilleur fonctionnement de ton code. Excel permet à un userform d'être lié directement aux feuilles de données (ainsi que les listbox et combobox, d'ailleurs), mais c'est tout sauf une bonne idée. Ca semble simplifier l'approche mais en fait, ça t'enferme dans une architecture compliquée qui devient vite une galère lors de l'évolution de ton développement. C'est pourquoi je te conseille vraiment de prendre le temps de comprendre l'architecture que je te propose. Je ne sais pas ce que tu étudies, mais si c'est l'informatique, tu gagneras à mettre en place les bonnes pratiques dès le début de tes apprentissages ;)
17/05/2019, 11h00
Sparfell29
Je ne comprends pas pourquoi ma listbox est chargée avec Initialize. Je ne fais pas référence à Listbox1 en dessous de
Code:

1 2 3 Private Sub UserForm_Initialize() Dim J As Long Dim I As Integer
à moins que cela ne fonctionne pas comme ça :/ ?

Pour ce qui est de l'execution pas à pas du module 1 :
- Si j'ai bien compris Module 1 est une macro qui remplie la listbox de l'userform ?
- Range est la référence structurée fournissant la plage de données
-
Code:

1 2 btnValidate_Click() Result = "Validate"
Confirme le choix effectués par la selection multiple
Code:

1 2 If .Result = "Validate" Then Range("h1").Value = .Choices
Pousse la chaine dans la cellule H1
Code:

1 2 Unload UserForm1 End Sub
décharge le formulaire je suppose ^^'

Il y a des subtilités de langage que je ne suis pas sûr de saisir tel que "appelant" "boucler" entre autres.
Je me suis lancé dans ce formulaire sans posséder ne serait ce que les bases de VBA, je suis en master 2 en qualité des produits de santé et je ne suis pas coutumier du tout de quelque langage de code que ce soit. C'est très challenging pour moi de réussir à faire ce formulaire. Je dois bien avouer que les notions d'architecture me dépasse un petit peu, je pensais qu'un formulaire servait simplement à faciliter le remplissage d'un tableau de données et je me retrouve finalement à devoir me servir de conditions If et Else qui n'était que des modals me faisant penser aux portes logiques que j'avais vu il y une dizaine d'année au lycée il y encore deux jours.
17/05/2019, 11h13
Pierre Fauconnier

Sorry, ce sont tes combobox qui sont chargés dans le Initialize.

Laisse tomber l'architecture et ses notions, ce n'est pas très important. Quant à Unload, ça veut simplement dire que le formulaire est vidé de la mémoire (il n'existe plus).

Ce que tu décris est bien ce qui doit se produire. Tu es donc dans le bon... On prend les cellules d'une colonne et on les pousse dans la listbox, on affiche la listbox puis, lorsque l'utilisateur la ferme, on récupère les lignes sélectionnées de la listbox. C'est aussi simple que cela.

Pour ce qui est des if et des boucles, cela vient du fait que les listbox ne permettent pas de récupérer directement les lignes sélectionnées. Perso, je trouve que ça manque, mais on est bien obligé de faire sans et donc, de travailler avec une boucle pour passer sur chaque ligne et tester si elle est sélectionné ou pas. Je trouve que les développeurs de ce contrôle auraient pu pousser la gentillesse à nous permettre la récup directe, mais ce n'est pas le cas.

Cela dit, sans boucles et sans if, tu n'iras pas loin en programmation... :?

Est-ce que ton truc fonctionne? As-tu pu transférer ce que j'ai montré à ton fichier?
17/05/2019, 13h03
Sparfell29

Non je n'ai pas réussi la fonction Array ne m'affiche aucun des services affichés entre guillemets dans la ListBox.
Si je veux essayer avec la fonction Range sachant que ma feuille s'appelle Produits et que la liste contenant les différents services (équivalent à tes fruits) s'appelle Tableau2 je dois écrire

Code:

.lboChoice.List = Range("t_Tableau2[Produits]").Value

?
17/05/2019, 13h05
Pierre Fauconnier

Oui, comme je te le montre dans le fichier d'exemple. Dans ce cas, il n'y a pas d'array.

Si tu peux mettre ton fichier de travail (pas de données sensibles), fais-le, je jetterai un coup d'oeil
17/05/2019, 13h09
Sparfell29

Je vais juste supprimer les noms et codes produits pour pas me faire virer la première semaine mais je pense pouvoir l'envoyer !
17/05/2019, 13h12
Sparfell29

1 pièce(s) jointe(s)

Pièce jointe 475634
17/05/2019, 13h12
Pierre Fauconnier

Ok. Sinon, je ne comprends pas où ça bloque. Il y a 15 lignes de codes à tout casser... :?
17/05/2019, 13h15
Sparfell29

Ma listbox est complétée par rawsource ici car j'avais besoin de pouvoir montrer ce que ca donnait en réunion tout à l'heure mais j'ai bien compris que ce n'était pas comme ca que je devais faire
17/05/2019, 13h16
Sparfell29

Il y a de fortes chances que ca soit moi l'élément bloquant :calim2::calim2::oops:

Voir 40 message(s) de cette discussion en une page