CODEWITHSUNDEEP
excelvbacombobox
scb998
scb998

Reputation: 909

remove duplicate items in a cascading combo box VBA

so I have a series of cascading combo boxes, which are populated dependant on the value of the first box as follows:

ID(pre - populated) > Licence number > State > Name

I am populating the comboboxes using the following bit of code:

If cmb_Project.ListIndex <> -1 Then
            cmb_State.Clear
           strSelected = cmb_Licence.Value

           LastRow = Worksheets("Entitlement Owners").Range("A" & Rows.Count).End(xlUp).row

           Set rngList = Worksheets("Entitlement Owners").Range("c2:c" & LastRow)
           Set rngCompany = Worksheets("Entitlement Owners").Range("b2:b" & LastRow)

           For Each rngCompany In rngList

                 If rngCompany.Value = strSelected Then

                      cmb_State.AddItem rngCompany.Offset(, -1)

                 End If

           Next rngCompany

      End If

below is a sample layout of the data I have

001      SA 763 Name 1
002     SA  547 Name 2
004     SA  2250    Name 3
004     SA  2250    Name 4

So in Combobox 1, I have: 001,002,003,004 (which is correct)

when I select 004 in the first box, the second box populates with: SA,SA which is not what I want.

I have been trying to follow the following example which allegedly will remove duplicates, but doesn't allow for the comparison I'm doing.

does anybody know how I can remove the duplicates from the search range?

Upvotes: 1

Views: 579

Answers (1)

A.S.H
A.S.H

Reputation: 29352

To remove duplicates when populating a list, you can use a dictionary, then set the combo's list property to the dictionary's keys. i.e.

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
For Each rngCompany In rngList
    If rngCompany.value = strSelected Then dict(rngCompany.Offset(, -1).text) = 0 ' add key to dict
Next rngCompany
cmb_State.List = dict.Keys

Upvotes: 2

Related Questions