Secondary axis with VBA

  • Thread starter Thread starter tubbinator
  • Start date Start date
T

tubbinator

I'm using the following VBA code and I never see the secondary value
axis. Can anyone figure out why???

Excel 2003, VBA

Thanks,
Tubbs


Code:
--------------------
' create the chart
ActiveSheet.ChartObjects.Add Left:=50, Top:=50, Width:=750, Height:=300

With ActiveSheet.ChartObjects(1).Chart

' create first series, Magnitude
.SeriesCollection.NewSeries

With .SeriesCollection(1)
.Name = ActiveSheet.Range("D1").Value
.Values = ActiveSheet.Range("D2:D" + Format(linecount, "#"))
.XValues = ActiveSheet.Range("A2:A" + Format(linecount, "#"))
.AxisGroup = xlPrimary
End With

' create second series, Sync
.SeriesCollection.NewSeries

With .SeriesCollection(2)
.Name = ActiveSheet.Range("H1").Value
.Values = ActiveSheet.Range("H2:H" + Format(linecount, "#"))
.XValues = ActiveSheet.Range("A2:A" + Format(linecount, "#"))
.AxisGroup = xlPrimary
End With

' create third series, Phase
.SeriesCollection.NewSeries

With .SeriesCollection(3)
.Name = ActiveSheet.Range("E1").Value
.Values = ActiveSheet.Range("E2:E" + Format(linecount, "#"))
.XValues = ActiveSheet.Range("A2:A" + Format(linecount, "#"))
.AxisGroup = xlSecondary
End With



For Each ax In .Axes
'If Not ((ax.Type = xlCategory) And (ax.AxisGroup = xlSecondary)) Then
ax.HasTitle = True
With ax.AxisTitle
.Font.Name = "Arial"
.Font.Size = 10.75
.Font.Bold = True
End With
'End If
Next ax

With .Axes(xlValue, xlSecondary).AxisTitle
.Caption = "Phase (Degrees)"
.Orientation = 90
End With

With .Axes(xlValue, xlPrimary).AxisTitle
.Caption = "Magnitude (mV)"
.Orientation = 90
End With

With .Axes(xlCategory, xlPrimary).AxisTitle

.Caption = "Sample Number"

End With
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = False
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
.ChartType = xlLine

End With 'chart
 
Didn't you step through the code? Your chart had a secondary axis until the end when
you ran this line:

.ChartType = xlLine

The xlLine chart type by default has only primary axes, so running this line
obliterated the axis you worked so hard to construct. Move the line to the top,
right after this:

With ActiveSheet.ChartObjects(1).Chart

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
You replied to the wrong thread.

Sometimes putting .ChartType up too high will cause an error. If you specify a
bubble of stock chart type before specifying enough data to populate that chart
type, it will crash.

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