Syntax for selecting a textbox within a chart?

[tenlbham]

I am writing a macro that will insert text & a cell value into a similarly
named text box within multiple charts, but being a noob with VBA, I'm not
sure of the syntax to use to select the text box in question. I was starting
with a simple bit of code that did the same thing for the ChartTitle:

ActiveChart.ChartTitle.Select
Selection.Characters.Text = "text here" & <value>

I tried to use:

ActiveChart.TextBoxes("Text Box 1").Select
Selection.Characters.Text = "text here" & <value>

.... as well as trying it a few other ways, but keep getting the error
"Method 'TextBoxes' of object '_Chart failed"

What am I missing?

Thank you!

 

[Andy Pope]

Hi,

Something like this for a textbox with the name 'Text Box 1'

ActiveChart.Shapes("Text Box 1").TextFrame.Characters.Text = "text here" &
123

Cheers
Andy

 

[Jon Peltier]

You might find it advantageous to construct the text in a cell, then link
the textbox to that cell. This makes it easier to update in the future:

ActiveChart.Textboxes(1).Formula = "=Sheet1!$A$1"

I can't seem to navigate my way to this formula using the Shapes object. You
can apply a cell's value to an axis title in a different way:

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

- Jon

 

[tenlbham]

That's it! Thanks Andy.

Jon P, I do use cell-value linking for other areas of my charts, but it
didn't work in this case. I wanted a simple way to put both text and a cell
value into the same text box. Thanks, though.

 

[Jon Peltier]

The simple way is to combine the text and value in a cell, then link the
cell to the textbox. Any formula in a textbox or other chart text element
can only be a link to a cell, and cannot include any operations.

- Jon