Multi-select listbox output into Worksheet Autofilter

[Darvistor]

Hello all,

I did some searches for this issue and there are some variations, but I can't seem to find my same issue. I am using Excel 2010 and have written/tweaked VBA code for a while now. I have a userform I created with 2 multi-select listboxes and 3 textboxes. The goal of my userform is to allow the user to input a start and end date for a record search, select one or more products from a list, select one or more record states from a list, make a copy of a worksheet, have it renamed, and finally filter the worksheet based on the selections from the userform.

The date filter, worksheet rename and copy all works fine. The issue I am currently having is getting the selections from the two listboxes in the proper format to use for the autofilter.

This is part of the code I used associated with the date filter:

    Dim StartDate As Date
    Dim EndDate As Date
    
    Range("C1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AT$2000").AutoFilter Field:=2, Criteria1:= _
        ">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

This part works great as there are never more than two selections for the date filter.

I recorded a macro of me selecting 3 choices in my product list box and the result is the following

    ActiveSheet.Range("$A$1:$AT$2000").AutoFilter Field:=5, Criteria1:=Array( _
        "Selection1", "Selection2", "Selection3"), Operator:=xlFilterValues

I used this as a baseline and after some forum/google searches came across this piece of code to get a multi-select output:

Dim ProductArray() As String
    Dim StateArray() As String
    Dim Cnt As Long
    Dim r As Long

'Product Filter
    Cnt = 0
  With Me.ListBox1
        If .ListIndex <> -1 Then
            For r = 0 To .ListCount - 1
                If .Selected(r) Then
                    Cnt = Cnt + 1
                    ReDim Preserve ProductArray(1 To Cnt)
                    ProductArray(Cnt) = .List(r)
                End If
            Next r
        End If
    End With
      
    ActiveSheet.Range("$A$1:$AT$2000").AutoFilter Field:=3, Criteria1:=Array( _
    ProductArray), Operator:=xlFilterValues
    
    'State Filter
    Cnt = 0
    With Me.ListBox2
        If .ListIndex <> -1 Then
            For r = 0 To .ListCount - 1
                If .Selected(r) Then
                    Cnt = Cnt + 1
                    ReDim Preserve StateArray(1 To Cnt)
                    StateArray(Cnt) = .List(r)
                End If
            Next r
        End If
    End With
    
    ActiveSheet.Range("$A$1:$AT$2000").AutoFilter Field:=6, Criteria1:=Array( _
    StateArray), Operator:=xlFilterValues

I thought I had this working at first, but now it seems that I am only getting the last item selected in the listbox. I don't know if this last code section is appropriate to get the output in the correct format. I also tried another method from the forum to output the listbox selection to a column in the worksheet. I was able to do this but then I can't figure out how to take these values and get them in the ("Selection1", "Selection2", "Selection3") format.

Any help/suggestions would be appreciated. If there is a different/better way to acheive the desired outcome, I'm all ears.

Thanks,

Darv

 

[Darvistor]

I figured I would ask again since I posted this on the day of maintenance. I thought someone would have responded.