CODEWITHSUNDEEP
vbaexcel
svacx
svacx

Reputation: 357

Adding rows in a MultiColumn ListBox

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.

Upvotes: 2

Views: 13415

Answers (1)

R3uK
R3uK

Reputation: 14537

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

Upvotes: 2

Related Questions