How to write a macro to modify an Excel chart
 

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
--
leave well enough alone

How to write a macro to modify an Excel chart
 

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?

--
Cheers,
Shane Devenshire


"raymondvillain" wrote:

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
--
leave well enough alone

How to write a macro to modify an Excel chart
 

Oops! Forgot to say Excel 2007. Thanks for your suggestion.
Henry
--
leave well enough alone


"ShaneDevenshire" wrote:

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?

--
Cheers,
Shane Devenshire


"raymondvillain" wrote:

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
--
leave well enough alone

How to write a macro to modify an Excel chart
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"raymondvillain" wrote in message
...
Oops! Forgot to say Excel 2007. Thanks for your suggestion.
Henry
--
leave well enough alone


"ShaneDevenshire" wrote:

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?

--
Cheers,
Shane Devenshire


"raymondvillain" wrote:

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
--
leave well enough alone

How to write a macro to modify an Excel chart
 

Hi,

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

--
Cheers,
Shane Devenshire


"raymondvillain" wrote:

Oops! Forgot to say Excel 2007. Thanks for your suggestion.
Henry
--
leave well enough alone


"ShaneDevenshire" wrote:

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?

--
Cheers,
Shane Devenshire


"raymondvillain" wrote:

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
--
leave well enough alone