Massimo Scola
asked on
VBA: Add rows to listbox based on criteria
I have a customer table with several columns. One column (19) states whether the customer is still active or not.
I would like to populate a listbox with columns 1, 2 and 3 based on a condition in column 19.
It is my first time that I'm populating a listbox with a condition.
Is there another way to populate the data?
Thanks
mscola
ListboxTable.xlsm
I would like to populate a listbox with columns 1, 2 and 3 based on a condition in column 19.
It is my first time that I'm populating a listbox with a condition.
Sub LoadShops(Optional ShowAll As Boolean)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Shops")
Dim cl As Range
Dim i As Integer
lstShops.Clear
If ShowAll = True Then
With lstShops
For Each cl In ws.Range("tblShops").Columns(3).Cells 'display shop name
If cl.Offset(, 19) = True Then
.List(i, 0) = cl.Offset(, -2).Value
.List(i, 1) = cl.Offset(, -1).Value
.List(i, 2) = cl.Value
End If
Next cl
i = i + 1
End With
End If
End Sub
Is there another way to populate the data?
Thanks
mscola
ListboxTable.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Roy_Cox: While your example works like a charm, I prefer not to write to a worksheet because my question is part of a bigger project and I already have many worksheets
Subodh Tiwari: I'm using your example code. I had to amend it a bit in order to make it work:
I had to change
If x(i, 19) = "True" Then
to
If x(i, 19) = True Then
from a string to Boolean value.
Thanks a lot for your help!
Subodh Tiwari: I'm using your example code. I had to amend it a bit in order to make it work:
I had to change
If x(i, 19) = "True" Then
to
If x(i, 19) = True Then
from a string to Boolean value.
Thanks a lot for your help!
You're welcome. Glad to help.
Writing to a worksheet is far more efficient than a loop, but it's your choice.
Also, loading the data to the ListBox piece meal is also more efficiernt than adding row by row. Even using 22 columns of data.
Also, loading the data to the ListBox piece meal is also more efficiernt than adding row by row. Even using 22 columns of data.
I'll help you adapt it further if you want to use this method.
ListboxTable.xlsm