I have posted this a year ago on MrExcel, but without much response. I solved the issue then by putting in a short delay in
the code. This workaround has suddenly stopped being effective though. So I want to get to the root.
In the code I
create a graph (bars) then add an extra data set which I want to display as a horizontal line (XYscatter). Then I delete the
secondary axis information to get a clean graph. This method comes from Peltier.
Now this used to work fine under
Excel 2003, but when moving to 2010 it failed. Partly because the various chart items are addressed differently under
The version I came up with works perfectly when stepping through or even when running with the Editor open.
However when the editor is closed an errorbox comes up to say basically that the secondary axis doesn't exist and so it can't
set the properties.
initially I got by this by creating a delay between creating the new data set (for the line)
and telling it to display this on a secondary axis. But that doesn't always work, sometimes the message still comes up.
When youcomment out the Wait loop in the code the error becomes apparent.
ActiveSheet.ChartObjects("Chart 1").Activate ' activate current combined chart
ActiveChart.SeriesCollection(2).Delete ' delete horizontal line
create_NewSer ' recreate horizontal line
Sub create_NewSer() 'the macro that doesn't run under excel 2010, only step wise
Dim WAIT As Double
.Values = "=Sheet1!$B$9:$B$10"
.XValues = "=Sheet1!$A$9:$A$10"
WAIT = Timer
While Timer < WAIT + 0.01 'put in a delay. Used to work
DoEvents 'do nothing
Wend ' if you comment out this While loop the code will fail
.ChartType = xlXYScatterLinesNoMarkers ' se the new points to XY line
.ColorIndex = 17
.Weight = xlThin
.LineStyle = xlDash
With .Axes(xlCategory, xlSecondary) ' this is where the code fails with
' "the specified dimension is not valid for the chart type" and
' "Method Axis of object _Chart failed
.MajorTickMark = xlNone
.TickLabelPosition = xlNone
.MinimumScale = 0
.MaximumScale = 1
With .Axes(xlValue, xlSecondary)
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines