Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to avoid selecting an object?
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to avoid selecting an object?
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" wrote in message ... 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to avoid selecting an object?
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to avoid selecting an object?
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" wrote in message ... 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" wrote in message ... 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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting last pasted object | Excel Programming | |||
Selecting Drawing Object By Name | Excel Programming | |||
Trying to Avoid Selecting Sheets | Excel Programming | |||
Treeview, avoid selecting node on keydown or up | Excel Programming | |||
Selecting object | Excel Programming |