Chart Error when using Chart Line - Column on 2 Axes in vba code

[John]

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("B3761"),
PlotBy:= _
xlColumns
ActiveChart.Location Where:=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

 

[Jon Peltier]

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("B3761"), PlotBy:=xlColumns
ActiveChart.Location Where:=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

 

[JohnTReed]

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