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

 

[Domenic]

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

 

[m0atz]

Domenic - this works fantastic, thank you.

 

[wyco]

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

Hello,

Any chance you could help with the same thing in v2003 please?
Thanks!

Viktor

 

[Domenic]

Try...

Option Explicit

Private Sub CommandButton1_Click()

    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim r As Long
    
    '//Change the name of the source worksheet, accordingly
    Set wksSource = Worksheets("Sheet1")
    
    '//Change the name of the destination worksheet, accordingly
    Set wksDest = Worksheets("Sheet2")
    
    With Me.ListBox1
        If .ListIndex <> -1 Then
            If wksSource.FilterMode Then wksSource.ShowAllData
            For r = 0 To .ListCount - 1
                If .Selected(r) Then
                    wksSource.Range("A1:R1").AutoFilter field:=1, Criteria1:=.List(r)
                    With wksSource.AutoFilter.Range
                        .Offset(1, 0).Copy Destination:=wksDest.Cells(wksDest.Rows.Count, "A").End(xlUp)(2)
                    End With
                End If
            Next r
            wksSource.ShowAllData
        End If
    End With
 
End Sub

 

[wyco]

Brilliant, thanks Domenic!

 

[Ady.]

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

First visit, first search, my problem solved Thank you

 

[danilao]

First visit, first search, my problem solved Thank you

Could you attach here a file with this example?

 

[yessabar]

Hi Guys,

I have encountered a similar problem and I am still working on it... if anyone of the community could please advice with the following issue it will be very helpful ?

I have created a userform to search ( or filter) 6 criterias that are in my column Worksheet. The criteria are : the firstname ( combobox), the surname (string : Textbox) , the sex ( Combobox), the weight ( integer : textbox),number of consommation ( integer : containing) and the consummation (or number of glasses drank).

In my userform I have added those criteria and a listbox to give me back the information picked in the worksheet. I managed to do it for one criteria ( for the firstname) , however when I want to apply the condition with more than one criteria I have some pain ..

Here is the code of my userform :


Private Sub CommandButton1_Click()
crit = ComboBox2.Value 'firstname, 1st column in my worksheet
crit2 = TextBox3.Value 'surname string
crit3 = ComboBox1.Value 'sexe
crit4 = TextBox2.Value ' weight
crit5 = TextBox4.Value 'number of consummation
crit6 = ComboBox3.Value ' comments


If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
lr = 2
Else
lr = Cells(Rows.Count, 1).End(xlUp).Row
End If
Me.Lbx.Clear
Dim col As Long
For x = 2 To lr


If Cells(x, 1) = crit Then
Me.Lbx.AddItem Cells(x, 1) ' firstname with is the 1st column of my worksheet
Me.Lbx.List(Me.Lbx.ListCount - 1, 2) = Cells(x, 3) 'surname wich is the 3 column
Me.Lbx.List(Me.Lbx.ListCount - 1, 3) = Cells(x, 4) 'sexe 4th column of the worksheet
Me.Lbx.List(Me.Lbx.ListCount - 1, 4) = Cells(x, 13) '...
Me.Lbx.List(Me.Lbx.ListCount - 1, 4) = Cells(x, 14) '...


Else


End If
Next x
End Sub

I want to apply that code with all the criteria needed.
Thanks by advance
Yass

 

[Domenic]

@ Yessabar

First, it's always best to start your own thread. When you bury your question in an existing one, not as many people will end up seeing it and so you're less likely to get a response. If you had started your own thread, you probably would have had a response by now from one of the many volunteers here on the board.

With regards to your question, try...

If Cells(x, 1).Value = crit And Cells(x, 3).Value = crit2 And Cells(x, 4).Value = crit3 And Cells(x, 13).Value = crit4 And Cells(x, 14).Value = crit5 Then
    With Me.Lbx
        .AddItem Cells(x, 1) ' firstname with is the 1st column of my worksheet
        .List(.ListCount - 1, 1) = Cells(x, 3) 'surname wich is the 3 column
        .List(.ListCount - 1, 2) = Cells(x, 4) 'sexe 4th column of the worksheet
        .List(.ListCount - 1, 3) = Cells(x, 13) '...
        .List(.ListCount - 1, 4) = Cells(x, 14) '...
    End With
End If

Note that the row and column numbering begin with zero. So, for columns, the first column is 0, the second column is 1, etc. Also, as it stands, the conditional statements are case-sensitive. If you don't want the first three conditions to be case-sensitive, you can do the following...

If UCase(Cells(x, 1).Value) = UCase(crit) And UCase(Cells(x, 3).Value) = UCase(crit2) And UCase(Cells(x, 4).Value) = UCase(crit3) And Cells(x, 13).Value = crit4 And Cells(x, 14).Value = crit5 Then

Hope this helps!