Formula in Chart Title via Macro

[shelfish]

Hello all,

Here is what I am trying to do:

chtTitle = "='" & chartDataSheetName & "'!$AG3$3"
.....ChartTitle.Characters.Text = chtTitle

This, of course, does not work because the title ends up being
"='Chart Data'!$AG3$3" (without the quotes) rather than the actual
formula. In searching the group I found a message posted in 1999 with
the following information....

http://groups.google.com/group/micr...ogramming/browse_frm/thread/291a242e5d730580#

****************** BLOCK QUOTE *********************

Is this a flaw in the object model, or am I missing something?

It is a flaw (I would have expected .Text to return the visible text,
and .Caption to return the formula), but you can always fall back to
XLM:

'Activate the chart, then use
sTitle = ExecuteExcel4Macro("GET.FORMULA(""Title"")")

which returns either the text of the title, or its formula in R1C1
style.

Regards

Stephen Bullen
Microsoft MVP - Excel
http://www.BM
SLtd.co.uk

****************** BLOCK QUOTE *********************

However, this has not worked for me either. I could be doing it
incorrectly. How do I identify the formula that it is getting? i.e.
what do I replace "Title" with?

I'm building the entire chart from a macro and I have all the labels
linking to cells so that last minute changes can be made at the whim
of the end user's boss. In other words, I can't just set the title
using formula within the macro. It needs to be in the excel sheet.

I appreciate any help with this.

-Shelton

 

[Andy Pope]

Hi,

Maybe it simply your range reference $AG3$3 ??

Anyway this works in xl2003.

Activechart.ChartTitle.Text = "=Sheet1!R3C33"

And ExecuteExcel4Macro is for getting the formula used in the chart title
rather than setting it.


Cheers
Andy

 

[shelfish]

N'mind!

I was making that way too dificult. Solution:

chtTitle = Range("'" & chartDataSheetName & "'!$AG$3").Value
....ChartTitle.Characters.Text = chtTitle

Feeling a bit dumb right now,
-S.

 

[Peter T]

That seems like a convoluted way of getting the text from a cell.

chtTitle = Worksheets(chartDataSheetName).Range("AG3").Value

If you want to link the Title to a cell see Andy's post. To get address in
R1C1 format see Address in help.

Regards,
Peter T