Pivot Charts, auto formating and macros

[David Howdon]

I'm trying to deal with the fairly well known problem of pivot charts
resetting their formatting whenever the data is refreshed.

I have a data series which I have graphed as a column chart (or vertical
bar chart if you prefer), however because it has a lot of data points
the columns are very thing and the colour of them cannot be seen because
of their borders.

Easy enough to fix, I simply remove the borders. So I recorded a macro
when I did this which generated the following

Sub Macro1()
Attribute Macro1.VB_ProcData.VB_Invoke_Func = " \n14"
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Interior.ColorIndex = xlAutomatic
End Sub

However to make this work more generally on charts with multiple series
I wanted a macro that would remove the borders for however many data
series i had. So I wrote.


Sub Macro2()
Dim Item As Series
For Each Item In SeriesCollection
Item.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Interior.ColorIndex = xlAutomatic
Next Item
End Sub

However this did not work giving RunTime Error 424

Obviously I am missing something. Could anyone let me know how to
achieve what I want. Also (and since I am trying to learn VB perhaps
more importantly) could someone explain what I was doing wrong. Thanks.

On a related note I'm currently learning VB using John Walkenbach's
"Excel 2002 Power Programming with VBA". It seems quite usable so far
but since it is always nice to have other options does anyone have
suggestions for other good books on VBA for Excel 2002?

Thanks again.

 

[Jon Peltier]

David -

Let me tweak your second macro:

Sub Macro2()
Dim srs As Series
For Each srs In ActiveChart.SeriesCollection
With srs
With .Border
.Weight = xlThin
.LineStyle = xlNone
End With
'.Shadow = False
'.InvertIfNegative = False
'.Interior.ColorIndex = xlAutomatic
End with
Next srs
End Sub

I've commented out the last three items inside With srs/End With because
they look redundant to me. Also, don't waste time selecting each series,
you can fully reference it without using the Selection object.

An alternative book for Excel VBA is "Excel 2002 VBA" (don't get "Excel
2003 VBA") by Bullen, Green, Bovey, and Rosenberg.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______