Change Series Formulas for all charts on worksheet

  • Thread starter Thread starter usmc-r70
  • Start date Start date
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
 
Hi,

Choose Edit, Links, and Change Source to your current workbook. May be able
to dispense with the macro using this technique.
 
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...
 
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...
 
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...
 
Back
Top