XL2007 Bug: Text Can be Changed in Shape with Linked Text
 

First, a reminder: To create a shape with linked text, enter "Hello" in,
say, cell A1, add an autoshape to the sheet, select that shape, click in the
formula bar, and enter "=A1". The shape now displays "Hello". If you
right-click on the shape, the "Edit Text" option is grayed-out.
To see the problem, with the shape selected, run the following in the
immediate window:
selection.shaperange(1).textframe2.textrange.text = "Goodbye"
The shape now says "Goodbye", with, e.g., "=A1" showing in the formula bar.
Recalculating (Ctrl + Alt + F9) does not change this. Clicking in the
formula bar, then pressing enter will redisplay the linked text.
To avoid this problem, programmers need to be able to tell if a shape has
linked text. I find no direct way of determining this. Here is a
work-around, illustrated in the immediate window:
? selection.shaperange(1).textframe2.textrange.chara cters(1,1).count
For a non-linked shape, the above gives the expected result: 1. For a
linked shape, the above gives the number of characters in the linked text:
5, if the linked text is "Hello"; not what you'd expect for .characters(1,1).
I've found no way to tell if text is linked if it only contains one
character, so I've had to exclude formatting one-character text. Here's an
example of how to handling this:

For N = 1 To Selection.ShapeRange.Count
Set Shp = Selection.ShapeRange(N)
With Shp
nShpType = .Type
If nShpType = msoAutoShape Or nShpType = msoTextBox _
Or nShpType = msoComment Then
If .TextFrame2.HasText Then
If .TextFrame2.TextRange.Characters(1, 1).Count = 1 And
.TextFrame2.TextRange.Characters.Count 1 Then
DoFormat .TextFrame 'Formats the shapes text
End If
End If
End If
End With
Next N

Chris