Solved

VBA: Add rows to listbox based on criteria

Posted on 2016-08-22
7
74 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
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 17

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 17

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

Author Comment

by:mscola
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 28

Expert Comment

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Advice in Xamarin 21 56
selection of current record jumps to a non-selected record 8 36
send keys not working in vba 7 41
excel help 4 18
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

920 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

13 Experts available now in Live!

Get 1:1 Help Now