Pivot charts for brains

[kate]

I have already asked questions regarding Pivot charts and I am still at a
loss, I have been told to run a macro to keep my color formats however I am
not much good at VBA. My chart is in a sheet underneath my pivot table and I
have been using
Private Sub Chart_Calculate()
With ActiveChart.SeriesCollection(3).Interior
.ColorIndex = 37
.Pattern = xlSolid
End With

the above to begin with but I know the private sub is in correct but what
should it be for a pivot chart?? also what does With
ActiveChart.SeriesCollection(3).Interior ??? mean I have looked on the
internet but is shows no examples and on this forum it only provides a link
to a support which doesnt give me any better indication.
Once i have resolved this I then also would like to know is it possible to
macro the chart for axis size as I am trying to compare two charts on the
same axis size.


Thanks
Kate

 

[Jon Peltier]

You need to find a tutorial on VBA. But basically...

ActiveChart is the active chart, in this case your pivot chart. However, if
the chart isn't active, it will not work properly. Instead of using
ActiveChart, you need to reference the correct chart. Since the
Chart_Calculate code is on the code module of the chart, you can simply use
"Me". All the plotted series in a chart are collected into the
SeriesCollection, and SeriesCollection(3) means the third series. Interior
refers to the interior of the columns, and ColorIndex means use the 37th
color out of the 56 Excel allows. Pattern is redundant, since xlSolid is the
default. With/End With is a shorthand way to identify an object, then do a
bunch of operations with that object. All the different bits are connected
by dots, the way folders and files on your hard drive are listed with back
slashes.

So right click the chart tab, choose View Code, and you'll see the code
controlling the chart. The procedure can be rewritten to:

Private Sub Chart_Calculate()
With Me.SeriesCollection(3).Interior
.ColorIndex = 37
End With
End Sub

or

Private Sub Chart_Calculate()
Me.SeriesCollection(3).Interior.ColorIndex = 37
End Sub

since you're only doing one thing within the With/End With.

This is how the whole object model works. To get the right syntax, record a
macro, and merge the new recorded pieces in with the existing procedure
above.

- Jon

 

[Andy Pope]

Hi Jon,

Is Kate's main problem the fact that she has a chartobject rather than a
chartsheet. In which case the Chart_Calculate event does not happen.

You need to move the code into a standard module and remove the Private
keyword.
Then in a worksheet event you can call the routine, making any of the
suggestions Jon made in regards to the actual formatting of the chart.

Cheers
Andy

 

[kate]

Thanks both Andy and Jon,
It is working fine I am very happy,
You are right I do need a to find some tutorial but thanks so much for your
timexx