dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] Excel VBA: HowTo: Update 1 Series of several on a Chart ???

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Resolved [RESOLVED] Excel VBA: HowTo: Update 1 Series of several on a Chart ???

    Esteemed Forum Participants and Lurkers:
    ===============================
    Excel 2003 VBA

    I have a workbook with 2 chart pages that I have been keeping for a few months. Basically, the 2 charts have been used to track my Systolic and Diastolic Blood Pressure. The 1st chart tracks BP over time (historic), and the 2nd chart tracks BP vs. Time of Day of reading. There is a Macro with a button that updated the chart after new data is entered.

    This morning I started taking meds to lower my BP and I want to overlay the new readings over the old readings on the same 2 charts. As I suspected, my new "Update" Macro, which now pulls data from a new worksheet, fouls up the old graphs.

    Here is the old code that updated the 2 graphs (2 series per chart sheet)
    Code:
        'Update the HISTORY chart
        Sheets("BP HISTORY").SetSourceData Source:=aSht.Range(aSht.Cells(2, 2), aSht.Cells(eRow, 4)), _
            PlotBy:=xlColumns
        
        'Update the BP vs. Time-Of-Day chart
        Sheets("BP vs. TIME-OF-DAY").SetSourceData Source:=aSht.Range(aSht.Cells(2, 9), aSht.Cells(eRow, 11)), _
            PlotBy:=xlColumns
    Now I have FOUR series per chart sheet - named "PreMedSys", "PreMedDias", "MedSys", and "MedDias". What can I do to the above code to ONLY UPDATE series "MedSys" and "MedDias", and NOT change the "PreMedSys" or "PreMedDias" series?

    Thank you for any and all comments, suggestions, and gracious assistance in this effort.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Excel VBA: HowTo: Update 1 Series of several on a Chart ???

    Can I have a peek at the workbook?

    or what you can do is record a macro for the same.
    Start the macro
    Right click on the chart and edit the sourcedata(add the series)
    Stop the macro
    Amend the code...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Excel VBA: HowTo: Update 1 Series of several on a Chart ???

    Thanks CoolSid ... I did use the Macro Recorder and I came up with a workable solution ... but it sure does complicate the code a LOT!

    The old single line:
    Code:
        'Update the HISTORY chart
        Sheets("BP HISTORY").SetSourceData Source:=aSht.Range(aSht.Cells(2, 2), aSht.Cells(eRow, 4)), _
            PlotBy:=xlColumns
    Gets replaced by the following, where "aSht" is the PreMed data and "bSht" is the PostMed Data, aRow and bRow are the end of Systolic data on the Pre and Post sheets ...
    Code:
    SNIPPET
        'Update the HISTORY chart
        'Extract the INDEX for the 4 series by name - make sure all 4 get processed
        j = 0
        With Sheets("BP HISTORY")
        For i = 1 To 4
            tStr = .SeriesCollection(i).Name
            Select Case tStr
                Case "PreMedSys"
                    'PreMed Time/Date Stamp X is in Column B, Systolic Y is in Column C
                    .SeriesCollection(i).XValues = aSht.Range(aSht.Cells(2, 2), aSht.Cells(aRow, 2))
                    .SeriesCollection(i).Values = aSht.Range(aSht.Cells(2, 3), aSht.Cells(aRow, 3))
                    j = j + 1
                Case "PreMedDias"
                    'PreMed Time/Date Stamp X is in Column B, Diastolic Y is in Column D
                    .SeriesCollection(i).XValues = aSht.Range(aSht.Cells(2, 2), aSht.Cells(aRow, 2))
                    .SeriesCollection(i).Values = aSht.Range(aSht.Cells(2, 4), aSht.Cells(aRow, 4))
                    j = j + 10
                Case "MedSys"
                    'PostMed Time/Date Stamp X is in Column B, Systolic Y is in Column C
                    .SeriesCollection(i).XValues = bSht.Range(bSht.Cells(2, 2), bSht.Cells(bRow, 2))
                    .SeriesCollection(i).Values = bSht.Range(bSht.Cells(2, 3), bSht.Cells(bRow, 3))
                    j = j + 100
                Case "MedDias"
                    'PostMed Time/Date Stamp X is in Column B, Diastolic Y is in Column D
                    .SeriesCollection(i).XValues = bSht.Range(bSht.Cells(2, 2), bSht.Cells(bRow, 2))
                    .SeriesCollection(i).Values = bSht.Range(bSht.Cells(2, 4), bSht.Cells(bRow, 4))
                    j = j + 1000
            End Select
        Next i
        End With
        'Report an ERROR if any of the Series Names were NOT processed
        If j <> 1111 Then MsgBox j & "  Not all 4 Series Names were processed on ""BP HISTORY""!": End
    This whole process ALSO has to be done for the "BP vs. TIME-OF-DAY" chart. I'll probably just do this with in-line code, but if I make this a function, I'll just need to pass the Sheet Name and the column ID with the "Time-of-Day" data.

    Is there a simpler way where the ".SetSourceData" method can be specified for an individual chart series? I tried but couldn't get it to work.
    Last edited by Webtest; Nov 7th, 2008 at 02:47 PM.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Excel VBA: HowTo: Update 1 Series of several on a Chart ???

    .SeriesCollection(i).XValues and .SeriesCollection(i).Values is the best way to get the data into the series... at least this is how I do it... It lets me get full control of the chart....
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Excel VBA: HowTo: Update 1 Series of several on a Chart ???

    Thanks for your help koolsid. It is all working now. One of these days I'll investigate all the nuances of ".SetSourceData" and see what it really can do.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  6. #6
    New Member
    Join Date
    Nov 2009
    Posts
    2

    Re: [RESOLVED] Excel VBA: HowTo: Update 1 Series of several on a Chart ???

    Check out http://vbacentral.blogspot.com for how to change chart series properties

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width