How to retrieve text from the textframe of an oval shape in Excel?

[Greg M]

It's straightforward to set text in the textframe of a shape using the
characters method but how can you retrieve the text? I've tried using text,
characters.caption and characters.text, and I've tried creating a characters
object and setting it from the characters method - nothing works. It looks
like a hole in the object model.

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim myOval As Oval
Dim myStr As String

Set myOval = Worksheets("sheet1").Ovals("Oval 1")

myStr = myOval.Caption
MsgBox myStr
End Sub

If you wanted to go through the Shapes collection:

Option Explicit
Sub testme2()
Dim myShape As Shape
Dim myStr As String

Set myShape = Worksheets("sheet1").Shapes("Oval 1")

myStr = myShape.DrawingObject.Caption
MsgBox myStr
End Sub

 

[Greg M]

Thanks - this works perfectly. I could have spent forever not finding out
that you have to use a different object to read and write the same property!
Greg McCormick