SeriesCollection.Delete not working in 2016

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

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,544
    Post Thanks / Like
    Mentioned
    1 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.

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
  •  

 

 
DMCA.com