Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA: Add rows to listbox based on criteria

Posted on 2016-08-22
7
Medium Priority
?
472 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:Massimo Scola
7 Comments
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 1000 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)
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 22

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.
ListboxTable.xlsm
0
 
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.
columncount.jpg
If it's not changed, you will only see the ID value.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Assisted Solution

by:Roy Cox
Roy Cox earned 1000 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.
ListboxTable.xlsm
0
 

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
to
If x(i, 19) = True Then

from a string to Boolean value.

Thanks a lot for your help!
0
 
LVL 33

Expert Comment

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

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

885 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