Pivot Chart VBA Reformat

[Guest]

Excel 2000:

If I create a pivot table and then a pivot chart based on that table, any
chart formatting I apply to the pivot chart is eliminated whenever the pivot
table is refreshed, or when the chart changes in any way.

I have written VBA code to format the pivot chart so it looks just the way I
want it to (custom chart type, font, etc.), but there appears to be no Chart
Event that will allow me to run the formatting code successfully. I tried
the Chart 'Calculate' Event, but that gets me into an infinite loop because
it seems that Excel re-calcs the pivot chart whenever parts of the chart are
selected for formatting (or when formatting is completed).

Anyone know how I can get the pivot chart to run my chart formatting code so
that the code runs one time any time the chart changes? Thanks for your help
....

Bill M.

 

[Guest]

One simple solution is a Global Variable - a binary 'switch' that is set to 1
when the formatting code begins execution, and back to 0 when the code is
done.

In the chart Calculate Event, the code only executes if the switch is set to
0.

This effectively disables the chart Calculate Event from executing any
actions while the re-formatting code is running, even if the reformatting
code fires the Calculate Event.

Once the reformatting code is done, then it is set to run again with the
next Calculate Event, because the switch is set back to 0.