-
Nov 7th, 2008, 12:08 PM
#1
Thread Starter
Frenzied Member
[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
-
Nov 7th, 2008, 12:22 PM
#2
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Nov 7th, 2008, 02:12 PM
#3
Thread Starter
Frenzied Member
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
-
Nov 8th, 2008, 08:43 AM
#4
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Nov 10th, 2008, 08:04 AM
#5
Thread Starter
Frenzied Member
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
-
Dec 1st, 2009, 12:58 PM
#6
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|