Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Charts, auto formating and macros
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. -- To contact me take a davidhowdon and add a @yahoo.co.uk to the end. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Charts, auto formating and macros
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/ _______ David Howdon wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating and Macros | Excel Discussion (Misc queries) | |||
Formating Charts 2007 | Charts and Charting in Excel | |||
Conditional formating in Charts | Charts and Charting in Excel | |||
auto pivot tables and charts ? | Excel Programming | |||
Page Formating & Macros | Excel Programming |