I wanted to use one of my customized charts (2 stack bars and one trending line to show 3 piece of data) as pivot chart. However, every time I click on the break to change a data value, Excel defaults the chart back to the 3-stacked-bar chart which is one of standard chart that I used to create my customized chart.

Is there a way for me to use my customized chart as pivot chart? I have tried many methods, including recording macro to see how default is set up and how pivot chart is created. But just no success in reaching what I am trying to do. If I need to write macro to achieve this, does anyone have a sample for me to model from?

Thanks

Kathy

Hi Kathy

You must record a macro which does the cutomizing to your chart. Copy the macro to the Chartsheet calculate event. Every time you change your Pivot chart the Chart calculate event runs the code.

Here is some example:

Private Sub Chart_Calculate()
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ChartType = xlLine
End Sub

-vemaju

Hi vemaju-

Thanks for responding to my question. But how to automate so that when I click on the page break for different values on the chart sheet, which is created by using pivot chart, it will execute the Chart_Calculate to display the desired chart? I was thinking about designing a form to accomplish such task, but was glad to see your suggestion which seems to be easier. I have already created a macro for my customized chart. But just don't know how to link the pieces together.

Thank you in advance for your help.

Kathy

Hi Vemaju -

I investigated further, provided your previous input. And it worked. Thank you so much again.

Kathy