3

I got a pivot table with shows the data for Quarters in it, and a macro that performs some calculations based on the data in pivot table. So now I want to run the Macro automatically when I changed the selection of pivot items, like if select Quarter1 , it shows the values for Q1 and run the macro automatically so that it calculate the values for Q1, and same for all selections including the multiple selections..

Community
  • 1
  • 1
harry
  • 310
  • 1
  • 4
  • 17

1 Answers1

10

Put something like this in the code module of the worksheet with the pivot table:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

' Call your macro here

End Sub
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • 1
    + 1 Yup. I got it confused with `Worksheet.PivotTableAfterValueChange Event` LOL – Siddharth Rout May 29 '12 at 13:49
  • Thanks mate, Its just very simple , I made it mess by doing lot of things to this before... Thanks again , this one working great.. :) – harry May 29 '12 at 13:58
  • Hi Doug, Can you help me with my other question regarding the pivotitems into seperate cells please?? – harry May 29 '12 at 14:01
  • Glad to help. I left a comment on your other question: http://stackoverflow.com/q/10785343/293078. – Doug Glancy May 29 '12 at 14:41
  • Is there a C# (Excel Interop) example for tapping into a PivotTable's change event? I need to rerun some "hide" code when the user filters a PivotTable. – B. Clay Shannon-B. Crow Raven Oct 28 '16 at 21:42
  • @B.ClayShannon there is, at least for the PivotTableUpdate event discussed here. For general how-to see this: http://www.bettersolutions.com/csharp/excel-interop/event-handling.htm. For the `PivotTableUpdate` syntax see: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheet_events.aspx – Doug Glancy Oct 29 '16 at 14:59
  • @DougGlancy: Thanks, I don't see how this can work, though, on a dead/petrified .xlsx file. Is it possible to embed the event handler code in the .xlsx file so that it actually would fire? I doubt it, but am not sure. – B. Clay Shannon-B. Crow Raven Oct 29 '16 at 15:30
  • 1
    @B.ClayShannon, no you can't embed it in an existing xlsx. The best you can do for an existing file is create a .Net addin with an application-level event that then checks if the workbook that fired it is your xlsx. The user would then have to install the addin. Not trivial, obviously. – Doug Glancy Oct 29 '16 at 17:10
  • @DougGlancy: Interesting concept - do you know of any examples that show how to do this? – B. Clay Shannon-B. Crow Raven Oct 31 '16 at 14:36
  • Here's something that looks good: https://msdn.microsoft.com/en-us/library/cc668205.aspx. I'm sure there's plenty of good resources out there. – Doug Glancy Nov 01 '16 at 04:08