VBA: How to fill two comboboxes without duplicate items

[nabila]

Hi, I have two comboboxes and want to fill with details from column f and column K from my database without duplicate items. The database will be updated daily. Basically this is what I've done but the comboboxes have no output when I run the program. Can somebody help me?



Private Sub UserForm_Click()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("F:K"))
For a = 6 To 11
If Sheet1.Cells(1, a).Value = Me("Label" & a).Caption Then
Me("combobox" & a).AddItem Sheet1.Cells(i, a).Value
End If
Next a
Next i
End Sub

 

[ranman256]

in the example, I load all values into an indexed collection.
this prevents duplicates.

Private Sub UserForm_Click()
Dim i As Long, a As Long
Dim col As New Collection
Dim vItm


On Error Resume Next


  'collect all cells into collection (unique only)
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("F:K"))
   For a = 6 To 11
        If InStr(Sheet1.Cells(1, a).Value, "Label") > 0 Then
           vItm = Sheet1.Cells(i, a).Value
           col.Add vItm, vItm
        End If
   Next a
Next i


   'load the combo w unique values
For i = 1 To col.Count
   'Me("combobox" & a).AddItem col(i)
   Debug.Print col(i)
Next

set col = nothing
End Sub

 

[Norie]

The code should go in the UserForm_Initialize event.

 

[Akuini]

Hi, I have two comboboxes and want to fill with details from column f and column K from my database without duplicate items. The database will be updated daily. Basically this is what I've done but the comboboxes have no output when I run the program. Can somebody help me?

Here's another example on how to populate unique values from a range to a combobox.

Private Sub UserForm_Initialize()
Dim d As Object, va, i As Long
Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare
va = Sheets("Sheet1").Range("F2", Cells(Rows.Count, "F").End(xlUp))
    For i = 1 To UBound(va, 1)
        d(va(i, 1)) = ""
    Next
ComboBox1.List = d.keys

End Sub