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/...xzz0KCalAl7L&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/...xzz0KCalAl7L&D

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" wrote:

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/...xzz0KCalAl7L&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/...xzz0KCalAl7L&D

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" wrote:

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" wrote:

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/...xzz0KCalAl7L&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/...xzz0KCalAl7L&D

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" wrote:

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" wrote:

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" wrote:

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/...xzz0KCalAl7L&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/...xzz0KCalAl7L&D

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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/...xzz0KCalAl7L&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/...xzz0KCalAl7L&D