SeriesCollection.Delete not working in 2016

[04nimmot]

I've not posted to a forum for Excel previously, normally my problems are solved by past answers or persistence, but this is a strange one.

I have a chart in Excel where series can be added and removed dynamically using check boxes. It works perfectly in Excel 2010, however in Excel 2016 the application stops responding and crashes when removing the series, adding a series still works correctly.

Any ideas? Is anybody aware of SeriesCollection.Delete not working in 2016?

Private Sub chkSalesTotMtd_Change()
    Dim sheet As String
    Dim chtName As String
    Dim chkValue As Variant
    Dim chkBox As String
    Dim linename As String
    Dim valuefield As String
    Dim linenameabr As String
    
    sheet = "ChtDealerMonth"
    chkBox = "chkSalesTotMtd"
    linename = "=""Sales Total Mtd"""
    linenameabr = "Sales Total Mtd"
    valuefield = "=BalanceSplit.xlsm!SalesTotMtdDlr"
    Call unprotectsheetsdlr
    Dim objChart As ChartObject
    For Each objChart In Sheets("ChtDealerMonth").ChartObjects
        chtName = objChart.Name
        Call addnewseriessecond(sheet, chkBox, chtName, linename, linenameabr, valuefield)
    Next objChart
    Call protectsheetsdlr
End Sub

Function addnewseries(sheet As String, chkBox As String, chtName As String, linename As String, linenameabr As String, valuefield As String)
    ActiveSheet.Unprotect
    chkValue = Sheets(sheet).OLEObjects(chkBox).Object.Value
   
    ActiveSheet.ChartObjects(chtName).Activate
    If chkValue = True Then
        'if checkbox is ticked
        Dim NewSrs As Series
        Set NewSrs = ActiveChart.SeriesCollection.NewSeries
        'add new series to chart
        With NewSrs
            .Name = linename
            .Values = valuefield
            .ChartType = xlLine
            .Format.Line.Weight = 2.25
         End With
     ElseIf chkValue = False Then
        'if checkbox unticked
        Dim IndexNum As Integer
        Set cht = ActiveChart.SeriesCollection
        'find index of series which matches name of series to remove
        For i = 1 To cht.Count
            If cht(i).Name = linenameabr Then IndexNum = i
        Next i
        'delete series from chart
        cht(IndexNum).Delete
    End If
End Function

 

[Jerry Sullivan]

Welcome to MrExcel,

That code seems to work for me if a series with a matching name was found.

It could be made a little more robust by only attempting to delete the series after a match is found...

For i = 1 To cht.Count
   If cht(i).Name = linenameabr Then 
      IndexNum = i
      'delete series from chart
      cht(IndexNum).Delete
      Exit For
   End If
Next i

In your current code, the .Delete is outside the loop. So if no match is found, Excel will attempt to delete cht(0). That might have cause your crash- although that should only occur if no matching series is found.

 

[04nimmot]

Thanks Jerry, the series definitely exists for the user having issues.

Saying that, I will make an amendment to the code as you suggest.
Although it shouldn't be possible for the user to try and delete a non-existent series, users are users and will find some magical way of doing it.

 

[04nimmot]

I've had the spreadsheet tested on multiple terminals, and it only seems to be failing on one. I'm requesting that IT reinstall the user's excel. Fingers crossed.

 

[mratontorron]

Is anybody aware of SeriesCollection.Delete not working in 2016?

Yes. In my case, when I attempt to delete a SeriesCollection that uses a Secondary Axis. The code in my procedure will run to the end as usual (also past the .Delete command) but Excel 2016 will crash upon exiting it.

I manage to reproduce the error from scratch in a stripped down example:

Sub abrclear_chart()
        Do While 1 < ActiveSheet.ChartObjects(1).Chart.SeriesCollection.Count
           MsgBox ("Next I will try and delete Series """ & ActiveSheet.ChartObjects(1).Chart.SeriesCollection(2).Name & """!")
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(2).Delete
        Loop
        MsgBox ("Whatever you did, it worked this time!")
        MsgBox (ActiveSheet.Name & "... or did it?!")
End Sub

I ran this identical code on 2 separate sheets, each with a single chart with at least 2 series.
The first chart has both series on the Primary Axis, the second chart has the second series on the Secondary Axis.

Both times the code will run through to the end (all MsgBoxes pop up), but Excel 2016 crashes after processing the second chart.