Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing a Chart's Series Source w/VBA (Excel 97)

    OK .. I need some direction ... I've exhausted my thimble of VBA expertise trying different combinations and can't get this to work. What I need to be able to do is to change the Sheet reference to a variable. The macro copies two hidden worksheets (a data worksheet and a predefined chart sheet). The data worksheet is empty of data at this point. The macro renames both sheets added based on a variable supplied on a form. The chart sheet series needs to reference the newly added/newly named data worksheet.

    ActiveChart.SeriesCollection(2).Values = "='Top $ Winners'!R3C4:R12C4"

    Any help will be greatly appreciated.
    Thanks in advance,
    Chance

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Chart's Series Source w/VBA (Excel 97)

    Hi Chance,

    If you have the sheet's name in a string variable called sSheetName:

    ActiveChart.SeriesCollection(2).Values = "='" & sSheetName & "'!R3C4:R12C4"

    Alternatively if it is a sheetobject variable called oSheet:

    ActiveChart.SeriesCollection(2).Values = "='" & oSheet.Name & "'!R3C4:R12C4"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Chart's Series Source w/VBA (Excel 97)

    Thanks Jan ... I knew it was a matter of syntax but no matter what combination I tried yesterday it failed. I will try this first thing this morning upon getting into work. Thank you so much for the help.

    Chris

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Chart's Series Source w/VBA (Excel 97)

    Interesting ... it wasn't the syntax used ... VBA couldn't change the series source nor the chart range because the worksheet had no data. We resolved it by putting in sample data in the first couple of rows, changed the source ranges, then the macro empties the worksheet data to prepare it for the user to load the live data.

    Thanks for your help, Jan

Posting Permissions

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