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

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?


Massimo ScolaAsked:
Who is Participating?
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Change the ColumnCount property of lstShop to 3 and replace existing codes on the form module with the following codes.

Private Sub UserForm_Initialize()
Call LoadShops(False)
End Sub

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
    LoadShops (True)
    LoadShops (False)
End If
End Sub

Sub LoadShops(Optional ShowAll As Boolean)
Dim ws As Worksheet
Dim tbl As ListObject
Dim x, y()
Dim i As Long, j As Long, n As Long

Set ws = Sheets("Shops")
Set tbl = ws.ListObjects("tblshops")
x = tbl.DataBodyRange
n = Application.CountIf(tbl.DataBodyRange.Columns(19), "True")
ReDim y(1 To n, 1 To 3)
If ShowAll = True Then
   With tbl.Range
      j = 0
      For i = 1 To UBound(x, 1)
         If x(i, 19) = "True" Then
            j = j + 1
            y(j, 1) = x(i, 1)
            y(j, 2) = x(i, 2)
            y(j, 3) = x(i, 3)
         End If
      Next i
      Me.lstShops.List = y
   End With
   x = tbl.DataBodyRange.Columns(1).Resize(, 3)
   Me.lstShops.List = x
End If
End Sub

Open in new window

For details, refer to the attached.
Roy CoxGroup Finance ManagerCommented:
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.
Glenn RayExcel VBA DeveloperCommented:
^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.
If it's not changed, you will only see the ID value.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Roy CoxConnect With a Mentor Group Finance ManagerCommented:
I've given some thought to your question and I believe this is what you are trying to do.

If the user checks the checkbox then only True entries are shown. I have written the code so that the option will show all true entries when the checkbox is not checked, but after checking the listbox will only show True entries. Note, the checkbox caption changes accordingly. The action is run using the checkbox click event.

When loading a ListBox I and many others find that the List Property is the best to use. The example currently loads the header row which might be an advantage, but can easily be removed if required. let me know and I'll amned thecode

To get only the True entries I use AdvancedFilter to a different sheet, which can be hidden. This will be faster than looping through the data to add the data. This will be noticeable the more data that is involved.

Note: also for simplicity and speed the code adds all the data to the Listbox, but only displays Columns 1,2, and 3.
Massimo ScolaAuthor Commented:
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
If x(i, 19) = True Then

from a string to Boolean value.

Thanks a lot for your help!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
Roy CoxGroup Finance ManagerCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.