filtering data through listboxes

[robertvdb]

I've been spending already quite some time on this, can't find it:

I have a range of data which needs to be filtered by employee name and project. I select the filter criteria through two listboxes. When I change the selection in either listbox, then the filter results should be displayed in cols A B C D.

Any help is appreciated.

 

[DanteAmor]

Assuming the listboxes are listbox1 for the name (column A) and listbox2 for the project (column C).
Which both have the MultiSelect property.


Then try the following code. Put all the code in your userform.

Private Sub ListBox1_Change()
  Call Proc_Filter_Data
End Sub

Private Sub ListBox2_Change()
  Call Proc_Filter_Data
End Sub

Sub Proc_Filter_Data()
  Dim i As Long, n As Long
  Dim st1 As String, st2 As String
  Dim ar1 As Variant, ar2 As Variant
  
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  With ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) Then st1 = st1 & .List(i) & ","
    Next
  End With
  If st1 <> "" Then
    st1 = Left(st1, Len(st1) - 1)
    ar1 = Split(st1, ",")
    Range("A1:D31").AutoFilter 1, ar1, xlFilterValues
  End If

  With ListBox2
    For i = 0 To .ListCount - 1
      If .Selected(i) Then st2 = st2 & .List(i) & ","
    Next
  End With
  If st2 <> "" Then
    st2 = Left(st2, Len(st2) - 1)
    ar2 = Split(st2, ",")
    Range("A1:D31").AutoFilter 3, ar2, xlFilterValues
  End If
End Sub

Comment on the results.
Respectfully
Dante Amor

 

[DanteAmor]

Edit:

Use this code:

Private Sub ListBox1_Change()
  Call Proc_Filter_Data
End Sub

Private Sub ListBox2_Change()
  Call Proc_Filter_Data
End Sub

Sub Proc_Filter_Data()
  Dim i As Long, n As Long
  Dim st1 As String, st2 As String
  Dim ar1 As Variant, ar2 As Variant
  
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  With ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) Then st1 = st1 & .List(i) & ","
    Next
  End With
  If st1 <> "" Then
    st1 = Left(st1, Len(st1) - 1)
    ar1 = Split(st1, ",")
    Range("A1:D1").AutoFilter 1, ar1, xlFilterValues
  End If

  With ListBox2
    For i = 0 To .ListCount - 1
      If .Selected(i) Then st2 = st2 & .List(i) & ","
    Next
  End With
  If st2 <> "" Then
    st2 = Left(st2, Len(st2) - 1)
    ar2 = Split(st2, ",")
    Range("A1:D1").AutoFilter 3, ar2, xlFilterValues
  End If
End Sub

 

[robertvdb]

Sir, your solution works very well !

Brilliant !

Thank you so much.