"Lines on 2 Axes" fails on Excel 2007

[Sumit]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/21/2007 by susaxena
'

'
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F31")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).AxisGroup = 1
ActiveChart.SeriesCollection(1).Values = "={4,7,3,4,5,6}"
ActiveChart.SeriesCollection(1).Name = "=""a"""
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.SeriesCollection(2).Values = "={9,3,5,7,2,8}"
ActiveChart.SeriesCollection(2).Name = "=""b"""

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "SUMIT"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Axis on a"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Axis on b"
End With
End Sub
This Macro works fine on Excel 2003 but fails on Excel 2007.

which charttpye will provide similar functionality on Excel 2007?

 

[Andy Pope]

Hi,

Built-in chart types are not available in xl2007.
Instead just create a Line chart with markers. Your code already has the
syntax to set the AxisGroup of each series.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/21/2007 by susaxena
'

'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F31")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).AxisGroup = 1
ActiveChart.SeriesCollection(1).Values = "={4,7,3,4,5,6}"
ActiveChart.SeriesCollection(1).Name = "=""a"""
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.SeriesCollection(2).Values = "={9,3,5,7,2,8}"
ActiveChart.SeriesCollection(2).Name = "=""b"""

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "SUMIT"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Axis on a"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Axis on b"
End With
End Sub

Cheers
Andy