Run-time error '91' related to Chart Object

[Lenna]

The following code creates a new chart each time new data is imported into a worksheet “Graph_QC”. I get Run-time error ‘91’ @ Set objChartSeriesColl = objChart.Chart.SeriesCollection when I run Add_ChartSeries code. Add_ChartSeries adds new series to the chart.

The code is executed in the following order:

1. Old chart is deleted with DeleteallCharts
2. New data is imported (code not included)
3. New chart is created with Build_Chart
4. User selects items in the Listbox(UserForm) to graph Add_ChartSeries
5. Data series are added with Add_ChartSeries

User may select other items to graph and rerun Add_ChartSeries code.

It seems after I play around with the chart and the error goes away and I’m able to run Add_ChartSeries as it is, however, it always throws an error the first time I ran the code after the new data is imported and new chart is generated. Please help fix the code so it always works.

Thank you so much for your time.

Dim objChart As ChartObject

Sub DeleteallCharts()

Dim chtObj As ChartObject
For Each chtObj In ActiveSheet.ChartObjects
chtObj.Delete
Next

End Sub

Sub Build_Chart()

'builds a chart on active sheet
Worksheets("Graph_QC").Select


Set objChart = ActiveSheet.ChartObjects.Add _
(Left:=30, Width:=775, Top:=15, Height:=345)
objChart.Chart.ChartType = xlXYScatterLines


End Sub


Sub Add_ChartSeries()


Dim i As Long, l As Long
Dim yAddress_ListItem As String, yAddress_ValuesRange As String
Dim xAddress_ValuesRange As String, xAddress_ListItem As String
Dim cht As Chart
Dim rng As Range, aCell As Range
Dim MyArY() As Variant, MyArX() As Variant
Dim LastRow As Long, iVal As Long
Dim s As SeriesCollection
Dim ChartMin As Long, ChartMax As Long


Application.EnableEvents = False
'Dim chSeries As Series
Worksheets("Graph_QC").Select


Dim objChartSeriesColl As SeriesCollection


Set objChartSeriesColl = objChart.Chart.SeriesCollection


'delete all chart series
 
 Sheets("Graph_QC").ChartObjects(1).Chart.ChartArea.ClearContents




With ActiveSheet 'change to relevant sheet?
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B26:B" & LastRow) 'non-contiguous range
End With


 
With Application.WorksheetFunction
    Range("E26").Value = .MIN(Range("D26:D" & LastRow).SpecialCells(xlCellTypeVisible))
    Range("F26").Value = .Max(Range("D26:D" & LastRow).SpecialCells(xlCellTypeVisible))
End With
    
     Range("E26").NumberFormat = "m/d/yy;@"
     Range("F26").NumberFormat = "m/d/yy;@"
     ChartMin = Range("E26").Value
     ChartMax = Range("F26").Value
    
'Set objChartSeriesColl = objChart.Chart.SeriesCollection


'For i = 0 To Me.ListBox2.ListCount - 1


If UserForm1.lstMain.ListIndex <> -1 Then 'if listbox is NOT empty
For l = 0 To UserForm1.lstMain.ListCount - 1
If UserForm1.lstMain.Selected(l) Then 'identify selected items
' count of cells in that range meeting criteria
iVal = Application.WorksheetFunction.CountIf(rng, UserForm1.lstMain.List(l))
            
' Resize arrays to hold filtered data
ReDim MyArY(1 To iVal)
ReDim MyArX(1 To iVal)


iVal = 1


' Store filtered values from that range into array
For Each aCell In rng.Cells
If aCell.Value = UserForm1.lstMain.List(l) Then
MyArY(iVal) = aCell.Offset(0, 1).Value
'Converts Date to Value/Double then to Integer to truncate time
MyArX(iVal) = Int(CDbl(aCell.Offset(0, 2).Value))
'MyArX(iVal) = aCell.Offset(0, 2).Value
iVal = iVal + 1
End If
Next aCell
            


xAddress_ListItem = UserForm1.lstMain.List(l) '.Value
'defines series name


With objChartSeriesColl.NewSeries 'adds each? Series
.Name = xAddress_ListItem
.Values = MyArY
.XValues = MyArX
'.ApplyDataLabels
'.DataLabels.Position = xlLabelPositionAbove
'.DataLabels.NumberFormat = "0"
End With
        End If
    Next
End If


Worksheets("Graph_QC").Select
'objChart.HasTitle = True
ActiveSheet.ChartObjects(1).Activate


With ActiveChart
    .Axes(xlCategory).TickLabels.NumberFormat = "m/d/yyyy" 'changes Xaxis text format
    .Axes(xlValue).TickLabels.NumberFormat = "General" 'changes Yaxis Text Format
    .SetElement (msoElementChartTitleAboveChart) 'adds chart title above chart
    .SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis) 'adds Xaxis title
    .SetElement (msoElementPrimaryValueAxisTitleRotated) 'adds rotated Yaxis Title
    .SetElement (msoElementLegendBottom) 'adds legend @ bottom
    .ChartTitle.Text = "QC"  'adds chart title above chart
    .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Sample Dates" 'renames Xaxis title to "X Title"
    '.Axes(xlValue, xlPrimary).AxisTitle.Text = "Sample Dates" 'renames Xaxis title to "X Title"
    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent Alt" 'renames Yaxis title to "Y Title"
    
    .Axes(xlCategory).MinimumScale = ChartMin
      
    .Axes(xlCategory).MaximumScale = ChartMax
    
End With


With ActiveChart.ChartArea.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0.3399999738
        .ForeColor.Brightness = 0
        .BackColor.ObjectThemeColor = msoThemeColorAccent1
        .BackColor.TintAndShade = 0.7649999857
        .BackColor.Brightness = 0
        .TwoColorGradient msoGradientHorizontal, 1
    End With
With ActiveChart.PlotArea.Format.Line 'adds black border around plot
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
End With


With ActiveChart.Legend.Format.Line 'adds black border around legend
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
End With


  Application.EnableEvents = True
  
End Sub

 

[Lenna]

I also get an error "Method 'Set Element of object chart failed" @ .SetElement (msoElementChartTitleAboveChart) 'adds chart title above chart but not always. Thanks