Below are two macros that were taken from an Excel VBA. They are
condensed to the minimum that still reproduces the problem. I need to
avoid selecting the object, but still need to read the text.
Can someone tell me why one macro works and the other does not?
Getting the AutoShapeType property (apparenty?) works. Why not getting
the text?
Is there a way to get the text from the rectangle object without
selecting it?

Thanks for any hints

KB

- - - - - - - - - -

Display text from all rectangles on a worksheet:

'This works, but selects the object which causes problems elsewhere,
e.g. if the worksheet is protected

Sub ShowText()
Dim OTbox As Object
For Each OTbox In ActiveSheet.Shapes
If OTbox.AutoShapeType = msoShapeRectangle Then
OTbox.Select
MsgBox Selection.Text
End If
Next OTbox
End Sub

'This returns an error 438 "Object does not support ..."

Sub ShowText()
Dim OTbox As Object
For Each OTbox In ActiveSheet.Shapes
If OTbox.AutoShapeType = msoShapeRectangle Then
MsgBox OTbox.Text
End If
Next OTbox
End Sub


*** Sent via Developersdex http://www.developersdex.com ***

Does this macro do what you want?

Sub ShowText()
Dim R As Range
Dim OTbox As Object
Set R = Selection
For Each OTbox In ActiveSheet.Shapes
If OTbox.AutoShapeType = msoShapeRectangle Then
OTbox.Select
MsgBox Selection.Text
End If
Next OTbox
R.Select
End Sub

--
Rick (MVP - Excel)


"KB01" <(E-Mail Removed)> wrote in message
news:OgAFxw5$(E-Mail Removed)...
> Below are two macros that were taken from an Excel VBA. They are
> condensed to the minimum that still reproduces the problem. I need to
> avoid selecting the object, but still need to read the text.
> Can someone tell me why one macro works and the other does not?
> Getting the AutoShapeType property (apparenty?) works. Why not getting
> the text?
> Is there a way to get the text from the rectangle object without
> selecting it?
>
> Thanks for any hints
>
> KB
>
> - - - - - - - - - -
>
> Display text from all rectangles on a worksheet:
>
> 'This works, but selects the object which causes problems elsewhere,
> e.g. if the worksheet is protected
>
> Sub ShowText()
> Dim OTbox As Object
> For Each OTbox In ActiveSheet.Shapes
> If OTbox.AutoShapeType = msoShapeRectangle Then
> OTbox.Select
> MsgBox Selection.Text
> End If
> Next OTbox
> End Sub
>
> 'This returns an error 438 "Object does not support ..."
>
> Sub ShowText()
> Dim OTbox As Object
> For Each OTbox In ActiveSheet.Shapes
> If OTbox.AutoShapeType = msoShapeRectangle Then
> MsgBox OTbox.Text
> End If
> Next OTbox
> End Sub
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Hi,

Try it like this

Sub ShowText()
Dim OTbox As Object
For Each OTbox In ActiveSheet.Shapes
If OTbox.AutoShapeType = msoShapeRectangle Then
MsgBox Shapes(OTbox.Name).TextFrame.Characters.Text
End If
Next OTbox
End Sub

Mike

"KB01" wrote:

> Below are two macros that were taken from an Excel VBA. They are
> condensed to the minimum that still reproduces the problem. I need to
> avoid selecting the object, but still need to read the text.
> Can someone tell me why one macro works and the other does not?
> Getting the AutoShapeType property (apparenty?) works. Why not getting
> the text?
> Is there a way to get the text from the rectangle object without
> selecting it?
>
> Thanks for any hints
>
> KB
>
> - - - - - - - - - -
>
> Display text from all rectangles on a worksheet:
>
> 'This works, but selects the object which causes problems elsewhere,
> e.g. if the worksheet is protected
>
> Sub ShowText()
> Dim OTbox As Object
> For Each OTbox In ActiveSheet.Shapes
> If OTbox.AutoShapeType = msoShapeRectangle Then
> OTbox.Select
> MsgBox Selection.Text
> End If
> Next OTbox
> End Sub
>
> 'This returns an error 438 "Object does not support ..."
>
> Sub ShowText()
> Dim OTbox As Object
> For Each OTbox In ActiveSheet.Shapes
> If OTbox.AutoShapeType = msoShapeRectangle Then
> MsgBox OTbox.Text
> End If
> Next OTbox
> End Sub
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Actually, use this macro instead (it is your second macro modified to
work)...

Sub ShowText()
Dim OTbox As Object
For Each OTbox In ActiveSheet.Shapes
If OTbox.AutoShapeType = msoShapeRectangle Then
MsgBox OTbox.TextFrame.Characters.Text
End If
Next OTbox
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:%23KxlvE6$(E-Mail Removed)...
> Does this macro do what you want?
>
> Sub ShowText()
> Dim R As Range
> Dim OTbox As Object
> Set R = Selection
> For Each OTbox In ActiveSheet.Shapes
> If OTbox.AutoShapeType = msoShapeRectangle Then
> OTbox.Select
> MsgBox Selection.Text
> End If
> Next OTbox
> R.Select
> End Sub
>
> --
> Rick (MVP - Excel)
>
>
> "KB01" <(E-Mail Removed)> wrote in message
> news:OgAFxw5$(E-Mail Removed)...
>> Below are two macros that were taken from an Excel VBA. They are
>> condensed to the minimum that still reproduces the problem. I need to
>> avoid selecting the object, but still need to read the text.
>> Can someone tell me why one macro works and the other does not?
>> Getting the AutoShapeType property (apparenty?) works. Why not getting
>> the text?
>> Is there a way to get the text from the rectangle object without
>> selecting it?
>>
>> Thanks for any hints
>>
>> KB
>>
>> - - - - - - - - - -
>>
>> Display text from all rectangles on a worksheet:
>>
>> 'This works, but selects the object which causes problems elsewhere,
>> e.g. if the worksheet is protected
>>
>> Sub ShowText()
>> Dim OTbox As Object
>> For Each OTbox In ActiveSheet.Shapes
>> If OTbox.AutoShapeType = msoShapeRectangle Then
>> OTbox.Select
>> MsgBox Selection.Text
>> End If
>> Next OTbox
>> End Sub
>>
>> 'This returns an error 438 "Object does not support ..."
>>
>> Sub ShowText()
>> Dim OTbox As Object
>> For Each OTbox In ActiveSheet.Shapes
>> If OTbox.AutoShapeType = msoShapeRectangle Then
>> MsgBox OTbox.Text
>> End If
>> Next OTbox
>> End Sub
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>