Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    save restore chart series excel (Excel 2003 et al.)

    I am faced with the challenge of "toggling series" in an excel chart, and find it such a good challenge that I thought to share it here.
    I suspect this will be a case of "half-designed" again.
    <pre>Public serAr(5) As Series
    Public strSer(5) As String
    Function SaveAndHideSeries(strSer() As String, cht As Chart, strSeriesName As String)
    Dim lngSeries As Long
    lngSeries = lngIndexToSeries(strSer, strSeriesName)
    Dim lng As Long
    For lng = ActiveChart.SeriesCollection.Count To 1 Step -1
    If strSeriesName = ActiveChart.SeriesCollection(lng).Name Then
    Set serAr(lngSeries) = ActiveChart.SeriesCollection(lng)
    lngSeries = lngSeries + 1 ' for the second of the pair
    ActiveChart.SeriesCollection(lng).Delete
    Else
    End If
    Next lng
    End Function</pre>

    The gist of my idea was to maintain two parallel arrays, a string array and a "series" array, and when the user toggles OFF a series, I would store that series as an object in the series array before deleting the series from the chart.

    When the user toggles ON the series, I thought, I'd retrieve the series from the series array, and just ActiveChart.Add it.
    But the VBA help seems to indicate that to .Add a series to a SeriesCollection I must quote range, labels, mother's maiden name etc; it seems to me that one can't just ActiveChart.Add mySeries.

    When I examine a retrieved series (screen snapshot), it seems to be unassigned in any way; trying to access any property results in "Object Required".

    My comment "half-designed" refers to what seems to me to be the case, that one can easily preserve a "series" as an object, but cannot re-site it in the original place as an object.
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: save restore chart series excel (Excel 2003 et al.)

    The Add method of the SeriesCollection object returns a Series object, it does *not* take a Series object as argument. There is no way to add a stored Series object to the SeriesCollection of a chart.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: save restore chart series excel (Excel 2003 et al.)

    (half an hour later)
    This is why I *love* the lounge.
    Having unburdened myself, my mind cleared up.
    It appears that hiding the appropriate ROW of data in the worksheet is sufficient to hide the line on the chart.

    The client doesn't really want the data hidden, but it's 4:10 p.m. Friday, so this will do for now.
    Although I still think I should be able to juggle a series in a chart, rather than fudging it at the data source.

    (saw the post) Thanks, Hans.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: save restore chart series excel (Excel 2003 et al.)

    I'm feeling particularly stupid now:<pre>Sub Macro7()
    With Selection.Border
    .LineStyle = xlNone
    End With
    With Selection
    .MarkerStyle = xlNone
    End With
    End Sub
    Sub Macro9()
    With Selection.Border
    .LineStyle = xlAutomatic
    End With
    With Selection
    .MarkerStyle = xlAutomatic
    End With
    End Sub</pre>


    It seems to be sufficient to toggle the line style and marker style.

Posting Permissions

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