Thread: Globally Change All Chart Titles' Substring with Macro?

I have to create a large report each month with continuously updated data. In the report are over 16 Excel 2003 charts all with all chart titles containing a string "As of 31 Xxx 2011" where "Xxx" is the standard 3 letter month abbreviation.

I have been able to find code that will update each chart's data individually, where you click on the chart, run the macro and the series updates etc. But for titles it doesn't work and I could just edit the individual title for the same effect as easily.

Is it possible for write a macro that will select ALL the charts on the active sheet, find the substring in each title (such as "Mar") and replace it with a second substring (such as "Apr")?

The data could be hard coded each month in say cells A1 & B1 and the macro updates all the charts from these cells.

Thanks for any help you may provide.

They are basically all on one worksheet page, with the data on a separate page.

Thanks for the help.
However,

It comes back as an error: (on line that begins with Newtitle = )

Run-time error '438'
Object doesn't support this property or method

I'm running Excel 2003 on Windows XP SP2.

Thank you - this is very helpful. A variation without VBA is to use the TEXT() function in a cell, where the the formula bar of the chart title references that cell.

For education's sake, is there a way to select a specific substring (such as "Mar") in a variable length title of every chart on a sheet, each chart title having a different title length and content, and replace that substring with another one (such as "Apr")? I have code that can do it for other objects, say formulas in series' definitions; I was wondering why similar code will not work for chart titles.

Thank you this works now, but it changes all the titles in every chart to the same string - useful, but not my needs unfortunately.

I thank you for all your effort, and certainly I may not be communicating exactly the problem, but basically I was looking for VBA code to replace a specific substring in the variable length titles of every chart on a sheet, replacing the substring (such as "Mar") with a new one (such as "Apr") but keeping all the other text in each title.

Thanks again.

Sub ChangeChartTitles()
Dim MyChart As Chart
Dim N As Integer
Dim NewTitle As Variant

NewTitle = Application.InputBox("Please enter new title", "New chart titles", "As of XXX " & Year(Now()) , , , , , 2)
If NewTitle = False Then Exit Sub
For N = 1 To ActiveSheet.ChartObjects.Count
Set MyChart = ActiveSheet.ChartObjects(N).Chart
MyChart.ChartTitle.Caption = NewTitle
Next N
End Sub


Thank you this works now, but it changes all the titles in every chart to the same string - useful, but not my needs unfortunately.

I thank you for all your effort, and certainly I may not be communicating exactly the problem, but basically I was looking for VBA code to replace a specific substring in the variable length titles of every chart on a sheet, replacing the substring (such as "Mar") with a new one (such as "Apr") but keeping all the other text in each title.

Thanks again.[/QUOTE]

Originally Posted by mrice

Maybe this?

Sub ChangeChartTitles()
    Dim objCht As ChartObject
    Dim lngPos As Long
    Dim OldValue  As String
    Dim NewValue As String
    OldValue = Range("A1").Value
    NewValue = Range("A2").Value
    For Each objCht In ActiveSheet.ChartObjects
        With objCht.Chart
            .ChartTitle.Text = WorksheetFunction.Substitute(.ChartTitle.Text, OldValue, NewValue)
        End With
    Next
End Sub

Old and new values are specified in A1 and A2 respectively.

Incidently, .Caption appear to work as well (in Excel 2007)

Excellent! This works like a charm.
Changed the hard coded ranges to Inputbox() variables and I'm all set.
Many thanks again.