Thread: Help: runtime error - Method seriescollection object_chart failed

That's almost 300 lines of code to wade through. Any hints about which
line throws the error?

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


huangx06 wrote:

> Hello,
>
> I have a draw chart sub which generates a runtime error "method
> seriescollection object_chart failed" from time to time. It doesn't
> happen all the time, probaly around 15% of the time. Below is the full
> code. I'm running out of ideas now in terms of how to fix the problem.
> Please help!!
>
> Thanks a lot
> ---------------------------------------------------------------------------------------------------
> Sub drawChart(nYears As Integer)
>
> Dim TempChartObjA As ChartObject
> Dim TempChartObjB As ChartObject
> Dim TempChartObjC As ChartObject
> Dim TempChartObjD As ChartObject
> Dim chartCount As Integer
> Dim i As Integer
> Dim TempSeriesCollection As SeriesCollection
>
> Call drawCheckBox(nYears)
>
> chartCount = Worksheets("DashBoard").ChartObjects.Count
> If chartCount > 0 Then
> For i = 1 To chartCount
> Worksheets("DashBoard").ChartObjects(1).Delete
> Next i
> End If
>
> 'reset the average line check boxes
> Worksheets("DashBoard").cb_DailyAverage.Visible = True
> Worksheets("DashBoard").cb_DailyAverage.Value = False
> Worksheets("DashBoard").cb_DailyAveragePercent.Visible = False
> Worksheets("DashBoard").cb_DailyAveragePercent.Value = False
>
> Set TempChartObjA = Worksheets("DashBoard").ChartObjects.Add(0, 0,
> 700, 300)
> Set TempChartObjC = Worksheets("DashBoard").ChartObjects.Add(0, 0,
> 700, 300)
> Set TempChartObjB = Worksheets("DashBoard").ChartObjects.Add(0, 0,
> 700, 300)
> Set TempChartObjD = Worksheets("DashBoard").ChartObjects.Add(0, 0,
> 700, 300)
>
> TempChartObjA.Name = "chartGDM"
> TempChartObjC.Name = "chartGDMPercent"
> TempChartObjB.Name = "chartIFERC"
> TempChartObjD.Name = "chartIFERCPercent"
>
> TempChartObjA.Activate
> ActiveChart.chartType = xlLineMarkers
> TempChartObjC.Activate
> ActiveChart.chartType = xlLineMarkers
>
> If ifercHasCharts Then
> Worksheets("DashBoard").cb_MonthlyAverage.Visible = False
> Worksheets("DashBoard").cb_MonthlyAverage.Value = False
> Worksheets("DashBoard").cb_MonthlyAveragePercent.Visible =
> False
> Worksheets("DashBoard").cb_MonthlyAveragePercent.Value = False
>
> TempChartObjB.Activate
> ActiveChart.chartType = xlLineMarkers
> TempChartObjD.Activate
> ActiveChart.chartType = xlLineMarkers
> End If
>
>
> 'Add new series data to the series collection to charts
> For i = 0 To nYears
> ' chartGDM
> TempChartObjA.Activate
> 'On Error Resume Next
> 'Worksheets("DataMap").ShowAllData
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(i + 1).XValues =
> "=DataMap!dateAxisAll"
> ActiveChart.SeriesCollection(i + 1).Name = "Year " &
> Year(Now()) - i
> ActiveChart.SeriesCollection(i + 1).Values =
> "=DataMap!DataYear" & Year(Now()) - i
>
>
> ActiveChart.SeriesCollection(i + 1).Select
> With Selection.Border
> Weight = xlThin
> LineStyle = xlAutomatic
> End With
> With Selection
> MarkerBackgroundColorIndex = xlAutomatic
> MarkerForegroundColorIndex = xlAutomatic
> MarkerStyle = xlSquare
> Smooth = False
> MarkerSize = 3
> Shadow = False
> End With
>
> ' chartGDMPercent
> TempChartObjC.Activate
> 'On Error Resume Next
> 'Worksheets("DataMap").ShowAllData
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(i + 1).XValues =
> "=DataMap!dateAxisAll"
> ActiveChart.SeriesCollection(i + 1).Name = "Year " &
> Year(Now()) - i
> ActiveChart.SeriesCollection(i + 1).Values =
> "=DataMap!DataYearPercent" & Year(Now()) - i
>
> ActiveChart.SeriesCollection(i + 1).Select
> With Selection.Border
> Weight = xlThin
> LineStyle = xlAutomatic
> End With
> With Selection
> MarkerBackgroundColorIndex = xlAutomatic
> MarkerForegroundColorIndex = xlAutomatic
> MarkerStyle = xlSquare
> Smooth = False
> MarkerSize = 3
> Shadow = False
> End With
>
> If ifercHasCharts Then
> 'chartIFERC
> TempChartObjB.Activate
> 'On Error Resume Next
> 'Worksheets("DataMap").ShowAllData
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(i + 1).XValues =
> "=DataMap!ifercChartMonthAxis"
> ActiveChart.SeriesCollection(i + 1).Name = "Year " &
> Year(Now()) - i
> ActiveChart.SeriesCollection(i + 1).Values =
> "=DataMap!IFERCDataYear" & Year(Now()) - i
> ActiveChart.SeriesCollection
>
> 'chartIFERCPercent
> TempChartObjD.Activate
> 'On Error Resume Next
> 'Worksheets("DataMap").ShowAllData
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(i + 1).XValues =
> "=DataMap!ifercChartMonthAxis"
> ActiveChart.SeriesCollection(i + 1).Name = "Year " &
> Year(Now()) - i
> ActiveChart.SeriesCollection(i + 1).Values =
> "=DataMap!IFERCDataYearPercent" & Year(Now()) - i
> ActiveChart.SeriesCollection
> End If
> Next i
>
> TempChartObjA.Activate
>
> With ActiveChart
> HasTitle = True
> ChartTitle.Text = "Gas Daily Average Spread: Receiving " &
> Worksheets("SymbolMap").Range _
> ("longLoc") & "- Delivery " &
> Worksheets("SymbolMap").Range("shortLoc")
> End With
>
> ActiveChart.Axes(xlValue).TickLabels.NumberFormat =
> "$#,##0.0_);[Red]($#,##0.0)"
>
> ActiveChart.Axes(xlCategory).Select
> With Selection.Border
> ColorIndex = 57
> Weight = xlMedium
> LineStyle = xlContinuous
> End With
> With Selection
> TickLabelSpacing = 20
> MajorTickMark = xlNone
> MinorTickMark = xlNone
> TickLabelPosition = xlLow
> End With
> ActiveChart.Axes(xlValue).MajorGridlines.Select
> With Selection.Border
> ColorIndex = 57
> Weight = xlHairline
> LineStyle = xlGray50
> End With
>
> ActiveChart.PlotArea.Select
> With ActiveChart
> Axes(xlCategory, xlPrimary).HasTitle = True
> Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
> "Date"
> Axes(xlValue, xlPrimary).HasTitle = True
> Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Spread"
> End With
> With ActiveChart.Axes(xlCategory)
> HasMajorGridlines = True
> HasMinorGridlines = False
> End With
> With ActiveChart.Axes(xlValue)
> HasMajorGridlines = True
> HasMinorGridlines = False
> End With
> ActiveChart.Axes(xlCategory).MajorGridlines.Select
> With Selection.Border
> ColorIndex = 57
> Weight = xlHairline
> LineStyle = xlGray50
> End With
> With ActiveChart.Axes(xlCategory)
> CrossesAt = 1
> TickLabelSpacing = 20
> TickMarkSpacing = 20
> AxisBetweenCategories = True
> ReversePlotOrder = False
> End With
>
> '####################################################
> TempChartObjC.Activate
>
> With ActiveChart
> HasTitle = True
> ChartTitle.Text = "GDA Spread: Receiving " &
> Worksheets("SymbolMap").Range _
> ("longLoc") & "- Delivery " &
> Worksheets("SymbolMap").Range("shortLoc") _
> & " As a Percentage of Receiving Location "
> & Worksheets("SymbolMap").Range _
> ("longLoc")
> End With
>
> ActiveChart.Axes(xlValue).TickLabels.NumberFormat =
> "#,##0.0%;[Red](#,##0.0%)"
>
> ActiveChart.Axes(xlCategory).Select
> With Selection.Border
> ColorIndex = 57
> Weight = xlMedium
> LineStyle = xlContinuous
> End With
> With Selection
> TickLabelSpacing = 20
> MajorTickMark = xlNone
> MinorTickMark = xlNone
> TickLabelPosition = xlLow
> End With
> ActiveChart.Axes(xlValue).MajorGridlines.Select
> With Selection.Border
> ColorIndex = 57
> Weight = xlHairline
> LineStyle = xlGray50
> End With
>
> ActiveChart.PlotArea.Select
> With ActiveChart
> Axes(xlCategory, xlPrimary).HasTitle = True
> Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
> "Date"
> Axes(xlValue, xlPrimary).HasTitle = True
> Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "%
> Spread"
> End With
> With ActiveChart.Axes(xlCategory)
> HasMajorGridlines = True
> HasMinorGridlines = False
> End With
> With ActiveChart.Axes(xlValue)
> HasMajorGridlines = True
> HasMinorGridlines = False
> End With
> ActiveChart.Axes(xlCategory).MajorGridlines.Select
> With Selection.Border
> ColorIndex = 57
> Weight = xlHairline
> LineStyle = xlGray50
> End With
> With ActiveChart.Axes(xlCategory)
> CrossesAt = 1
> TickLabelSpacing = 20
> TickMarkSpacing = 20
> AxisBetweenCategories = True
> ReversePlotOrder = False
> End With
> '####################################################
> '##################################################'
> TempChartObjB.Activate
> 'code deleted
> ' TempChartObjD.Activate
> 'code deleted
> '################################################
> TempChartObjA.BringToFront
> TempChartObjC.Visible = False
> If ifercHasCharts Then
> TempChartObjB.Visible = False
> TempChartObjD.Visible = False
> End If
>
>
> Worksheets("DashBoard").OLEObjects("ChartType_List").Object.Selected(0)
> = True
> Application.ScreenUpdating = True
> End Sub
>
>

Hi Jon,

Thank you for looking at my problem. I have deleted those lines I think are not related to the problem. Can you take a look again? Again, my problem is totally random. It happens no more than 20% of the time.

Thanks again.

I think you deleted all of them <g>

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


huangx06 wrote:

> Hi Jon,
>
> Thank you for looking at my problem. I have deleted those lines I think
> are not related to the problem. Can you take a look again? Again, my
> problem is totally random. It happens no more than 20% of the time.
>
> Thanks again.
>
>