Log In
Register
Home
November 19, 2018
Offline
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
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
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.
Answers Post
November 19, 2018
Offline
thanks for your reply. I have done as you suggest but it doesn't work. run time error 424 object required.
Trusted Members
Moderators
November 1, 2018
Offline
On which line? You will need to amend Combobox2 in the code to whatever the name of the second combobox is of course.
1 Guest(s)
