VBA: How to fill two comboboxes without duplicate items

nabila

New Member
Joined
Nov 13, 2018
Messages
1
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

Well-known Member
Joined
Jun 17, 2014
Messages
1,807
in the example, I load all values into an indexed collection.
this prevents duplicates.

Code:
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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,361
Office Version
365
Platform
Windows
The code should go in the UserForm_Initialize event.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,339
Office Version
2013
Platform
Windows
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.

Code:
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
 
Last edited:

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top