Error setting shape text property

[Sarge]

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.aspx?scid=kb;en-us;317293#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

 

[Ed Ferrero]

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

 

[Tushar Mehta]

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

 

[mrbillbenson]

I advise using .Selection's Text property, which does require that you select the button (shape) first, but so what? Just


Sub ChangeView()
Dim S As Worksheet
Dim ReturnHere 'Leave Variant
Dim Btn As Shape

On Error Resume Next
Set S = ActiveSheet
Set ReturnHere = ActiveCell
Set Btn = ActiveSheet.Shapes(Application.Caller)
Btn.Select
If Selection.Text <> "Text 1" Then
Selection.Text = "Text 1"
Else
Selection.Text = "Text 2"
End If
If Not ReturnHere Is Nothing Then ReturnHere.Activate

End Sub