CODEWITHSUNDEEP
vbaexcel
Goos van den Bekerom
Goos van den Bekerom

Reputation: 1493

Excel VBA - Loop for populating ComboBox that doesn't add duplicates

I'm populating a ComboBox on a userform with a loop it adds all the "names" in the first column of a table, that have the same "type" in the second column.

I don't want to go into detail, but its possible that the same "name" occurs multiple times. I do not want the loop to add those duplicate values though.

I found a few solutions on other forums, but those looked very outdated to me and I feel like this should have an easy fix. (These "outdated" solutions are like 30+ sentence codes, I don't feel like i need that)

Can anyone help me further with this?

This is the populating loop:

With resourceSheet.ListObjects("Table3")
    For x = 2 To .ListRows.Count + 1
        If .Range(x, 2) = cType Then

            'cbname is the combobox
            cbName.AddItem .Range(x, 1)

        End If
    Next x
End With

Upvotes: 4

Views: 3277

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

These "outdated" solutions are like 30+ sentence codes, I don't feel like i need that

Though you already have an answer, here is another option using collections

Sub Sample()
    Dim Col As New Collection, itm As Variant

    With resourceSheet.ListObjects("Table3")
        For x = 2 To .ListRows.Count + 1
            If .Range(x, 2) = cType Then
                On Error Resume Next
                Col.Add .Range(x, 2).Value, CStr(.Range(x, 2).Value)
                On Error GoTo 0
            End If
        Next x
    End With

    For Each itm In Col
        cbName.AddItem itm
    Next
End Sub

Upvotes: 3

Dave
Dave

Reputation: 1643

Try this:

' Create Dictionary object
Dim obj As Object
Set obj = CreateObject("Scripting.Dictionary")

With resourceSheet.ListObjects("Table3")
    For x = 2 To .ListRows.Count + 1
        If .Range(x, 2) = cType Then

        ' If name doesn't exist in the Dictionary object yet, add the name to the listbox and the Dictionary object
            If IsEmpty(obj.Item(.Range(x, 1) & "")) Then

                'cbname is the combobox
                cbName.AddItem .Range(x, 1)
                obj.Item(.Range(x, 1) & "") = .Range(x, 1)
            End If

        End If
    Next x
End With

The dictionary object allows you to use the name as a key. If the key doesn't exist, it adds the name to the listbox and adds the key. Next time it comes across the same name, that key already exists so it can move on to the next line.

Upvotes: 3

Related Questions