Change Series Formulas for all charts on worksheet

U

usmc-r70

I have a workbook with a 'template' worksheet that has 4 charts that
reference data contained only in this worksheet .

When I copy the 'template' and rename it (say Project1), the 'Series
Values', 'Series Name', and 'Title' reference in the formula bar continue
referencing the 'template' worksheet.

I found the code shown below and it works for chart 'Series Values', 'Series
Name', but NOT the chart 'Title' that I have referenced in the formula bar.
Credit to: http://peltiertech.com/Excel/Charts/ChgSrsFmla.html#ixzz0KCalAl7L&D

How can I modify the code below to:

1. Include the 'Title' reference shown in the formula bar.

2. Instead of typing the new worksheet name into the dialog box, have the
code reference $AC$37 which has the formula: =MID(CELL("filename",B31),
FIND("]",CELL("filename",B31))+1,255).

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


http://peltiertech.com/Excel/Charts/ChgSrsFmla.html#ixzz0KCalAl7L&D
 
S

Shane Devenshire

Hi,

Choose Edit, Links, and Change Source to your current workbook. May be able
to dispense with the macro using this technique.
 
U

usmc-r70

I am using Excel 2007. All references to 'Edit, Links, and Change Source' is
'grayed out'.

This code works perfectly on another computer using a simplified version
written in Excel 2007.



Shane Devenshire said:
Hi,

Choose Edit, Links, and Change Source to your current workbook. May be able
to dispense with the macro using this technique.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


usmc-r70 said:
I have a workbook with a 'template' worksheet that has 4 charts that
reference data contained only in this worksheet .

When I copy the 'template' and rename it (say Project1), the 'Series
Values', 'Series Name', and 'Title' reference in the formula bar continue
referencing the 'template' worksheet.

I found the code shown below and it works for chart 'Series Values', 'Series
Name', but NOT the chart 'Title' that I have referenced in the formula bar.
Credit to: http://peltiertech.com/Excel/Charts/ChgSrsFmla.html#ixzz0KCalAl7L&D

How can I modify the code below to:

1. Include the 'Title' reference shown in the formula bar.

2. Instead of typing the new worksheet name into the dialog box, have the
code reference $AC$37 which has the formula: =MID(CELL("filename",B31),
FIND("]",CELL("filename",B31))+1,255).

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


http://peltiertech.com/Excel/Charts/ChgSrsFmla.html#ixzz0KCalAl7L&D
Click to expand...
Click to expand...
 
S

Shane Devenshire

Hi,

When you click the chart title what is the formula you see on the Formula Bar?

In 2007 the Edit links command is on the Data tab which I suppose you found.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


usmc-r70 said:
I am using Excel 2007. All references to 'Edit, Links, and Change Source' is
'grayed out'.

This code works perfectly on another computer using a simplified version
written in Excel 2007.



Shane Devenshire said:
Hi,

Choose Edit, Links, and Change Source to your current workbook. May be able
to dispense with the macro using this technique.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


usmc-r70 said:
I have a workbook with a 'template' worksheet that has 4 charts that
reference data contained only in this worksheet .

When I copy the 'template' and rename it (say Project1), the 'Series
Values', 'Series Name', and 'Title' reference in the formula bar continue
referencing the 'template' worksheet.

I found the code shown below and it works for chart 'Series Values', 'Series
Name', but NOT the chart 'Title' that I have referenced in the formula bar.
Credit to: http://peltiertech.com/Excel/Charts/ChgSrsFmla.html#ixzz0KCalAl7L&D

How can I modify the code below to:

1. Include the 'Title' reference shown in the formula bar.

2. Instead of typing the new worksheet name into the dialog box, have the
code reference $AC$37 which has the formula: =MID(CELL("filename",B31),
FIND("]",CELL("filename",B31))+1,255).

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


http://peltiertech.com/Excel/Charts/ChgSrsFmla.html#ixzz0KCalAl7L&D
Click to expand...
Click to expand...
Click to expand...
 
U

usmc-r70

In the formula bar: =TEMPLATE!$AC$62 and without data one chart reads
#DIV/0! , with the other reading #N/A .

When I click outside the chart all the Data tab functions are highlighted.
When I click inside any chart the all Data tab functions, except Show & Hide
Detail, are 'grayed out'.

Shane Devenshire said:
Hi,

When you click the chart title what is the formula you see on the Formula Bar?

In 2007 the Edit links command is on the Data tab which I suppose you found.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


usmc-r70 said:
I am using Excel 2007. All references to 'Edit, Links, and Change Source' is
'grayed out'.

This code works perfectly on another computer using a simplified version
written in Excel 2007.



Shane Devenshire said:
Hi,

Choose Edit, Links, and Change Source to your current workbook. May be able
to dispense with the macro using this technique.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

I have a workbook with a 'template' worksheet that has 4 charts that
reference data contained only in this worksheet .

When I copy the 'template' and rename it (say Project1), the 'Series
Values', 'Series Name', and 'Title' reference in the formula bar continue
referencing the 'template' worksheet.

I found the code shown below and it works for chart 'Series Values', 'Series
Name', but NOT the chart 'Title' that I have referenced in the formula bar.
Credit to: http://peltiertech.com/Excel/Charts/ChgSrsFmla.html#ixzz0KCalAl7L&D

How can I modify the code below to:

1. Include the 'Title' reference shown in the formula bar.

2. Instead of typing the new worksheet name into the dialog box, have the
code reference $AC$37 which has the formula: =MID(CELL("filename",B31),
FIND("]",CELL("filename",B31))+1,255).

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


http://peltiertech.com/Excel/Charts/ChgSrsFmla.html#ixzz0KCalAl7L&D
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Excel 2007 - problems with macro to change series formula 3
Change Chart Series with VBA 2
PowerPoint Change/update links to new source (PPT with charts & objects linked to Excel) 1
Changing source for series collection in charts 1
InputBox to resize all charts of active worksheet 2
Using a variable for worksheet name in SERIES 3
delink all charts in worksheet 3
VBA to update Chart Source Data Rage on Multiple Charts 1

Top