Autofiltering

[musictech]

I am trying to filter the contents of a listbox, how can I do that?

[CODE]Private Sub categorybx_Click()
If categorybx.Value = "Cable" Then
categorybx.AutoFilter Criteria1:="Cable"
If categorybx.Value = "Fiber" Then
categorybx.AutoFilter Criteria1:="Fiber"
If categorybx.Value = "Faceplate" Then
categorybx.AutoFilter Criteria1:="Faceplate"
If categorybx.Value = "Jack" Then
categorybx.AutoFilter Criteria1:="Jack"
If categorybx.Value = "Connector/Coupler" Then
categorybx.AutoFilter Criteria1:="Connector/Coupler"
If categorybx.Value = "Patch Cable" Then
categorybx.AutoFilter Criteria1:="Patch Cable"
If categorybx.Value = "Hardware" Then
categorybx.AutoFilter Criteria1:="Hardware"
If categorybx.Value = "Termination" Then
categorybx.AutoFilter Criteria1:="Termination"
If categorybx.Value = "Electronics" Then
categorybx.AutoFilter Criteria1:="Electronics"
End Sub[/CODE]

[musictech]

I have made some progress.

I have the following now:

Private Sub categorybx_Click()
    Dim cat As Range
    Dim typ As Range
    Dim des As Range
    Dim MyArray() As Variant
    
    Set inf = Sheets("data sheet").Range("B1:d20000")
    Sheets("data sheet").AutoFilterMode = False
    
    If categorybx.Value = "Fiber" Then
        inf.AutoFilter Field:=1, Criteria1:="Fiber"
        'MyArray = WorksheetFunction.Transpose(inf)
        Typebx.List = WorksheetFunction.Transpose(inf)
    End If
    If categorybx.Value = "Faceplate" Then
            inf.AutoFilter Field:=1, Criteria1:="Faceplate"
    End If
    If categorybx.Value = "Jack" Then
            inf.AutoFilter Field:=1, Criteria1:="Jack"
    End If
    If categorybx.Value = "Connector/Coupler" Then
            inf.AutoFilter Field:=1, Criteria1:="Connector/Coupler"
    End If
    If categorybx.Value = "Patch Cable" Then
            inf.AutoFilter Field:=1, Criteria1:="Patch Cable"
    End If
    If categorybx.Value = "Hardware" Then
            inf.AutoFilter Field:=1, Criteria1:="Hardware"
    End If
    If categorybx.Value = "Termination" Then
            inf.AutoFilter Field:=1, Criteria1:="Termination"
    End If
    If categorybx.Value = "Electronics" Then
            inf.AutoFilter Field:=1, Criteria1:="Electronics"
    End If
    
 End Sub

I can not figure out how to add the filtered items to the second listbox.

I have 3 listboxes representing 3 columns in a spreadsheet. I need the listbox to filter the data in the subsequient listboxes. Much the way autofiltering works in a spreadsheet. How can I do that?

thanks for the help

[Mike Fogleman]

I am not sure what your goal is here. It sounds like you want to present a
list of choices, and when the user selects one, leave only the selected item
in the listbox. To do that make sure the ListBox has a LinkedCell somewhere
on the worksheet. Then put this code in your ListBox_Click ().

Private Sub categorybx_Click()
categorybx.Enabled = False
categorybx.ListFillRange = categorybx.LinkedCell
categorybx.Enabled = True
End Sub

This tells the listbox to display a different list, the item chosen from the
original list.
If this is not what you intended, then post back.
Mike F


"musictech" <[email protected]> wrote
in message news:[email protected]...
>
> I am trying to filter the contents of a listbox, how can I do that?
>
> [CODE]Private Sub categorybx_Click()
> If categorybx.Value = "Cable" Then
> categorybx.AutoFilter Criteria1:="Cable"
> If categorybx.Value = "Fiber" Then
> categorybx.AutoFilter Criteria1:="Fiber"
> If categorybx.Value = "Faceplate" Then
> categorybx.AutoFilter Criteria1:="Faceplate"
> If categorybx.Value = "Jack" Then
> categorybx.AutoFilter Criteria1:="Jack"
> If categorybx.Value = "Connector/Coupler" Then
> categorybx.AutoFilter Criteria1:="Connector/Coupler"
> If categorybx.Value = "Patch Cable" Then
> categorybx.AutoFilter Criteria1:="Patch Cable"
> If categorybx.Value = "Hardware" Then
> categorybx.AutoFilter Criteria1:="Hardware"
> If categorybx.Value = "Termination" Then
> categorybx.AutoFilter Criteria1:="Termination"
> If categorybx.Value = "Electronics" Then
> categorybx.AutoFilter Criteria1:="Electronics"
> End Sub
> --------------------
>
>
> --
> musictech
> ------------------------------------------------------------------------
> musictech's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=24851
> View this thread: http://www.excelforum.com/showthread.php?threadid=391066
>

[musictech]

Mike,

What I have is a list of parts. They are represented in 3 columns. The first column is the Category. So I will have only a few categories but several items that fall under those categories. Next column is subcategory and the final is a description (or the specific part). What I have created is a userform with 3 listboxes, and a button called insert. My goal is to have the contents of each column listed in the each listbox. When I select an item from any listbox, I would like it to filter the other boxes. Normally of course they would start from the left at the category box and move to the right. And the category box is the only one I would like not to be filtered. I would like it to always show every category, so you can easily jump into another category.

The insert button is going to be a different story. I am using this spreadsheet as a master list, and will not be the spreadsheet I will be dealing with. So the insert button will be used to copy the selected data from the userform into another spreadsheet based on the cell I select to be the starting point. So basically the end user opens a new parts order spreadsheet, selects the first cell in the row they want to start on, then clicks on a button at the top and starts selecting parts. As he/she hits insert, the parts they select are copied into the order in the next available row after the row they selected.

I hope that clears up things and thanks for your help

[musictech]

still trying to solve this problem