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. |
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. |
All times are GMT +1. The time now is 09:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com