How to write a macro to modify an Excel chart

[raymondvillain]

I want to "record" a macro that, for example, changes the line styles on an
Excel chart. I tried by selecting the chart, then clicking on "record
macro", manually changing the line style, and then clicking on "stop
recording". But then when I tried to use the macro it wouldn't do anything.
When I edited the macro there was no code, just the comment lines giving the
shortcut.

Is it possible to use the "record" feature for macros that work with charts,
or must one start with the VB editor?
Thanks,
Henry

 

[ShaneDevenshire]

Hi,

Here is the macro I get when starting in the spreadsheet:

Sub FormatChartLine()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlThin
.LineStyle = xlDash
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
End Sub

Starting on the chart produces something like this:

Sub Macro2()
With Selection.Border
.ColorIndex = 57
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlDiamond
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
End Sub

What version of Excel are you using?

 

[raymondvillain]

Oops! Forgot to say Excel 2007. Thanks for your suggestion.
Henry

 

[Jon Peltier]

Unfortunately, the Excel 2007 macro recorder doesn't record too much when
you are editing charts (and nothing at all when you are editing shapes). I
think they were out of time when 2007 was being released, and couldn't
upgrade it for the new Office shapes.

- Jon

 

[ShaneDevenshire]

Hi,

In that case its going to be more work because you will need to write the
macro manually.