Error setting shape text property

Hi guys and girls(i'm told there are a few of you out there),

I have seen posted on MSDN that the Text property can not be set on a
shape via VBA using excel 2002.
http://support.microsoft.com/default...7293#appliesto

The work around sounds hillarious, using Word to create a shape object for
us and then doing a copy / paste via automation.

I have the same issue with excel 2003 is there any other way to provide a
text area on a chart that is movable and can change the text property?

For reference the line of code that errors is
ActiveSheet.Shapes(1).TextFrame.Characters.Text = "Hello"

Runtime Error '1004'
>- Unable to set the Text property of the Characters class

Regards

Mark

Hi Sarge,

The shape object in Excel actually refers to a few different objects that
behave in different ways.

The code works for a simple shape (like a rectangle) but not for a diagram
node shape (like a Pyramid).

ActiveSheet.Shapes(1).TextFrame.Characters.Text = "Hello"

In your case, you might have a shape that is embedded in a ChartObject. So
you need to activate the ChartObject instead of the Sheet.
Try this;

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Shapes(1).TextFrame.Characters.Text = "Hello"

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

> Hi guys and girls(i'm told there are a few of you out there),
>
> I have seen posted on MSDN that the Text property can not be set on a
> shape via VBA using excel 2002.
> http://support.microsoft.com/default...7293#appliesto
>
> The work around sounds hillarious, using Word to create a shape object for
> us and then doing a copy / paste via automation.
>
> I have the same issue with excel 2003 is there any other way to provide a
> text area on a chart that is movable and can change the text property?
>
> For reference the line of code that errors is
> ActiveSheet.Shapes(1).TextFrame.Characters.Text = "Hello"
>
> Runtime Error '1004'
>>- Unable to set the Text property of the Characters class
>
> Regards
>
> Mark
>

More often that not you will find that XL's macro recorder will give
you the necessary code. Of course, it is code that needs some amount
of cleaning and sometimes generalization. But to get the core code,
turn on the recorder (Tools | Macro > Record new macro...), do by hand
whatever it is you want, turn off the recorder, and switch to the VBE.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected]am says...
> Hi guys and girls(i'm told there are a few of you out there),
>
> I have seen posted on MSDN that the Text property can not be set on a
> shape via VBA using excel 2002.
> http://support.microsoft.com/default...7293#appliesto
>
> The work around sounds hillarious, using Word to create a shape object for
> us and then doing a copy / paste via automation.
>
> I have the same issue with excel 2003 is there any other way to provide a
> text area on a chart that is movable and can change the text property?
>
> For reference the line of code that errors is
> ActiveSheet.Shapes(1).TextFrame.Characters.Text = "Hello"
>
> Runtime Error '1004'
> >- Unable to set the Text property of the Characters class
>
> Regards
>
> Mark
>
>
>