Results 1 to 3 of 3
Thread: Chart Title
2014-01-07, 11:46 #1
- Join Date
- Jun 2010
- Quincy, MA
- Thanked 0 Times in 0 Posts
I have a monthly spreadsheet in which I create the same bar chart across 15 different clients. The title reflects both the month and year. The question is, is there something similar to "Find and Replace" that will allow me to change November 2013 to December 2013 without having to go into each and every chart? I don't really know VBA but am willing to learn how to use it. Using Windows 2010. Thank you
2014-01-07, 14:30 #2
- Join Date
- Jan 2001
- La Jolla, CA
- Thanked 63 Times in 59 Posts
If the date is in a cell and you select that date when initially creating the bar chart, the date will be reflected in the chart title. Then, when you change the date in that cell, the chart will automatically change as well. Will that work for you?
Alternatively, if you've just graphed the data and then use Chart Tools | Layout | Labels | Chart Title you can click in the formula bar and type: =Sheet1!A1 if your title is in A1 on Sheet1. Then, when you change the entry in A1, the title on the chart will change.
But, maybe I misread and you want the title on separate sheets. Steve's VBA will do that, obviously. But, you could also make the chart title reference =Sheet1!A1 for all of the chart titles on all of the separate sheets.
Last edited by kweaver; 2014-01-07 at 14:37.
The Following User Says Thank You to kweaver For This Useful Post:
2014-01-07, 14:32 #3
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
This will loop through the chart sheets and the chart objects within all the worksheets
Option Explicit Sub FindReplaceChartTitles() Dim wks As Worksheet Dim Cht As Chart Dim chtObj As ChartObject Dim strFind As String Dim strReplace As String 'Change as desired strFind = "Old Title" strReplace = "New Title" 'Loop through charts For Each Cht In ActiveWorkbook.Charts Cht.ChartTitle.Text = _ Application.WorksheetFunction.Substitute( _ Cht.ChartTitle.Text, strFind, strReplace) Next 'loop through worksheets For Each wks In ActiveWorkbook.Worksheets 'Loop through chart objects For Each chtObj In wks.ChartObjects chtObj.Chart.ChartTitle.Text = _ Application.WorksheetFunction.Substitute( _ chtObj.Chart.ChartTitle.Text, strFind, strReplace) Next Next End Sub
Option Explicit Sub ChangeChartTitlesToFormula() Dim wks As Worksheet Dim Cht As Chart Dim chtObj As ChartObject Dim strReplace As String 'Change cell reference as desired strReplace = "=Sheet1!$A$1" 'Loop through charts For Each Cht In ActiveWorkbook.Charts Cht.ChartTitle.Caption = strReplace Next 'loop through worksheets For Each wks In ActiveWorkbook.Worksheets 'Loop through chart objects For Each chtObj In wks.ChartObjects chtObj.Chart.ChartTitle.Caption = strReplace Next Next End Sub
The Following User Says Thank You to sdckapr For This Useful Post: