Using VBA to put a chart series on the secondary X axis

Question

I have a line chart to which I'm attempting to add, via VBA, a horizontal line at a given Y value. I'm following this guide, which suggests adding a line to an existing chart and then changing its type to scatter, which causes Excel to put the series on the secondary X axis automatically. It can then be formatted further.

My problem is that when doing this with VBA I don't understand how to switch a line onto the secondary X axis. By default all new lines will go on the primary X axis.

For example, suppose chrt is a Chart object:

' Enable secondary X axis (I think)
chrt.HasAxis(XlAxisType.xlCategory, XlAxisGroup.xlSecondary) = True

Dim se As Series
Set se = chrt.SeriesCollection.NewSeries
se.ChartType = xlXYScatterLinesNoMarkers

' Add a line at Y = 0
se.xValues = Array(0, 1)
se.values = Array(0, 0)

Now, how to get se onto the secondary X axis?

what version of Excel are you using? Can you try the following answer and comment. The code below is only to make the line to have secondary axis. So I assume everything else is fine in your code which is not shown here.. — bonCodigo
You have to assign at least one series to AxisGroup xlSecondary. Excel may automatically add secondary axis or axes; generally it does add the secondary value axis and often not the secondary category axis. — Jon Peltier

bonCodigo bonCodigo · Accepted Answer · 2013-01-13T13:21:43

I followed this link for Excel 2003 and recorded a macro. Please note that for Excel 2007 charts have such a unfortunate cases with recording macros. But you get lucky in Excel 2010 again. So watch out for your version if you are going to do it based on a macro and then copy the code...

Change sheet and chart name according to yours.

With Sheets(3).ChartObjects("Chart 1")
     .SeriesCollection(2).AxisGroup = xlSecondary
     .HasAxis(xlCategory, xlPrimary) = True
     .HasAxis(xlCategory, xlSecondary) = True
     .HasAxis(xlValue, xlPrimary) = True
     .HasAxis(xlValue, xlSecondary) = True
     .Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
     .Axes(xlCategory, xlSecondary).CategoryType = xlAutomatic
  End With

Using VBA to put a chart series on the secondary X axis

1 Answers