formatting a pivot chart: formatting is lost on change of page data

[obiron]

I have a pivot chart with 5 series and three page fields on it.
When the page fields are updated the chart is refreshed.

There is a 'bug' in pivot charts that means that the series are refreshed and all formatting on the series is lost. Not a problem in itself because I can reformat using a macro (code below).

 Application.ScreenUpdating = False
    ActiveChart.SeriesCollection(4).Select
    With Selection.Border
        .ColorIndex = 25
        .Weight = xlThick
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        .ColorIndex = 3
        .Weight = xlThick
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.SeriesCollection(3).Select
    With Selection.Border
        .ColorIndex = 46
        .Weight = xlMedium
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.SeriesCollection(5).Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlMedium
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.SeriesCollection(2).Select
    With Selection.Border
        .ColorIndex = 33
        .Weight = xlMedium
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    Application.ScreenUpdating = True

The problem is that I can't find an event to hang the macro on; The only one that seems to fire automatically is chart_calculate() but this throws me an error message

Run time error 104: select method of series class failed

on the following line (2nd line of macro)

    ActiveChart.SeriesCollection(4).Select

It seems that when the calculate fires, the chart has not rebuilt its series (although there are the correct number of items in the collection if you do a count...??)

How do I call the macro AFTER the cacluate has finished or is there another event that will always fire post calculate which I can capture when the series has been populated.

TIA

Obiron.

(if anyone can solve this, I can share with the user base a solution for cascading page fields on Excel 2003...)

 

[obiron]

I don't do this very often, but this is the final piece of some pretty hardcore vba that I want to share with the community so

BUMP....


Obiron