MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2000 Pivot Chart & compatability with 97 VBA macro

Posted by Fred Giesen on January 12, 2001 3:16 PM

How do I turn off excel 2000's pivot charts? Or create compatable code between Excel 97 & 2000?

When running Macro's generated in excel 97 on excel 2000, the command which makes a chart from a pivot table creates a pivot chart. The pivot chart object does not have the same methods and properties as a chart object therefore the macro's bomb out.

If you attempt to make a chart using only a portion of a pivot table your problem gets worse. Selecting just one cell in a pivot table, then selecting the chart wizard gives you a pivot chart of the whole pivot table. This results in a huge formatting project whereas in excel 97, you got what you wanted. (and every time you change a pivot item you need to reformat!)

The tools I develop are used by excel 97 and excel 2000 users.

The interesting thing is when you open your spread sheet everything looks the same as excel 97, as long as you don't create a new chart all the macros
I'm dreading having to rewrite my tool and as long as I have users on excel 97 that's not a solution anyway.

Another important feature I need is the ability to add a series to the chart, the chart wizard does not give you that option when working with a pivot chart.

Posted by Jennifer McMahon on January 22, 2001 1:04 PM

Is this why I'm creating huge files (2M) when my older ones are 400K? I have been using an old file as a template and copying my new data in to get around it.