Pivot Chart formatting persistence 2

[asrisk]

Hello all,

I have a simple pivot table from which I have created a pie chart. (Project ID codes vs hours booked to project)

If I add data labels to the chart using "Format Data Series", then the labels disappear when the pivot table refreshes.

If I add the labels using the "Chart Options" menu option, then they are persistent. Unfortunately changes I make to the font/position using "Format Data Labels" then disappear when the pivot table refreshes!

How can I make the formatting of the chart and data labels persistent?

Thanks

Andy.

-------
^{I am not responsible for any "Sponsored Links" which may appear in my messages.}

 

[xlbo]

Which version of excel ??
If you are woking on a PIVOT chart then I'm afraid you are out of luck - the only way to retain the formatting is to record a macro that re-creates it each time - this can be run automatically after the refresh

If not, try right clicking on the pivot table
Choose "Table Options" and untick "Autoformat Table" and make sure that "Preserve Formatting" IS ticked

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[asrisk]

Good point. It's Excel 2003, and it is a Pivot Chart (i.e. the pivot table toolbar comes up when the chart is selected)

I tried changing the options as suggested, to no effect.

Unfortunately, Excel wants to show the data labels in 5-point text, which is a bit tricky to read.

As suggested I was able to make a macro to do this automatically - but as a follow up question (if I may), how come these two lines work:

ActiveSheet.ChartObjects(1).Activate
ActiveChart.SeriesCollection(1).DataLabels.Font.Size = 7

but this single line doesn't:

ActiveSheet.ChartObjects(1).SeriesCollection(1).DataLabels.Font.Size = 7

Thanks

-------
^{I am not responsible for any "Sponsored Links" which may appear in my messages.}

 

[xlbo]

Gotta admit that I don't know for sure - All I do know is that working with charts in VBA has always been a bit hit and miss for me. There is a distinction between a chart and a chartOBJECT which always causes me grief and I think that is where your line trips up. You ACTIVATE the chartOBJECT but then have to reference it as activeCHART - that is why it cannot go on one line. Now why it works like that ...well ..... that is between Mr Gates and his team of developers !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[asrisk]

Never mind, I am used to working around Mr Gates and his quirks Thanks for your help Geoff.

Andy.

-------
^{I am not responsible for any "Sponsored Links" which may appear in my messages.}

 

[xlbo]

no probs

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[asrisk]

For the sake of completeness, and in case anyone else may have been interested, I have found that the following single line works, without having to activate the ChartObject first:

ActiveSheet.ChartObjects(1).[red]Chart.[/red]SeriesCollection(1).DataLabels.Font.Size = 7

Ours is not to reason why, etc...

-------
^{I am not responsible for any "Sponsored Links" which may appear in my messages.}

 

[xlbo]

Thanks asrisk - have a star yourself for posting back with that !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question