Change Chart Series with VBA
 

Can anyone help? I am new to VBA

I have several chart sheets (they are not embedded) that I would lik
to change the series using vba instead of perfoming manually each tim
the data range changes.

I have found the following code from the link below to change char
series.
http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

with the code:


Code
-------------------
Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in sheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series

OldString = InputBox("Enter the string to be replaced:", "Enter old string")

If Len(OldString) 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oChart In ActiveSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Su
-------------------


However, does anyone have any code that automatically change the char
series(when the data range changes) without using input/message boxes?

Many thank

--
Suean
-----------------------------------------------------------------------
Sueann's Profile: http://www.excelforum.com/member.php...fo&userid=3443
View this thread: http://www.excelforum.com/showthread.php?threadid=54195

Change Chart Series with VBA
 

You don't need VBA for this kind of dynamic chart behavior. You can set up
dynamic data ranges that update their definitions when the amount of data
changes, and use these ranges for the chart source data. Here are a few
examples and links to mo

http://www.peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Sueann" wrote in
message ...

Can anyone help? I am new to VBA

I have several chart sheets (they are not embedded) that I would like
to change the series using vba instead of perfoming manually each time
the data range changes.

I have found the following code from the link below to change chart
series.
http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

with the code:


Code:
--------------------
Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in sheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series

OldString = InputBox("Enter the string to be replaced:", "Enter old
string")

If Len(OldString) 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oChart In ActiveSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString,
NewString)
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Sub
--------------------


However, does anyone have any code that automatically change the chart
series(when the data range changes) without using input/message boxes?

Many thanks


--
Sueann
------------------------------------------------------------------------
Sueann's Profile:
http://www.excelforum.com/member.php...o&userid=34430
View this thread: http://www.excelforum.com/showthread...hreadid=541957

Change Chart Series with VBA
 

Thanks for the link Jon, my charts are now updating automatically.


--
Sueann
------------------------------------------------------------------------
Sueann's Profile: http://www.excelforum.com/member.php...o&userid=34430
View this thread: http://www.excelforum.com/showthread...hreadid=541957