none
VBA to Autofilter Pivot Tables on change via combobox RRS feed

  • Question

  • Hello,

    I have recorded a macro to filter the pivot table however I am unable unable to get it to work with the change event on my user form?  I have three pivot fields that filter the report. 

    Thanks.

    Friday, January 20, 2017 8:30 PM

All replies

  • Hi,

    After setting the rowsource of the combobox, you could try the code like:

    Private Sub ComboBox1_Change()
    Set pvt = ActiveSheet.PivotTables("PivotTable1")
    Set fld = pvt.PivotFields("a")
    fld.ClearAllFilters
    For Each itm In fld.PivotItems
    If itm <> ComboBox1.Value Then
    itm.Visible = False
    Else
    itm.Visible = True
    End If
    Next itm
    End Sub

    If you have any issue, please let me know.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, January 23, 2017 3:01 AM
    Moderator
  • Is there a to make this a function and call it from the on change event? I believe this would be easier.

    Wednesday, January 25, 2017 8:40 PM
  • Hi,

    Please check if the code below meets your requirement.

    Private Sub ComboBox1_Change()
    Call filter("PivotTable1", "a", ComboBox1.Value)
    End Sub
    Function filter(pvtName As String, fldName As String, val As String)
    On Error GoTo handler
    Set pvt = ActiveSheet.PivotTables(pvtName)
    Set fld = pvt.PivotFields(fldName)
    fld.ClearAllFilters
    For Each itm In fld.PivotItems
    Debug.Print itm
    If itm = val Then
    itm.Visible = True
    Else
    itm.Visible = False
    End If
    Next itm
    Exit Function
    handler:
    MsgBox "No " & val & " in the " & pvtName
    fld.ClearAllFilters
    End Function

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 26, 2017 3:16 AM
    Moderator