Chart_Calculate event - code looping

[RobH1979]

Hi

I've got a pivot chart in Excel 2000, but have the common problem that I can't change the format of the chart because it re-sets itself to it's original format each time it updates. I need to put a series on a second y axis.

I've been using the Chart_Calculate event to make the change to the second y axis, so that everytime the chart is updated it will apply my desired formatting rather than reverting to the standard. However the code (below) loops on every update. When it reaches the '.TickLabel' line is go straight back to the 'Private Sub' line. I assume therefore that changing the number format is forcing the chart to re-calculate. Is there anyway I can get round this?

Private Sub Chart_Calculate()
'Put % series on secondary Y axis and make axis match series
ActiveChart.SeriesCollection(3).AxisGroup = 2
With ActiveChart.Axes(xlValue, xlSecondary)
.HasTitle = True
.AxisTitle.Characters.Text = "Percentage of Callout Charges >10% above mean"
.TickLabels.NumberFormat = "0.0%"
End With

End Sub

Thanks
Rob

 

[RobH1979]

Sorted this myself. Put Application.EnableEvents = FALSE before the code and turn it back to TRUE afterwards