Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: SeriesCollection.Delete not working in 2016

  1. #1
    New Member
    Join Date
    Nov 2017
    Location
    United Kingdom
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SeriesCollection.Delete not working in 2016

    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?
    Code:
    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
    Code:
    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

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,754
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SeriesCollection.Delete not working in 2016

    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...

    Code:
            
    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.
    Using Excel 2016

  3. #3
    New Member
    Join Date
    Nov 2017
    Location
    United Kingdom
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SeriesCollection.Delete not working in 2016

    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.

  4. #4
    New Member
    Join Date
    Nov 2017
    Location
    United Kingdom
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SeriesCollection.Delete not working in 2016

    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.

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SeriesCollection.Delete not working in 2016

    Quote Originally Posted by 04nimmot View Post
    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:
    Code:
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •