Excel 2003 Charts - Globally change all charts' titles with a macro?

[parodytx]

I have to create a large report each month with continuously updated data. The report is based on 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. All the charts are in the same worksheet.

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 to 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 substrings could be hard coded each month in say cells A1 (current month) & B1 (previous month) and the macro updates all the charts using these cells.

Thanks for any help you may provide.

 

[SkipVought]

Hi,

Why not use a REAL DATE, formatted as you so desire?

Your Chart Titles can reference a cell in which you could have a formula with your title & formatted date, using the TEXT() function. No macro required. For example for your current date in A1
[tt]
="Your Chart Title - as of "&TEXT(A1,"dd mmm yyyy")
[/tt]


Are all these charts plotting different data, or subsets of similar data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[parodytx]

Well I guess this is why you guys are the experts - never occurred to me that the chart titles allowed referenced cell data. Nice to know.

All the data charts are YTD, MTD and Prior MTD chartings of incidence data for multiple compounds in different areas. Up to 14 series per chart, each.

Thanks again.

 

[SkipVought]

Let me give you an example of what I often do with charts.

I have 2 charts. One chart shows past due and on time load by work center for work coming to the selected work center. The other chart shows the load in the selected work center for the next period by month.

A combobox is used to select the work center. The charts source data is filtered on the selected work center and consequently the chart only shows data from the visible cells. This can be accomplished with a very small macro, that you can record and get some help modifying.

BOTTOM LINE: I can display DOZENS or HUNDREDS of different charts, with just TWO actual charts.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}