Very wierd problem. Please help

[Francis]

I wrote a piece of code to generate a bunch of charts(>40) in a
worksheet. It was running smoothly and generate some beautiful charts.
However, last Thursday it stops due to a run-time error while running,
23 charts has been generated. The error message is "Unable to set the
HasTitle property of the Axis class". It is very wierd because the
code is in a loop and it pass the first 23 charts and block the 24th.

Then I tried some modification and thought the problem maybe related
to the font limitation in a chart. (see http://support.microsoft.com/kb/215573)
this DOESN'T work.

Then I try to run this code twice and triple. Find it is interesting
that at first, it stops at 24th. If you stop and re-run, it stops at
17th and give another error message, similar to "unable to set xxx
property of xxx class". If you stop and re-run for third time. It just
go through!!! What a damn!!! If you re-run for forth time, it stops at
24th again. then repeat.


Please help me, save me!


Following is the code.

Set cht1 = wsChart.ChartObjects.Add(posLeft, posTop, posWidth,
posHeight)

With cht1.Chart
.ChartType = xlLine

.SeriesCollection.NewSeries
.SeriesCollection(1).Name = "Projected Net Loss"
.SeriesCollection(1).Border.ColorIndex = 25
.SeriesCollection(1).Border.Weight = xlMedium
.SeriesCollection(1).Border.LineStyle = xlContinuous
.SeriesCollection(1).XValues = rngMonth
.SeriesCollection(1).Values = rngProLoss

.SeriesCollection.NewSeries
.SeriesCollection(2).Name = "Actual Net Loss"
.SeriesCollection(2).Border.ColorIndex = 3
.SeriesCollection(2).Border.Weight = xlThick
.SeriesCollection(2).Border.LineStyle = xlContinuous
.SeriesCollection(2).XValues = rngMonth
.SeriesCollection(2).Values = rngActLoss

.SeriesCollection.NewSeries
.SeriesCollection(3).Name = "PCG Projected Net Loss"
.SeriesCollection(3).Border.ColorIndex = 33
.SeriesCollection(3).Border.Weight = xlMedium
.SeriesCollection(3).Border.LineStyle = xlContinuous
.SeriesCollection(3).XValues = rngMonth
.SeriesCollection(3).Values = rngPCGProLoss

.Legend.Position = xlLegendPositionBottom

.HasTitle = True
.ChartTitle.Characters.Text = rngTitle.Value & " " &
rngTitle.Offset(0, 2).Value
.ChartTitle.Characters.Font.Size = 10
.ChartTitle.Font.Bold = True
.ChartTitle.Left = 35

.Axes(xlCategory, xlPrimary).HasTitle = True '<- Get error message
here.
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 9
.Axes(xlCategory, xlPrimary).CrossesAt = 1
.Axes(xlCategory, xlPrimary).TickLabelSpacing = 6
.Axes(xlCategory, xlPrimary).TickMarkSpacing = 6
.Axes(xlCategory, xlPrimary).TickLabels.Orientation = 90
.Axes(xlCategory, xlPrimary).TickLabels.Font.Size = 9
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Loss
$1000s"
.Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 9
.Axes(xlValue, xlPrimary).MinimumScale = 0
.Axes(xlValue, xlPrimary).TickLabels.Font.Size = 9

.Legend.Border.LineStyle = xlNone
.Legend.Font.Size = 9

'.PlotArea.Border.LineStyle = xlNone
.PlotArea.Interior.ColorIndex = xlNone
.Axes(xlValue, xlPrimary).MajorGridlines.Border.LineStyle = xlDot

Set cht1 = Nothing

End With

Thank you! any suggestion appreciated.

 

[paul.robinson]

Hi
I find that anything that involves updating charts can always do with
a sprinkling of DoEvents. So put

DoEvents

in after you do the .HasTitle = True. No harm to put it in after
other .HasTitle lines too.
I think (and it is a wild guess) that the code runs ahead trying to
manipulate the Title object before it is properly created. The
DoEvents forces the object to be properly created before you
manipulate it.
hope it helps!
Paul

I wrote a piece of code to generate a bunch of charts(>40) in a
worksheet. It was running smoothly and generate some beautiful charts.
However, last Thursday it stops due to a run-time error while running,
23 charts has been generated. The error message is "Unable to set the
HasTitle property of the Axis class". It is very wierd because the
code is in a loop and it pass the first 23 charts and block the 24th.

....