Add Pivot Table Filters to current UserForm code.

This is what I have now.

Can someone tell me what I am doing wrong?

Private Sub cmdFilter_Click()
    Dim cChk As Control, arrVals, i As Long
    Dim X, objDict As Scripting.Dictionary, lngRow As Long

    Application.ScreenUpdating = False

    'Clear filters
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0

    'Get unique list of values in the filter column (Col. "B")
    Set objDict = New Scripting.Dictionary
    X = Application.Transpose(Range("B13", Cells(Rows.Count, "B").End(xlUp)))
    For lngRow = 1 To UBound(X, 1)
        objDict(X(lngRow)) = 1
    Next lngRow

    For Each cChk In Me.grpFilter.Controls
        If TypeName(cChk) = "CheckBox" Then
            If cChk.Value = True Then
                objDict.Remove cChk.Tag
            End If
        End If
    Next cChk

    ReDim arrVals(objDict.Count - 1)
    For i = 0 To objDict.Count - 1
        arrVals(i) = objDict.Keys(i)
    Next i
    ReDim Preserve arrVals(1 To UBound(arrVals) + 1)
    arrVals(UBound(arrVals)) = "="
    On Error Resume Next
    'Pivot Table
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("Fund"), _
    varItemList:=arrVals.Visible = False
    'Worksheet
    ActiveSheet.Range("A12").CurrentRegion.AutoFilter _
        Field:=2, Criteria1:=arrVals, Operator:=xlFilterValues
    Unload Me
    Application.ScreenUpdating = False
End Sub

'Pivot Table
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("Fund"), _
    varItemList:=arrVals.Visible = False
    'Worksheet
    ActiveSheet.Range("A12").CurrentRegion.AutoFilter _
        Field:=2, Criteria1:=arrVals, Operator:=xlFilterValues

Looks good to me! I read somewhere you could use an array, but I guess that explains why I couldn't get it to work. Regardless, it looks great. Thanks.


Now I just have to figure out how to get it to work with multiple pivot-tables/columns if possible. If it isn't possible, this is great and fulfils the requirement.

V/r,

Josh