Adding rows in a MultiColumn ListBox

Question

I'm messing around with VBA, especially with listboxes.

I've created a search button, that's using a find property (.Find) and searches whatever we've put in the textbox in it's data table.

It can add the search results to a listbox, but if we want the entire row from where search result is, how do we add it, instead of doing something like this vba listbox multicolumn add?
How can it be done, using VBA?
Is it even possible?
Else, is it possible without VBA?

What is happening:

Data table: a | b | c | d | e
            1 | 2 | 3 | 4 | 5
Search:   a 
Listbox : a

Search:   1 
Listbox : 1

Search:   2
Listbox : 2

Search:   d 
Listbox : d

What I "want":

Data table: a | b | c | d | e
            1 | 2 | 3 | 4 | 5

Search:  a
Listbox: a | b | c | d | e

Search:  1
Listbox: 1 | 2 | 3 | 4 | 5

Search:  2
Listbox: 1 | 2 | 3 | 4 | 5

Search:  d
Listbox: a | b | c | d | e

If it's needed I can provide the code I am using for testing the search.

score 2 · Accepted Answer · edited Nov 04 '15 at 13:41

The items in a multicolumn listbox can be accessed using the .List member. This member accepts two parameters as input:

ListBox1.List(RowIndex, ColumnIndex)

RowIndex: The row index of the record we want to access. Note the rows in a listbox are zero based. Therefor the index of the first row is 0.

ColumnIndex: The column index of the field we want to access. Note the columns in a multi column listbox are also zero indexed. Therefore the first column has an index of 0.

For example :

'Assign values to the columns 
For i = 1 To 9
    ListBox1.AddItem 'Create a new row
    ListBox1.List(i - 1, 0) = i 'Fill the first column
    ListBox1.List(i - 1, 1) = i * 10 
    ListBox1.List(i - 1, 2) = i * 100 
    ListBox1.List(i - 1, 3) = i * 1000 
Next i

Alternatively

but if we want the entire row from where search result is, how do we add it, instead of doing something like this vba listbox multicolumn add ?

Assuming you have a range object (e.g., rngFound) which represent the result of your .Find:

Me.ListBox1.Clear
Me.ListBox1.ColumnCount = rngFound.Columns.Count
Me.ListBox.RowSource = rngFound.Address

Good explanation. I edited it also to include the option for using the `RowSource` method as an alternative. — David Zemens, Nov 04 '15 at 13:45
@DavidZemens : thx for the edit, I forgot about `RowSource` as I haven't used it in few years! But nice completion and actual answer of the OP! ;) — R3uK, Nov 04 '15 at 14:18
I forget about it to because I do mainly PPT development anymore, I don't do much with Excel but I remember that one can be useful *if* your UserForm needs to be linked to the worksheet. The neat thing about that is then the ListBox will reflect changes to the worksheet, without having to explicitly "update" the `.List` property — David Zemens, Nov 04 '15 at 14:33

Adding rows in a MultiColumn ListBox

1 Answers1