Ms Excel Pivot Charts

[SteveQL]

Hi,
There is a known problem with pivot charts in excel 2000 where if you update data in the chart you loose all of the charts formatting. Microsoft recommends that you record a macro when applying the formatting which I suppose is an OK workaround if you are comfortable with Macros.

I've heard but have not tested that this issue has been resolved with Excel 2003. Can any of you confirm this??

Cheers!
Steve

 

[sha76]

Sorry, but no, it still happens in 2003. The ones I'm using are updated in vba anyway, so it was no problem to stick a bit more code in there, but there shouldn't be anything you can't capture by recording a macro (that's where I got most of my code to be honest, I hate Excel vba!).

"Your rock is eroding wrong." -Dogbert

 

[xlbo]

Some stuff you can stop changing by unticking the "Autoformat" option of the table / chart

Unfortunately, that doesn't apply to much...

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[SteveQL]

OK, so if Excel 2003 is not a fix, do you think that a macro needs to be recorded for each Pivot chart, or could I write a fairly genaric Macro that would capture the charts existing format before the update and then reapply the formatting after the update?

Any code samples would be greatly appreciated!

Cheers!
Steve

 

[xlbo]

It would be tricky to write generic code for this as different chart types have different features - you can't treat a pie chart the same as a line chart - you can't treat an area chart the same as a bar chart etc etc - you'd need to store the chart TYPE and then based on teh type, lookup what variables are available and store all of them - bear in mind that chart objects are some of the most complex objects to work with in VBA and you have the start of a nightmare - best bet is to record the macro on creation and just keep applying it for each individual chart

IMHO - this really is something that MS should have put right a long time ago - the fact that they havn't indicates (to me at least) that it is a difficult thing for them to achieve

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[sha76]

Thankfully for me the charts I'm using currently are only spread across a few files, so I just have the one macro for each. My code was generated from recording a macro when changing one chart then copying & editing the code for the others. Admittedly this doesn't give you the most efficient code, unfortunately I don't know my way round Excel vba well enough to improve it without a lot of research!
You could try

http://www.mrexcel.com

for further help.

"Your rock is eroding wrong." -Dogbert