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]

I like your thought-process on the oLb.RowSource = vTemp.Address idea, but because vTemp doesn't actually have an address, it was doomed to fail. It's unfortunate that VBA doesn't allow us to setup a header to work with anything but the Rowsource property; which leaves us only with workarounds. The first of which is one that I use regularly: using labels above the listbox to act as headers. The second is outlined in red in the code below:

[/COLOR]Select Case YNE

Case "Either"
    Run "PopulateListBox", oLb
[COLOR=#ff0000]    oLb.ColumnHeads = True[/COLOR]
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
[COLOR=#ff0000]    oLb.ColumnHeads = False[/COLOR]
[COLOR=#ff0000]    oLb.AddItem "Header 1", 0[/COLOR]
[COLOR=#ff0000]    For x = 1 To 4[/COLOR]
[COLOR=#ff0000]        oLb.List(0, x) = "Header " & x + 1[/COLOR]
[COLOR=#ff0000]    Next x[/COLOR]
[COLOR=#ff0000]    oLb.ListIndex = 0[/COLOR]
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
[COLOR=#ff0000]    oLb.ColumnHeads = False[/COLOR]
[COLOR=#ff0000]    oLb.AddItem "Header 1", 0[/COLOR]
[COLOR=#ff0000]    For x = 1 To 4[/COLOR]
[COLOR=#ff0000]        oLb.List(0, x) = "Header " & x + 1[/COLOR]
[COLOR=#ff0000]    Next x[/COLOR]
[COLOR=#ff0000]    oLb.ListIndex = 0[/COLOR]

End Select[COLOR=#333333]

Regards,

CJ

 

[miami2k]

CJ honestly I only had time now to look properly at your reply.

I considered working with labels above the listbox but my commands will also change the visible columns and it sounds difficult (if not even impossible) to change the labels, depending the columns showing.

Your code in red returns me this thing that is not what I was looking for. But possibly it can be improved.
https://ibb.co/cdFG1S

Thank you!

 

[MrIfOnly]

Looking back on my code in response #11 , it's pretty generic and probably not all that useful but can be modified by spelling out the header of each column or referring back to the worksheet for the headers. Something like:

   oLb.ColumnHeads = False
    oLb.AddItem Cells(1, 1).Value, 0
    For x = 1 To 4
        oLb.List(0, x) = Cells(1, x + 1).Value
    Next x
    oLb.ListIndex = 0

Unlike actual listbox headers, or labels, though, these would be scrollable instead of static at the top of the listbox.

Also, depending on how you are changing the visible columns, these headers may not show up correctly either. Can you post the code you are using to do this? It may be possible to change the cell references (and even labels) to match them.

Regards,

CJ

 

[miami2k]

CJ sorry for the delay. I'm working on this prject in my free time.
I've created a sample file that is similar to what I'm trying to do.
Here is the file: http://jmp.sh/0N6bvAD
Thank you if you have time to give it a look.
My goal is to create checkboxes (or combo) that filter the results depending on the values of the colums.
If possible I'd like to keep the headers that now disappear when I check the box and also the formatting.

AN