View original (French)

Listbox without duplicates in a VBA Excel userform

Solved
wire less Posted messages 247 Registration date   Status Member Last intervention   -  
f894009 Posted messages 17417 Registration date   Status Member Last intervention   -
Good classic topic... sorry I've browsed the forums but I still can't create my list box without duplicates!!

My script... I have a ComboBox1 and I want that when I click on it, I populate my list box. My list box fills up... without having the same item twice!!
So I did this: 
Private Sub ComboBox1_Click()
 Dim Unique As New Collection
 Dim Value As Range

 Unique.Add ComboBox1.Value

 For Each Value In Unique
 Me.ListBox1.AddItem Value
 Next Value

There it is... simplistic... (yes well... it's what I found on the forums... it wasn't my idea... I'm adding the elements of my combobox into a "Collection", (apparently collections do not accept duplicates), then I transfer my collection into the list box.

And well... guess what... it doesn't work :'-(

Does anyone have an idea or a correction... or another solution?
Thanks.

3 answers

  1.
    VlkPr3s Posted messages 251 Status Member 130
     
    I did something like you for my C# application, and to avoid duplicates, you have to test the elements of the ListBox; basically, you compare the previous one with the next one, and if they are different, you keep it in there; otherwise, you remove one of the two occurrences.

    --
    Best regards,
    Pr3s.
    0
    1. wire less Posted messages 247 Registration date   Status Member Last intervention   5
       
      Arfff ... Ok ... it's just that with the "collections" it seemed easier, but I admit I don't really know how to use it!!
      I'm going to wait a bit longer, if no one has a solution, I will try your solution with loops that test each occurrence to see if they are duplicates :-/
      0
      1. VlkPr3s Posted messages 251 Status Member 130 > wire less Posted messages 247 Registration date   Status Member Last intervention  
         
        with a loop, that’s enough in my opinion ^^
        0
  2. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello to both of you, 

    Dim Unique As New Collection Dim i As Integer Dim Value As Variant Private Sub ComboBox1_Click() On Error Resume Next Unique.Add Item:=ComboBox1.Text, Key:=CStr(ComboBox1.ListIndex) On Error GoTo 0 'clear listbox ListBox1.Clear 'Loop through the contents of the collection to populate the ListBox For Each Value In Unique Me.ListBox1.AddItem Value Next Value End Sub
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Hello f89,

      small note
      if you instantiate using "new" and if you release control back to the system at the end of the macro (and only when using "new"), you must "destroy" the object at the end of the macro
      set unique = nothing
      Best regards
      0
      1. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717 > michel_m Posted messages 18903 Registration date   Status Contributor Last intervention  
         
        Hello,
        Indeed, I often forget it in "little codes," thank you.
        0
  3. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hello gentlemen,

    An example without a collection, just a small error handling: 
    Private Sub ComboBox1_Click() On Error GoTo fin Me.ListBox1 = ComboBox1 If ListBox1.ListIndex = -1 Then GoTo fin Exit Sub fin: Me.ListBox1.AddItem ComboBox1 End Sub


    --
    🎼 Best regards,
    Franck 🎶
    0