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.

David Zemens · Accepted Answer · 2015-11-04 13:41:39Z

2

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

edited Nov 4, 2015 at 13:41

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

answered Nov 4, 2015 at 13:25

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Zemens Over a year ago

Good explanation. I edited it also to include the option for using the RowSource method as an alternative.

R3uK Over a year ago

@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! ;)

David Zemens Over a year ago

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

Collectives™ on Stack Overflow

Adding rows in a MultiColumn ListBox

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related