Custom PivotChart reverts to standard type when modified

November 30, 2005 6:40 PM

In Excel 2002 when I create a PivotChart and apply a custom or user-defined chart type (in this instance a 2-axis line chart), the chart type reverts to a standard single-axis line chart whenever I modify one of the PivotFields. How do I prevent this?

I've changed the default chart type, but it makes no difference. Currently the solution I'm using is a piece of VBA code in the Calculate event of the chart class module, thusly:

Private Sub Chart_Calculate()

ActiveChart.ApplyCustomType _
ChartType:=xlUserDefined, _
TypeName:="Ritchie"

End Sub

The problem is that this scrap of code must be included with each and every chart, and it isn't very portable: if someone opens the workbook who doesn't have a custom chart type called "Ritchie" on their workstation the event will raise an error.

posted by Ritchie to computers & internet (3 comments total)

In the table options, there's a checkbox along the lines of reformat or autoformat or something like that (I don't have access to Excel right now). You want to uncheck that.
posted by willnot at 6:57 PM on November 30, 2005

Not familiar with the problem, or that version of Excel, but if Willnot's suggestion doesn't get you anywhere I'd recommend that you check out Mr Excel - search through the forum, as someone somewhere has probably already had the same problem and had it resolved there...
posted by Chunder at 11:20 AM on December 1, 2005

I tried willnot's advice to no avail, and headed to Mr Excel as per Chunder's suggestion.

According to the advice on the forums, the behaviour I'm encountering is 'as designed' in Excel 2002 and cannot be changed. The VBA workaround I'm currently using is apparently the standard approach.

Thanks to both willnot and Chunder for their help!
posted by Ritchie at 10:06 PM on December 6, 2005