Populating multi column combobox

[TPFKAS]

Hi all,
I am looking for a VBA code to populate a two column combobox using data from two columns of data on different locations in a spreadsheet.
Any idea how to do this?
Thanks.

 

[mikerickson]

I'd use the .AddItem method and .List property.

Private Sub UserForm_Initialize()
    Dim i As Long
    With ComboBox1
        .ColumnCount = 2
        For i = 1 To 10
            .AddItem Range("A1:A10").Cells(i, 1).Value
            .List(.ListCount - 1, 1) = Range("D1:D10").Cells(i, 1).Value
        Next i
    End With
End Sub

 

[diddi]

just thinking about your code mike. i used this method a lot but found it slow with large lists. i tried using a temp array to load the data and manipulate anything if required and then

    Combobox1.list = MyTempArray

and it turned out to be a considerable time saver

 

[mikerickson]

I agree about the time, unfortunately my Excel2004 is not very stable and that approach often crashes Excel for me.

 

[TPFKAS]

Thanks Mike. That did the trick.
Two more question:
1. I use this combobox and I would like that the two values also appear in the field once I selected the one I want. I now only see the first value.
2. I have got fmMatchEntryComplete selected and this works great. When I type in a character that matches the first character of one of the entries in the first column it appears. Can it be done that the second colum will then also show the corresponding value?

 

[mikerickson]

re: 1)
The .TextColumn property of the Combobox determine which column's entry is shown in the box. But it can only be one column. A workaround might be to add a third (not visible) column to the list and set the .TextColumn to 3.

Private Sub UserForm_Initialize()
    Dim i As Long
    With ComboBox1
        .ColumnCount = 3
        .ColumnWidths = ";;0"
        .TextColumn = 3
        
        For i = 1 To 10
            .AddItem Range("A1:A10").Cells(i, 1).Value
            .List(.ListCount - 1, 1) = Range("D1:D10").Cells(i, 1).Value
            .List(.ListCount - 1, 2) = .List(.ListCount - 1, 0) & vbTab & .List(.ListCount - 1, 1)
        Next i
        
    End With
End Sub

re:2)
Similarly, the value that is matched against the typing is controlled by the .BoundColumn property. But it can only be one column. Sorry.

 

[TPFKAS]

Hi Mike,

I am still trying to understand this code, but anyhow its works exactly as I want it on both quesion 1 and 2.

Thank you very much. I much appreciate your help!

Best regards from Holland

Tom Kas

 

[vllgv]

For this userform, what would you put in the combobox code?

 

[mikerickson]

This thread is about how one loads the combobox. Loading is done in the Userform_Initialize event typically, not in the combobox code.
The code in the combobox deals with what happens when the user selects an item from the box, not with how the list entries are put in the box.

 

[bhavinshah1086]

Hello Mike,

First let me thank you for providing lots of answers over various queries raised by many people. I have been active visitor of site for various problem statements i get while working on Excel / Excel VBA.

In this thread, I also use Combo box to display two columns data. However, I am using MS Access to fetch data from table and showing in Combo box. My query is about header in combo box. How do I set header for combo box when continuous range is not used from worksheet in data set/row source.

once again Thanks alot.

Regards
Bhavin.