multicolumn listbox

[poultercm]

Hi,

I have a small application which reads the contents of a spreadsheet and then writes the contents of each row to a listbox. The listbox has 5 columns.

I have dropdown boxes in the application that I wish to use in order to filter what gets displayed in the Listbox. For example, one column of data in the Listbox will only include the values "Yes" or "No" and I have a drop down box which is used to select Yes or No. If "Yes is selected I only want the rows which contain "Yes" to be displayed in the Listbox.

The code snippet which I use to populate the list box is shown below

    For i = 1 To LastUsedRowNewStock - 1            ListBox1.AddItem newStockListArray(i, 2) 'Serial number        ListBox1.List(i - 1, 1) = newStockListArray(i, 3) 'Type        ListBox1.List(i - 1, 2) = newStockListArray(i, 5) 'DC switch Yes or No        ListBox1.List(i - 1, 3) = newStockListArray(i, 4) 'Communication        ListBox1.List(i - 1, 4) = newStockListArray(i, 1) 'Pallet number            Next

Please can someone advise how I can achieve displaying filtered rows please?

Thank you

 

[MrIfOnly]

You could use the autofilter property to filter the worksheet depending on the value of your combobox, then bring the filtered results to the listbox.

The filter code would be something like this (with specifics like worksheet, column, and combobox name changed):

Worksheets("Sheet1").Range("A1").AutoFilter Field:=3, Criteria1:=combobox1.value, VisibleDropDown:=False

Regards,

CJ

 

[poultercm]

Hi MrIfOnly,

Thank you for your reply -I will try what you have suggested.

What you seem to be saying is work on the data from the spreadsheet and not in on the data held in memory by the ListBox object.

Is there a way to operate on the data already in the Object ListBox?

Thank you

 

[MrIfOnly]

Is there a way to operate on the data already in the Object ListBox?

My first inclination is to says 'no - it can't be done', but the more I think about it, the more I think there may be a way. I'll tinker with it and let you know what I find, unless someone else here has something already in their toolbox for this.

Regards,

CJ

 

[poultercm]

Hi MrIfOnly,

Thanks for you reply again.

I have used the code snippet you provided and can see that the excel sheet gets filtered now, but I don't know how to copy the filtered or visible rows of data into my Listbox. Please could you advise?

As you may have guessed I'm a beginner with VBA but do have some very rusty software background.

Thank you

 

[MrIfOnly]

Actually, I came up with the following solution that I think will be much faster than my original suggestion of filtering the worksheet contents and, at the same time, answers your question of "Is there a way to operate on the data already in the Object ListBox?" with a resounding "Yes!"

Before I get into it, I need to give a shout-out to RoyUK from whom I cannibalized the code that he had posted in post #3 here.

The first thing I need you to do is to make a copy of your workbook and work with the copy until you're sure this solution will work for you.

Next, cut and paste your code that you are using to populate your listbox into a standard module (not the Userform module) in your workbook. You will have to replace any reference to the listbox with oLb. See my code below for an example (I am using the rowsource property to populate the listbox with dummy data, but you can still use the method you posted in your O.P.).

Sub PopulateListBox(oLb As MSForms.ListBox)
 oLb.RowSource = "'Sheet1'!A1: E26"
End Sub

Next, paste the following code into a standard module also:

Sub filterResults(oLb As MSForms.ListBox, fCol As Integer, YNE As String)
    Dim vaItems As Variant
    Dim i As Long, j As Long
    Dim c      As Integer
    Dim vTemp() As Variant
    fCol = fCol - 1
    
    ReDim vTemp(oLb.ListCount, oLb.ColumnCount - 1)
Select Case YNE
Case "Either"
    Run "PopulateListBox", oLb
Case "Yes" 'filter for Yes
    Run "PopulateListBox", oLb
    vaItems = oLb.List 'Put the items in a variant array
    oLb.RowSource = "" 'remove if rowsource property is not being used
    For i = LBound(vaItems, 1) To UBound(vaItems, 1)
            If LCase(vaItems(i, fCol)) = "yes" Then
                For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
                    vTemp(j, c) = vaItems(i, c)
                Next c
                j = j + 1
            End If
    Next i
    oLb.List = vTemp
    
Case "No" 'filter for No
    Run "PopulateListBox", oLb
    vaItems = oLb.List 'Put the items in a variant array
    oLb.RowSource = "" 'remove if rowsource property is not being used
    For i = LBound(vaItems, 1) To UBound(vaItems, 1)
            If LCase(vaItems(i, fCol)) = "no" Then
                For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
                    vTemp(j, c) = vaItems(i, c)
                Next c
                j = j + 1
            End If
    Next i
    oLb.List = vTemp
        
End Select
End Sub

I used 3 Option Buttons on my userform labeled with Yes, No, and Either and named YesOpt, NoOpt, and EitherOpt. I thought this worked pretty slick, but you can still use a combobox with the three options instead with a minor change to the next code. In your userform module paste the following:

Private Sub Userform_initialize()
    Run "PopulateListBox", Me.ListBox1
    EitherOpt.Value = True
End Sub
Private Sub NoOpt_Click()
    Run "filterResults", Me.ListBox1, 5, "No"
End Sub
Private Sub YesOpt_Click()
    Run "filterResults", Me.ListBox1, 5, "Yes"
End Sub
Private Sub EitherOpt_Click()
    Run "filterResults", Me.ListBox1, 5, "Either"
End Sub

Finally, change Listbox1 in the above code to whatever name you end up using for your listbox, and change the '5' to match whichever column in the listbox the yes or no is in.

Let me know if I wasn't clear on anything and please let me know how it works out for you.

Regards,

CJ

 

[poultercm]

Hi CJ,

Thank you for a very comprehensive answer I will try this over the next few days and let you know.

Thanks again

 

[poultercm]

Hi CJ,

Thank so much - this works a treat!

You have been very helpful and I would just like to thank you for you valuable knowledge.

Have a great day

 

[MrIfOnly]

You are very welcome! Thanks for the feedback.

Regards,

CJ

 

[miami2k]

CJ, when I run the function with option either the list is populated with headers when i activate any other option I lose the headers line.
Any suggestion?
I've tried to replace
oLb.List = vTemp
with
oLb.RowSource = vTemp.Address
but is not working.