Also filter column in listbox in another column

[ipbr21054]

Code in use shown below

The listbox is populated & when the use makes a selection its filtered for column 4th column
Can an edit be made so its also filtered on 2nd column.

Maybe & if possible when a user clicks in the listbox a option will be shown for 2nd / 4th column then proceed to filer as requested

Private Sub ListBox1_Click()
    Dim i As Long
    Dim colour As String
    
    ' filter listbox on 4th column
    colour = ListBox1.List(ListBox1.ListIndex, 3)
    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.List(i, 3) <> colour Then
            ListBox1.RemoveItem (i)
        End If
    Next i
           
    ' how many of this color
     Me.TextBox2 = ListBox1.ListCount & "  " & colour
End Sub

 

[hungtbatman1]

Code in use shown below

The listbox is populated & when the use makes a selection its filtered for column 4th column
Can an edit be made so its also filtered on 2nd column.

Maybe & if possible when a user clicks in the listbox a option will be shown for 2nd / 4th column then proceed to filer as requested

2 and 4

Private Sub ListBox1_Click()
    Dim i As Long
    Dim s1 As String, s2 As String
    
    ' filter listbox on 2th and 4th column
    s1 = ListBox1.List(ListBox1.ListIndex, 1)   ' col 2
    s2 = ListBox1.List(ListBox1.ListIndex, 3)   ' col 4
    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.List(i, 1) <> s1 Or ListBox1.List(i, 3) <> s2 Then
            ListBox1.RemoveItem (i)
        End If
    Next i
           
    ' how many of this color
'     Me.TextBox2 = ListBox1.ListCount & "  " & colour
End Sub

any number of columns

Private Sub ListBox1_Click()
Dim i As Long, k As Long
Dim columns As String, numbers, s, a As Boolean
    
    columns = InputBox("Enter column numbers separated by a slash /, e.g. 2, or 4, or 2/4, or 2/4/5, ...", "Enter column numbers")
    If columns = "" Then Exit Sub
    numbers = Split(columns, "/")
    ReDim s(0 To UBound(numbers))
    For i = 0 To UBound(numbers)
        s(i) = ListBox1.List(ListBox1.ListIndex, numbers(i) - 1)
    Next i
    
    For i = ListBox1.ListCount - 1 To 0 Step -1
        For k = 0 To UBound(s)
            a = ListBox1.List(i, numbers(k) - 1) <> s(k)
            If a Then Exit For
        Next k
        If a Then ListBox1.RemoveItem (i)
    Next i
           
    ' how many of this color
'     Me.TextBox2 = ListBox1.ListCount & "  " & colour
End Sub

 

[ipbr21054]

2 & 4 didnt work & sorry to say other wasnt what i was looking for.

A user would click in the list box.
Then at this point ask if use wishes to see column 2 or 4 then continue to filter & load as requested.

I can make a custom userform with 2 buttons BUT not sure of the code for it.
User clicks in listbox.
My userform pops up.
User is ask Do you wish to load column 2 or 4
Depending on his answer with then load the column he wishes

Even two command buttons on this current userform to let use select which he needs to filter

 

[ipbr21054]

Example.
This code supplied below is for when the user clicks in the Listbox.

So attached is my userform with two command buttons.
Command Button1 called FILTER MODELS "column 4"

Command Button2 called FILTER PLUG "column 2"

Lets put code to these command buttons to then filter column 2 & 4 & not have it when a user clicks in listbox

Private Sub ListBox1_Click()
    Dim i As Long
    Dim colour As String
    
    ' filter listbox on 4th column
    colour = ListBox1.List(ListBox1.ListIndex, 3)
    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.List(i, 3) <> colour Then
            ListBox1.RemoveItem (i)
        End If
    Next i
           
    ' how many of this color
     Me.TextBox2 = ListBox1.ListCount & "  " & colour
End Sub

 

[hungtbatman1]

Do I understand the question correctly?

Private Sub ListBox1_Click()
    Dim i As Long
    Dim colour As String
    If MsgBox("Filter by column 2? Doesn't mean filter by column 4", vbYesNo, "Select column number") = vbYes Then
        colour = ListBox1.List(ListBox1.ListIndex, 1)
    Else
        colour = ListBox1.List(ListBox1.ListIndex, 3)
    End If
    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.List(i, 3) <> colour Then
            ListBox1.RemoveItem (i)
        End If
    Next i
          
    ' how many of this color
     Me.TextBox2 = ListBox1.ListCount & "  " & colour
End Sub

 

[ipbr21054]

I will give that a try tomorrow thanks.
The wording for the message can’t be cryptic as I work with people who have learning disabilities.

To save time & also maybe another option to test.

On the screenshot o supplied there are 2 command buttons.
Can you advise code so that if a user clicks one it will filter one column & if the user clicks the other then it will click the other column.

I need to make this clear as possible as we’ve had a hard life so far hence why it needs to be easy & straight forward.

Thanks.

 

[ipbr21054]

My last reply won’t work so please ignore it.

 

[NoSparks]

Even two command buttons on this current userform to let use select which he needs to filter

You could use 2 option buttons on the form, initializing the most commonly used one to be true in UserForm_Initialize
Then something along the lines of

Private Sub ListBox1_Click()
    Dim i As Long, filtStr As String, listCol As Long
    
    Select Case True
        Case OptionButton1.Value
            filtStr = ListBox1.List(ListBox1.ListIndex, 3)
            listCol = 3
        Case OptionButton2.Value
            filtStr = ListBox1.List(ListBox1.ListIndex, 1)
            listCol = 1
    End Select

    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.List(i, listCol) <> filtStr Then
            ListBox1.RemoveItem (i)
        End If
    Next i

    ' how many of filter results
     Me.TextBox2 = ListBox1.ListCount
     
End Sub

 

[ipbr21054]

How would that work.

The user would select an option button then make a selection in the listbox ?

 

[hungtbatman1]

I will give that a try tomorrow thanks.

I noticed a mistake. Corrected code:

Private Sub ListBox1_Click()
    Dim i As Long, col As Long
    Dim colour As String
    If MsgBox("Filter by column 2? Doesn't mean filter by column 4", vbYesNo, "Select column number") = vbYes Then
        colour = ListBox1.List(ListBox1.ListIndex, 1)
        col = 1
    Else
        colour = ListBox1.List(ListBox1.ListIndex, 3)
        col = 3
    End If
    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.List(i, col) <> colour Then
            ListBox1.RemoveItem (i)
        End If
    Next i
          
    ' how many of this color
     Me.TextBox2 = ListBox1.ListCount & "  " & colour
End Sub