Return Chart title formula using VBA

Hi

I have a spreadsheet in which chart titles are linked to the contents of cells through formulas, e.g.

='chart data'!$CI$1

Is there a way to use VBA to return this formula, rather than the text that the formula refers to? All of the ChartTitle properties (caption, text, characters) seem to return the text not the formula. I’m looking for something simple, along these lines:

Please Login or Register  to view this content.

thanks for that Teylyn

Unfortunately it didn't work - I don't think formula is a property of ChartTitle or ChartTitle.Text

here's an example of a sheet with what I'm trying to do - I'd like the macro to return the formula in the chart title:

=Sheet1!$A$31

not the content of the cell it refers to:

"Monthly Australian Dollar Exchange Rates"

cheers

That's probably it - the code's certainly logical
sorry, should have mentioned I'm using 2007
thanks again for your help
best wishes

Hi teylyn

I can see the work around would be effective, if a bit clunky.

As I was hoping to use this bit of code on several dozen charts at a time, and the titles are sometimes in different sheets to the charts, I think I'll have to approach it in another way.

What I was actually trying to do for a worksheet with about 40 charts on was to temporarily delete the chart titles, export the charts as pictures to PowerPoint without titles, put the title text in the title area of the PowerPoint slide, then restore the chart titles (with formula) in the original worksheets. On reflection, I may be able to do this using “Undo” in the Excel code to restore the chart title rather than reinserting the title using the formula.

Thank you for the sleuthing – at least I didn’t miss anything too obvious.

Best wishes

Nicky

thank you all for your help

All the options for code to return the title formula looked fearsome!

In the end, I went with a variation of Andy's suggestion - temporarily changing the title font colour to the background colour. I also temporarily set it to font size 1 so it doesn't obscure any axis titles or other material, and resized the plot area to fill some of the space previously used by the chart title.

if you're interested, here is the code - it copies pictures of all graphs on worksheet "charts" onto PowerPoint slides, and puts the title text in the powerpoint title area. Useful if you're using the same chart pack with lots of charts for multiple purposes.

You'll need microsoft powerpoint object library checked in tools references

Please Login or Register  to view this content.