Event to fire when pivottable is pivoted

T

Tod

I'm trying to write code that will run when the user moves
pivot fields between page and row area.

My first idea was to enter a formula:

=Counta(A:A)

It's not a guarantee, but most times this would cause the
formula to change. So then I coded the Worksheet_Change
event. Of course this did not work because the Change
event looks for the actual content of the cell to change,
not the value of the formula. So I put my code in a
Worksheet Calculate event. This works, but the event will
run ANYTIME something on the sheet calculates. I don't
know any code that would limit the event to a range.

So... what can I do (Excel 2000 or older) to have code run
only when the pivot table is pivoted.

tod
 
B

Brad Vontur

This isn't an ideal solution. But when you change the
layout of the PivotTable a worksheet Selection change
event will always fire. So you can run a procedure that
determines if the new state of the Worksheet layout is
equal to the previous state, use static variables/arrays
perhaps. If the layout is different, then run another
procedure.

-Brad
 
Top