Change Chart Series with VBA

S

Sueann

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top