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

 

[NoSparks]

How would that work.

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

What happened when you tried it ?

 

[ipbr21054]

Post #8 worked fine.
I was asking before i tried it as i was out at the time.

Just a question.
If a user doesnt select a option button & clicks in the listbox they will see this RTE,debug to line shown in red.
Is there something we can do about that or do we just run with it.

Thanks

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

 

[NoSparks]

Just a question.
If a user doesnt select a option button & clicks in the listbox they will see this RTE,debug to line shown in red.
Is there something we can do about that or do we just run with it.

Re-read the suggestion,

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

and that situation will never arise

 

[ipbr21054]

Thanks will go with post 8