locked
How to run a macro when a Filter is applied on Particular Pivot Field RRS feed

  • Question

  • I have seen how to call a macro when a filter is applied on the pivot table here: How to run a macro automatically when the selections of pivot tables are changed

    But I have a different requirement, I have 5 Pivot Fields and each filter change has a different function to perform. So, I'm wondering if it is possible to assign a different macro for each of the Pivot Fields?

    I know that I can use worksheet_Change function and then use range as a parameter to check whether a filter is applied in that range or not. But the problem here is, I'll be moving the Pivot Fields to Column labels and Row Labels within the macro which will call other macros subsequently.

    I don't want that to happen. That is the reason I'm searching for a way to detect Pivot Filter change on ONE PIVOT FIELD


    Pramod

    Wednesday, August 3, 2016 4:16 PM

All replies

  • You can use

    Application.EnableEvents=false

    'Do other code

    Application.EnableEvents=True

    If that doesn't work, then the other standard practice is to use a global variable such as IgnoreEvent:

    Dim IgnoreEvent as Boolean

    IgnoreEvent=True

    'Do other code

    IgnoreEvent=False

    Then in other events, if IgnoreEvent is True, exit the event without doing anything.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, August 3, 2016 9:33 PM
  • I'm sorry but I think I didn't get your answer completely.  I didn't understand how I'll get to know which Pivot Field has caused the macro to run. Because, whenever a Field item selection filter is changed, I move that to Column or Row label (Depending on my need) and then perform some actions on them. So, can you please tell me how can I know the Pivot Field that fired the macro?

    You can use

    Application.EnableEvents=false

    'Do other code

    Application.EnableEvents=True

    If that doesn't work, then the other standard practice is to use a global variable such as IgnoreEvent:

    Dim IgnoreEvent as Boolean

    IgnoreEvent=True

    'Do other code

    IgnoreEvent=False

    Then in other events, if IgnoreEvent is True, exit the event without doing anything.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz



    Pramod


    Wednesday, August 3, 2016 9:42 PM