Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with Bubble series (2002)

    I have a procedure that attempts to delete all the current series of a bubble chart and then to recreate them with new values. It reads values down a filtered list, supplying the name x, and y values and size in four columns.
    The problem is it either fails to delete the series (Delete method of Series class failed) with the statement myChart.SeriesCollection(x).Delete - even though I can then delete them manually - or it is unable to set the Name property. Any ideas what might be happening? Thanks, Andy.

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

    Re: Working with Bubble series (2002)

    Can you post the code you are using, or a sample workbook?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Bubble series (2002)

    Attached sample workbook. The code runs on clicking the Create Bubble Chart button. It should then delete all current series, and create new ones based on the displayed filtered data on the Tier Summary sheet. Thanks, Andy.

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

    Re: Working with Bubble series (2002)

    The bubble chart in your workbook does have a data series, but it is a very strange one - it is based on partly empty data, and it cannot be selected manually: it doesn't appear in the Chart Objects dropdown list on the Chart toolbar. I assume that deleting it fails for the same reason. If you supply some valid values in the Chart | Source Data, Series tab, it becomes possible to delete the series either manually or in code.

    You try to create new series also based on partially missing data, that's why naming them fails.

    You should ensure that columns O, W and AD on Tier Summary (the ones used to provide the XValues, Values and BubbleSizes) are all populated. The code then runs OK.

    Note: you should set Application.ScreenUpdating to True at the end of the code, not to False.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Working with Bubble series (2002)

    Perhaps I don't completely understand what you are after.

    Why not set the bubble chart to be all the data in those columns? The charts will automatically only use the visible data after the data is filtered without having to use code to reset to just the subset of data.

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Bubble series (2002)

    Is it possible to set the visible data as chart series for a bubble chart? Each row needs to become a new series to achieve this..

    Do empty cells need to be populated with zeros to allow the creation of new series? Andy.

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

    Re: Working with Bubble series (2002)

    Excel will automatically use only visible data. So you can base the chart on the entire range, and it will be updated automatically when the user filters the data.

    Yes, empty cells must be populated with zeros.

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Bubble series (2002)

    Thank you. I believe it was the blank cells causing problems. Andy.

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

    Re: Working with Bubble series (2002)

    > I believe it was the blank cells causing problems

    It certainly was!

Posting Permissions

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