Fill combobox with multiple columns

Re: Fill combobox with multiple columns

do you not just need to adjust the bound columns property of the combo box to be = to no. of columns you're filling it with?

if you're filling with VBA can you post your code.

Re: Fill combobox with multiple columns

This uses a recordset to populate but you could use data from a worksheet

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open conn 
rs.Open “qry_get_data “, cn


'POPULATE THE COMBO
Dim MyArray() As String
Dim I As Single
While Not rs.EOF
    z = z + 1
rs.MoveNext
Wend


ReDim MyArray(z, 2) [COLOR=Red]Change the 2 to the number of desired columns[/COLOR]
For I = 1 To z
'if first recordset then move to beginning of record
   If I = 1 Then
   rs.MoveFirst
   End If


'loads the values of the recordset to the array, you'll need to add two more
   MyArray(I, 0) = rs.Fields(0)
   MyArray(I, 1) = rs.Fields(1) [COLOR=Red]Add in the relevant number of fields here[/COLOR]
   rs.MoveNext
Next I


With Me.combobox1
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = "2.5 in; 0 in"
.List = MyArray
End With
rs.Close

Re: Fill combobox with multiple columns

You guys make this look so difficult!

Private Sub UserForm_Initialize()
    With ComboBox1
        .ColumnCount = 2
        .List = Range("ArrayList").Value
    End With
End Sub

Then just set the bound column to the one you want returned.

You don't even have to set the ColumnCount in code like this it's a property you can change in the properties window.

Re: Fill combobox with multiple columns

So where are you populating your combobox from. Aaron's solution is neat & quick, although I would use a Listbox.

Re: Fill combobox with multiple columns

Quote from Winnij

Thank you for your quick reply!

I guess Hogmaghub has the solution to my problem. The combo box is not filled with values of a range. I'll go and try it.

Werner

Range? Array? doesn't matter...

OK then here's the array version:

Private Sub UserForm_Initialize() 
    With ComboBox1 
        .ColumnCount = 2 
        .List = MyArray
    End With 
End Sub

Re: Fill combobox with multiple columns

This is the solution:

In one combobox with 2 columns, you need to put this code:

Do While Not recordset.EOF

combobox.AddItem (recordset.Fields("field1") & ";" & recordset.Fields("field2"))
recordset.MoveNext

Loop

The point is in the ";", used by vba for separing the different columns.

I hope this help you.