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

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.

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

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 wrote:

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

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

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

"Greg M" wrote:

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.