XL 2010 Alimenter ComboBox multi-colonne sans doublons

[modus57]

J'ai trouvé une discussion sur ce forum concernant ce sujet.
Le code d'Hervé XLDnaute Barbatruc fonctionne bien sauf que les données dans le ComboBox sont juxtaposées et non pas alignées.
Voici mon code légèrement modifié :

Private Sub UserForm_Initialize()
Dim Coll As New Collection
Dim Cellule As Range
With Me.ComboBox1
.ColumnCount = 4
.ColumnWidths = ("45;50;50;120")
End With
With Sheets("BDD")
For Each Cellule In .Range("Tableau1")
On Error Resume Next
Coll.Add Cellule, CStr(Cellule)
If Err = 0 Then
Me.ComboBox1.AddItem (CStr(Cellule))
End If
On Error GoTo 0
Next Cellule
End With
End Sub

Comment le corriger pour des données alignées dans le ComboBox.
Merci d'avance pour votre aide.

 

[laurent3372]

Peux-tu expliquer ce que tu entends par "alignées" par opposition à "juxtaposées" ?

 

[modus57]

Bonjour Laurent,
Merci de vous intéresser à cette discussion.
Voici une copie d'écran de l'UserForm :

 

[patricktoulon]

bonjour
un petit fichier en exemple peut être non ?
c'est bien beau de montrer une capture mais le résultat souhaité c'est quoi ???
a nous les données ne parlent on est pas censés savoir l'ordre des données

 

[modus57]

Bonjour patricktoulon ,
Voici le fichier.

 

[Rhysand]

Bonjour à tous

Je laisse ici un exemple de comment remplir la liste, comment la mettre en ordre croissant et décroissant, et comment supprimer les doublons

exemple avec feuille avec le nom "Feuille1", liste déroulante avec le nom "ComboBox5"

Private Sub ajouterElementsCombobox5() ' ajouter des éléments à la liste déroulante (5)

Me.ComboBox5.Clear

Dim ligne As Integer, col As Integer
Dim ini, fim As Integer
Dim i, j As Integer
Dim xplus As String
Dim xmoins As String

ligne = 6
col = 4

'* ajouter des éléments de données de feuille à la liste déroulante
While Application.ThisWorkbook.Worksheets("Feuille1").Cells(ligne, col) <> ""
    Me.ComboBox5.AddItem (Application.ThisWorkbook.Worksheets("Feuille1").Cells(ligne, col))
    ligne = ligne + 1
Wend

ini = 0
fim = Me.ComboBox5.ListCount - 1

'* lister les éléments par ordre croissant ou décroissant
For i = ini To fim - 1
    For j = i + 1 To fim
'--------------------------------------------------------------------
'--------------------------------------------------------------------
' descending mode
'--------------------------------------------------------------------
'        If Me.ComboBox5.List(i) < Me.ComboBox5.List(j) Then
'            xplus = Me.ComboBox5.List(j)
'            Me.ComboBox5.List(j) = Me.ComboBox5.List(i)
'            Me.ComboBox5.List(i) = xplus
'--------------------------------------------------------------------
'--------------------------------------------------------------------
' ascending mode
'--------------------------------------------------------------------
        If Me.ComboBox5.List(i) > Me.ComboBox5.List(j) Then
            xmoins = Me.ComboBox5.List(j)
            Me.ComboBox5.List(j) = Me.ComboBox5.List(i)
            Me.ComboBox5.List(i) = xmoins
'--------------------------------------------------------------------
'--------------------------------------------------------------------
        End If
    Next j
Next i

'* supprimer les doublons dans la liste déroulante (si un nom d'élément est répété)
For i = 0 To Me.ComboBox5.ListCount - 2
    For j = Me.ComboBox5.ListCount - 1 To i + 1 Step -1
        If Me.ComboBox5.List(i) = Me.ComboBox5.List(j) Then 'répété
            Me.ComboBox5.RemoveItem (j)
        End If
    Next j
Next i

End Sub

Private Sub UserForm_Activate()

Call ajouterElementsCombobox5   ' ajouter des éléments à la liste déroulante (5)

End Sub

J'espère aider

 

[patricktoulon]

bonjour @Rhysand
sur une liste de plus de 500 ton truc est interminable c'est une méthode qui marche mais il y a beaucoup moins lourd

 

[Dranreb]

Bonjour.
Comme par exemple :

Private Sub UserForm_Initialize()
    Dim Coll As New Collection
    Dim Cellule As Range
    With Me.ComboBox1
        .ColumnCount = 4
        .ColumnWidths = ("44;50;49;126")
    End With
    ComboBox1.List = Feuil1.[Tableau1].Value
    End Sub

Mais je vous conseillerais plutôt 4 ComboBox à une seule colonne prises en charge par un objet ComboBoxLiées

ComboBox liées et contrôles associés

Définit deux types d'objets à évènements: ComboBoxLiées prenant en charge les ComboBox pour la saisie des données d'identification de la ligne à créer, puis, ultérieurement, à consulter voire modifier, et ControlsAssociés prenant en charge les...

www.excel-downloads.com

 

[patricktoulon]

re
j'ai deja montrer plusieur fois ces dernières semaine comment remplir une combo ou listbox sans doublons sans collection et sans dico sans utiliser la combo et son index si value
c'est pas compliqué

Private Sub UserForm_Activate()
    tabl = Sheets("BDD").ListObjects("Tableau1").DataBodyRange.Value ' on prend tout le databodyrange du listobject("tableau")dans une variable tableau
    tablnom = Application.Index(tabl, 0, 4) ' on cré un tableau avec la colonnes des noms
     With ComboBox1
        .ColumnCount = UBound(tabl): .ColumnWidths = ("45;55;25;120") 'parametrage combo
        For i = 1 To UBound(tablnom) 'on boucle sur les noms si x=i alors il n'y est pas encore  on le met sinon non
            x = Application.IfError(Application.Match(tablnom(i, 1), tablnom, 0), 0)
            If i = x Then ComboBox1.AddItem "": For c = 1 To UBound(tabl, 2): .List(.ListCount - 1, c - 1) = tabl(i, c): Next
        Next
    End With
End Sub

 

[Dranreb]

Ceci a marché du 1er coup avec 4 ComboBox convenablement nommées :

Option Explicit
Private WithEvents CL As CLsCAs.ComboBoxLiées
Private Sub UserForm_Initialize()
   Set CL = Création.ComboBoxLiées: CL.Plage Feuil1
   CL.Add Me.CBxN°OP, "N° OP"
   CL.Add Me.CBxDateOP, "Date OP"
   CL.Add Me.CBxIdClient, "ID client"
   CL.Add Me.CBxNomClient, "Nom client"
   CL.CouleurSympa
   CL.Actualiser
   End Sub

 

[patricktoulon]

re
allez j'en rajoute une
si vous avez envie de changer l'ordre des colonnes ou même en enlever
la aussi c'est simple
ajouter 2 lignes au code précédent
par exemple ici on va mettre( id - nom - date - OP)

Private Sub UserForm_Activate()
    tabl = Sheets("BDD").ListObjects("Tableau1").DataBodyRange.Value    ' on prend tout le databodyrange du listobject("tableau")dans une variable tableau
    tablnom = Application.Index(tabl, 0, 4)    ' on cré un tableau avec la colonnes des noms
'*************************
    arrcolumns = Array(3, 4, 2, 1)    'mettre les colonnes que l'on veut dans l'ordre que l'on veut
    tabl = Application.Index(tabl, Evaluate("ROW(" & 1 & ":" & UBound(tabl) & ")"), arrcolumns)
'*************************
    With ComboBox1
        .ColumnCount = UBound(tabl): .ColumnWidths = ("45;55;25;120")    'parametrage combo
        For i = 1 To UBound(tablnom)    'on boucle sur les noms si x=i alors il n'y est pas encore  on le met sinon non
            x = Application.IfError(Application.Match(tablnom(i, 1), tablnom, 0), 0)
            If i = x Then ComboBox1.AddItem "": For c = 1 To UBound(tabl, 2): .List(.ListCount - 1, c - 1) = tabl(i, c): Next
        Next
    End With
End Sub

 

[Dranreb]

Et comme j'ai vu des doublons sur la combinaison de l'ensemble de ComboBox, pour en afficher le détail dans une ListBox :

Option Explicit
Private WithEvents CL As CLsCAs.ComboBoxLiées
Private Sub UserForm_Initialize()
   Set CL = Création.ComboBoxLiées: CL.Plage Feuil1
   CL.Add Me.CBxN°OP, "N° OP"
   CL.Add Me.CBxDateOP, "Date OP"
   CL.Add Me.CBxIdClient, "ID client", "000"
   CL.Add Me.CBxNomClient, "Nom client"
   CL.CouleurSympa
   CL.Actualiser
   End Sub
Private Sub CL_Change(ByVal Complet As Boolean, ByVal NbrLgn As Long)
   If NbrLgn = 0 Then LBxDétails.Clear
   End Sub
Private Sub CL_Bingo(Lignes() As Long)
   Dim TDon(), LDon As Long, TLBx(), LLBx As Long, C As Long
   TDon = CL.PlgTablo.Value
   Me.LBxDétails.ColumnCount = UBound(TDon, 2)
   ReDim TLBx(1 To UBound(Lignes, 1), 1 To UBound(TDon, 2))
   For LLBx = 1 To UBound(Lignes)
      LDon = Lignes(LLBx)
      For C = 1 To UBound(TLBx, 2):
         TLBx(LLBx, C) = TDon(LDon, C)
         Next C, LLBx
   LBxDétails.List = TLBx
   End Sub

Elle affiche parfois des lignes identiques, bien sûr, puisqu'aucune autre colonne ne les distingue dans l'exemple.

 

[modus57]

Une question à patricktoulon
Comment formaté la 3ème colonne du ComboBox "000"
Merci @+

 

[patricktoulon]

re
j'oubliais
le coloumnwidth aussi dans le même ordre que celui choisi dans arrcolumns
bien entendu j'ai pris les largeur des colonnes des colonnes de la plage mais il peut en etre autrement

Private Sub UserForm_Activate()
    Dim Table As Range, ColWidth(), CW$, Tabl, tablNom, C&
    Set Table = Sheets("BDD").ListObjects("Tableau1").DataBodyRange
    Tabl = Table.Value    ' on prend tout le databodyrange du listobject("tableau")dans une variable tableau
    tablNom = Application.Index(Tabl, 0, 4)    ' on cré un tableau avec la colonnes des noms
    '*************************
    arrcolumns = Array(3, 4, 2, 1)    'mettre les colonnes que l'on veut dans l'ordre que l'on veut
    Tabl = Application.Index(Tabl, Evaluate("ROW(" & 1 & ":" & UBound(Tabl) & ")"), arrcolumns)
    ReDim ColWidth(1 To Table.Columns.Count)
    For C = 1 To UBound(ColWidth): ColWidth(C) = Table.Cells(1, C).Width: Next    'récolte des width colonne
    ColWidth = Application.Index(ColWidth, Evaluate("ROW(" & 1 & ":" & 1 & ")"), arrcolumns)    'dans le meme ordre que tabl
    '*************************
    With ComboBox1
        .ColumnCount = UBound(Tabl): .ColumnWidths = Join(ColWidth, ";")   'parametrage combo
        For i = 1 To UBound(tablNom)    'on boucle sur les noms si x=i alors il n'y est pas encore  on le met sinon non
            x = Application.IfError(Application.Match(tablNom(i, 1), tablNom, 0), 0)
            If i = x Then ComboBox1.AddItem "": For C = 1 To UBound(Tabl, 2): .List(.ListCount - 1, C - 1) = Tabl(i, C): Next
        Next
    End With
End Sub

 

[patricktoulon]

Une question à patricktoulon
Comment formaté la 3ème colonne du ComboBox "000"
Merci @+

dans cet exemple comme j'ai mis le id en colonne 0(la premiere de la combo) ce sera 1 sinon ca serait 3

Private Sub UserForm_Activate()
    Dim Table As Range, ColWidth(), CW$, Tabl, tablNom, C&
    Set Table = Sheets("BDD").ListObjects("Tableau1").DataBodyRange
    Tabl = Table.Value    ' on prend tout le databodyrange du listobject("tableau")dans une variable tableau
    tablNom = Application.Index(Tabl, 0, 4)    ' on cré un tableau avec la colonnes des noms
    '*************************
    arrcolumns = Array(3, 4, 2, 1)    'mettre les colonnes que l'on veut dans l'ordre que l'on veut
    Tabl = Application.Index(Tabl, Evaluate("ROW(" & 1 & ":" & UBound(Tabl) & ")"), arrcolumns)
    ReDim ColWidth(1 To Table.Columns.Count)
    For C = 1 To UBound(ColWidth): ColWidth(C) = Table.Cells(1, C).Width: Next    'récolte des width colonne
    ColWidth = Application.Index(ColWidth, Evaluate("ROW(" & 1 & ":" & 1 & ")"), arrcolumns)    'dans le meme ordre que tabl
    '*************************
    With ComboBox1
        .ColumnCount = UBound(Tabl): .ColumnWidths = Join(ColWidth, ";")   'parametrage combo
        For i = 1 To UBound(tablNom)    'on boucle sur les noms si x=i alors il n'y est pas encore  on le met sinon non
            x = Application.IfError(Application.Match(tablNom(i, 1), tablNom, 0), 0)
            If i = x Then ComboBox1.AddItem ""
            For C = 1 To UBound(Tabl, 2): .List(.ListCount - 1, C - 1) = Format(Tabl(i, C), IIf(C = 1, "000", "@")): Next
        Next
    End With
End Sub