Detecting a formula in a chart title

[John Walkenbach]

In a chart's title, you can create a link to a cell with a statement such
as:

ActiveChart.ChartTitle.Text = "='Sheet1'!R1C1"

But, there appears to be no way for VBA to detect whether a chart title has
a formula. The statement below, for example, simply displays the chart's
title (not the formula):

MsgBox ActiveChart.ChartTitle.Text

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

----- Posted by John Walkenbach of JWalk & Associates -----
----- Visit "The Spreadsheet Page" -----
----- http://www.j-walk.com/ss -----

[David J. Braden]

John,
I don't want to get into the metaphysical aspects on this one (who me? <g>), but
after one hour at it I could find no workaround along the lines you pose. Man, I
tried. The only thing I can think of would be to have a dedicated defined name
for each chart's title. For creating your own stuff, I suppose that will do; for
processing other people's stuff, we're sol, apparently.

Regards,
Dave Braden

[Robert Rosenberg]

No solution for me neither. However, use a textbox for the title instead of
the chart title itself and you'll be able to gain access to the formula...

Dim txtTitle As TextBox

Set txtTitle = Sheet1.ChartObjects(1).Chart.TextBoxes("Text Box 2")
MsgBox txtTitle.Text & vbCrLf & txtTitle.Formula

--
_______________
Robert Rosenberg
RCOR Consulting
Microsoft MVP - Excel

http://ntware.com

John Walkenbach <jo...@j-walk.com> wrote in message
news:eY38taqx#GA.265@cppssbbsa05...

[Stephen Bullen]

Hi John,

> 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.BMSLtd.co.uk

[John Walkenbach]

Ah, that's the ticket! I'm trying to create a utility that locates every
link in a workbook. Tracking down a linked formula in a chart title had me
stumped. XLM to the rescue.

Thanks,
John

Stephen Bullen <Ste...@BMSLtd.co.uk> wrote in message
news:VA.0000055d.033a82ac@mars...

[David J. Braden]

John Walkenbach wrote:
>
> XLM to the rescue.
>

Ironic, isn't it? There are a number of things that can be done in XLM that
can't be done in VBA. Evidently Stephen is still using the stuff, or has a
prodigous memory (or both).

Dave Braden

[John Walkenbach]

It gets even more complicated. A chart can (potentially) contain link
formulas in any or all of the four axis titles. And, each data series label
can contain a link. It seems that none of this information is accessible via
VBA.

-John

Stephen Bullen <Ste...@BMSLtd.co.uk> wrote in message

news:VA.0000055f.0262815d@mars...
> Hi John,

>
> > I'm trying to create a utility that locates every
> > link in a workbook.
>

> What's wrong with Bill's FindLink.xla on my web site?
>
> Ahh - I see it doesn't find links in the chart title, either!

[Stephen Bullen]

Hi John,

> It gets even more complicated.

Good luck!