Solved

VBA: Add rows to listbox based on criteria

Posted on 2016-08-22
7
62 Views
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
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
0
Comment
Question by:mscola
7 Comments
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
Comment Utility
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)
Else
    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)
lstShops.Clear
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
Else
   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.
ListboxTable.xlsm
2
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
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
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
^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.
columncount.jpg
If it's not changed, you will only see the ID value.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 250 total points
Comment Utility
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.
ListboxTable.xlsm
0
 

Author Comment

by:mscola
Comment Utility
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!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome. Glad to help.
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
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.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now