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

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



"John" <(E-Mail Removed)> wrote in message
news:14CC2D0E-6C4E-4A67-8E88-(E-Mail Removed)...
>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
>

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("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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
>
> "John" <(E-Mail Removed)> wrote in message
> news:14CC2D0E-6C4E-4A67-8E88-(E-Mail Removed)...
> >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
> >
>
>
>