I am trying to use the custom chart, Line - column on 2 axes in vba code. I
couldn't get it to work so I tried using the Macro recoder. The following is
a macro that was recorded. However when I run it after it has been recorded I
get the following Error message, "RunTime Error 1004, Methods " 'Axes of
object - chart failed".

Is there some way that I can create this chart type on the fly using VBA? I
have a routine that creates a chart, "Chart Type, Column" with no problems.

'Macro recorded routine
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),
PlotBy:= _
xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
End Sub

I answered your later post, but for the archives I'll give a simple answer
here.

The macro recorder sometimes puts statements in the wrong order. You should
apply the chart type after the chart has been populated with data. In the
corrected macro below, note the new position of the ApplyCustomType comand.

Sub DoChart()
Charts.Add
ActiveChart.SetSourceData _
Source:=Sheets("Data").Range("B37:D61"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
With Selection.Interior
.ColorIndex = 36
.Pattern = 1
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"John" wrote in message
...
I am trying to use the custom chart, Line - column on 2 axes in vba code. I
couldn't get it to work so I tried using the Macro recoder. The following
is
a macro that was recorded. However when I run it after it has been
recorded I
get the following Error message, "RunTime Error 1004, Methods " 'Axes of
object - chart failed".

Is there some way that I can create this chart type on the fly using VBA?
I
have a routine that creates a chart, "Chart Type, Column" with no
problems.

'Macro recorded routine
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),
PlotBy:= _
xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
End Sub

Thank you for your responce I will try it out but your answer does make sense.

"Jon Peltier" wrote:

I answered your later post, but for the archives I'll give a simple answer
here.

The macro recorder sometimes puts statements in the wrong order. You should
apply the chart type after the chart has been populated with data. In the
corrected macro below, note the new position of the ApplyCustomType comand.

Sub DoChart()
Charts.Add
ActiveChart.SetSourceData _
Source:=Sheets("Data").Range("B37:D61"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
With Selection.Interior
.ColorIndex = 36
.Pattern = 1
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"John" wrote in message
...
I am trying to use the custom chart, Line - column on 2 axes in vba code. I
couldn't get it to work so I tried using the Macro recoder. The following
is
a macro that was recorded. However when I run it after it has been
recorded I
get the following Error message, "RunTime Error 1004, Methods " 'Axes of
object - chart failed".

Is there some way that I can create this chart type on the fly using VBA?
I
have a routine that creates a chart, "Chart Type, Column" with no
problems.

'Macro recorded routine
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),
PlotBy:= _
xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
End Sub