Filtering a listbox based on a column

[davedave24]

Hi, I'm trying to filter a listbox based on a specific column. It's a 2 column listbox, loaded from a range of 3 columns. Column 3 is the category (let's say A, B, C, D etc.)

How would I approach filtering/displaying by category? eg. click on the Category A button, and only category A items are shown.

 

[Andrzejek]

How do you populate your listbox?

if you use a data source, you may want to try:
[tt]
Select * from MyTable
Where category = 'A'[/tt]

Have fun.

---- Andy

 

[davedave24]

Sorry probably not enough info from me

It's filled from an excel sheet

    Dim rngS As Range
    With Sheets("ProductList")
        Set rngS = .Range(.Range("A3"), .Cells(Rows.count, 1).End(xlUp)).Resize(, 2)
    End With
    
    'add the customer array to the combobox
    listProducts.RowSource = rngS.Address(External:=True)

So column A is the product code, column B is the product name, column C is the category (the listbox only displays the first 2 columns)

 

[davedave24]

This is where I'm at:

    'clear the products listbox
    listProducts.RowSource = ""
    
    Dim c As Range
    For Each c In Intersect(Sheets("ProductList").Range("C:C"), Sheets("ProductList").UsedRange)
    
        If c.Value = "A" Then
            listProducts.RowSource = c.Address(External:=True)
            'listProducts.AddItem c.Value
        End If
    
    Next c

I just need it to grab each line (column A and B), where column C = the selected category, and put A and B in the listbox.

 

[davedave24]

Nevermind, solved it:

            If c.Value = "A" Then
                With listProducts
                    .AddItem
                    .List(.ListCount - 1, 0) = c.Offset(0, -2).Value
                    .List(.ListCount - 1, 1) = c.Offset(0, -1).Value
                End With
            End If