MultiSelect Listbox - Autofilter

[m0atz]

I have two listboxes on a userform, one for year, one for month.

The month is a multiselect box, i.e. user can select more than one month. What I'd like to do is to autofilter the multiple items selected within the listbox onto one column in a sheet. So, it would view entries for Jan, Feb, Mar etc.

The code is easy for one month, but now i'm using multiselect lb I'm stuck. I'm thinking along the lines of an array, but not sure how this would work in defining the length of the array required etc...

Something like this?

  With Worksheets("Visits")
        .ShowAllData
        .range("A1:R1").autofilter field:=17, Criteria1:=Array("x", "y", etc)
        'where x and y etc are values in the listbox select
  End With

 

[yessabar]

Hi Dominic,

Above all thanks for your help. Actually, this is the first time I am posting a thread on such forum, so I take note about your advice.

The provided code would only work if the user enters all the criteria asked. However if the user does not want to fill one of the criterias it won't pick up the data for the selected criteria.

I have not manage to find a way to let the user choose one of the criteria and then apply the filter upon his choice...

For example : if the user want to apply his filter on the criteria sexe="M" the goal will be to get into the listBox all the male that are in my sheet.

If you have another alternative it will be very helpful.
++
Yass

 

[Domenic]

I haven't fully tested the following macro, but try...

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> Crit1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Crit2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Crit3 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Crit4 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Crit5 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Crit6 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Crit1 = ComboBox2.Value <SPAN style="color:#007F00">'firstname</SPAN><br>    Crit2 = TextBox3.Value <SPAN style="color:#007F00">'surname</SPAN><br>    Crit3 = ComboBox1.Value <SPAN style="color:#007F00">'sexe</SPAN><br>    Crit4 = TextBox2.Value <SPAN style="color:#007F00">'weight</SPAN><br>    Crit5 = TextBox4.Value <SPAN style="color:#007F00">'number of consummation</SPAN><br>    Crit6 = ComboBox3.Value <SPAN style="color:#007F00">'comments</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> Len(Crit1) = 0 And Len(Crit2) = 0 And Len(Crit3) = 0 And Len(Crit4) = 0 And Len(Crit5) = 0 <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Please enter at least one criteria, and try again!", vbExclamation<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    Me.LBx.Clear<br>    <br>    LastRow = Cells(Rows.Count, 1).End(xlUp).Row<br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> LastRow<br>        <SPAN style="color:#00007F">If</SPAN> IIf(Len(Crit1) = 0, <SPAN style="color:#00007F">True</SPAN>, UCase(Cells(i, 1)) = UCase(Crit1)) And IIf(Len(Crit2) = 0, <SPAN style="color:#00007F">True</SPAN>, UCase(Cells(i, 3)) = UCase(Crit2)) And _<br>            IIf(Len(Crit3) = 0, <SPAN style="color:#00007F">True</SPAN>, UCase(Cells(i, 4)) = UCase(Crit3)) And IIf(Len(Crit4) = 0, <SPAN style="color:#00007F">True</SPAN>, <SPAN style="color:#00007F">CStr</SPAN>(Cells(i, 13)) = Crit4) And _<br>            IIf(Len(Crit5) = 0, <SPAN style="color:#00007F">True</SPAN>, <SPAN style="color:#00007F">CStr</SPAN>(Cells(i, 14)) = Crit5) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Me.LBx<br>                .AddItem Cells(i, 1) <SPAN style="color:#007F00">' firstname 1st column</SPAN><br>                .List(.ListCount - 1, 1) = Cells(i, 3) <SPAN style="color:#007F00">'surname 3rd column</SPAN><br>                .List(.ListCount - 1, 2) = Cells(i, 4) <SPAN style="color:#007F00">'sexe 4th column</SPAN><br>                .List(.ListCount - 1, 3) = Cells(i, 13) <SPAN style="color:#007F00">'weight 13th column</SPAN><br>                .List(.ListCount - 1, 4) = Cells(i, 14) <SPAN style="color:#007F00">'number of consummation 14th column</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Does this help?

 

[yessabar]

Hi Dominic ,


It does not work properly . In fact, the code do roughly the same thing as the previous one . It takes into consideration only one criteria. When I enter more than 1 criteria nothing is displayed in the list box.


I have thing about another way to do this that might be easier and more realistic .


As a classic filter, the list box would show all the data when we open the userform . In other word , if there is no criteria selected the list box contains all the data that are in me sheet.
When the user will change or add a criteria the list will reduce automatically. I have try that with the function *Like* but I have not reach my goal ..


Do you have an idea of how we can do that ?

 

[yessabar]

Hi Dominic,

Just to let you know that I get it

Thanks a lot for your help
Yass

 

[Domenic]

You're very welcome! And thanks for the feedback!

 

[nimesh29]

Hi Yessabar-
I know this post is old but, I am trying to create something similar and struggling to filter the Listbox. Are you able to share the sample file of this project?

Regards,
Nimesh

 

[HimJ]

If you're using Excel 2007 or later version, try...

Option Explicit

Private Sub CommandButton1_Click()

    Dim MyArray() As String
    Dim Cnt As Long
    Dim r As Long
   
    Cnt = 0
    With Me.ListBox1
        If .ListIndex <> -1 Then
            For r = 0 To .ListCount - 1
                If .Selected(r) Then
                    Cnt = Cnt + 1
                    ReDim Preserve MyArray(1 To Cnt)
                    MyArray(Cnt) = .List(r)
                End If
            Next r
        End If
    End With
       
  With Worksheets("Visits")
        If .FilterMode Then .ShowAllData
        .Range("A1:R1").AutoFilter field:=17, Criteria1:=MyArray, Operator:=xlFilterValues
  End With

  'etc...
 
End Sub

It's replacing the values in the array. I mean, I have made 2 selections in the listbox. In the dynamic array it first stores the 1st selection but when it comes to 2nd one, it replaces the 1st value in the array. Hence unable to apply autofilter with both the values as criteria. Only latest one is applied. Any modifications you suggest. I am working on Excel 2013. I have a multiselect listbox and want to apply autofilter with all the values selected in the listbox as the criteria...