Link to home
Start Free TrialLog in
Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland

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.

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

Open in new window


Is there another way to populate the data?

Thanks

mscola
ListboxTable.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Have a look at this and see if it is what you want. It basically exports all the True data to another sheet then loads the ListBox.

I'll help you adapt it further if you want to use this method.
ListboxTable.xlsm
^No points, but adding to Subodh Tiwari's post.  To change the ColumnCount property for the lstShops ListBox, select the ListBox object, then go to Properties window (bottom left) and change the ColumnCount from 1 to 3.
User generated image
If it's not changed, you will only see the ID value.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Massimo Scola

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!
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.