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!

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"tenlbham" wrote in message
...
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!

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 futu

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Andy Pope" wrote in message
...
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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"tenlbham" wrote in message
...
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!

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.

"Andy Pope" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"tenlbham" wrote in message
...
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!

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"tenlbham" wrote in message
...
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.

"Andy Pope" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"tenlbham" wrote in message
...
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!