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 have. Only one combobox filled with data from column f without duplicate. Anyone can help me how to fill the second combobox?
Thank you
Private Sub UserForm_Initialize()
Dim d As Object, va, i As Long
Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare
va = Sheets("DB").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
Simple option is to just to duplicate what you do for the first combobox, like this:
Private Sub UserForm_Initialize()
Dim d As Object, va, i As Long
Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare
va = Sheets("DB").Range("F2", Cells(Rows.Count, "F").End(xlUp))
For i = 1 To UBound(va, 1)
d(va(i, 1)) = ""
Next
ComboBox1.List = d.keys
d.RemoveAll
va = Sheets("DB").Range("K2", Cells(Rows.Count, "K").End(xlUp))
For i = 1 To UBound(va, 1)
d(va(i, 1)) = ""
Next
ComboBox2.List = d.keys
End Sub
If the two ranges should have the same number of rows, I'd suggest using two dictionaries so you only have to loop once, while populating both dictionaries.
thanks for your reply. I have done as you suggest but it doesn't work. run time error 424 object required.
On which line? You will need to amend Combobox2 in the code to whatever the name of the second combobox is of course.
I just realize the name of second combobox is not Combobox2 but Combobox3. It works now. Thank you so much Velouria. It helps a lot
You're welcome.
