VBA: Add rows to listbox based on criteria

Posted on 2016-08-22
Last Modified: 2016-08-23
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?


Question by:Massimo Scola
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 31

Accepted Solution

Subodh Tiwari (Neeraj) earned 250 total points
ID: 41765662
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.
LVL 20

Expert Comment

by:Roy Cox
ID: 41765678
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.
LVL 27

Expert Comment

by:Glenn Ray
ID: 41765776
^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.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

LVL 20

Assisted Solution

by:Roy Cox
Roy Cox earned 250 total points
ID: 41766373
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.

Author Comment

by:Massimo Scola
ID: 41766557
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!
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41766842
You're welcome. Glad to help.
LVL 20

Expert Comment

by:Roy Cox
ID: 41767251
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.

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question