TextFrame.Characters.Text problem with grouped shape in XP

[pH7]

The oval shapes on the drawing layer are grouped, some under one grou
and some under another group. This function is called for each to
level group. The following function works in Excel 97 and 2000 bu
fails on the line that says “test_string
in_shape.TextFrame.Characters.Text” with a Type mismatch error in Exce
XP. An alternate version that uses instead the 2 commented lines work
in 2000 and XP but not Excel 97. I would like to find something tha
works in all versions. Any ideas?

Code
-------------------
Function match_letter(in_shape As Shape, dimension_letter As String) As Boolean
' This function will act differently depending on the type of in_shape.
' If in_shape is a oval or callout, then the text will be compared to the dimension_letter
' that we are seeking. If it is a match we will return True otherwise return false.
' If in_shape is a group, then this function will call itself recursively to look for
' a shape in this group that is a match. If a shape in the group matches, return
' True, otherwise continue to search.

Dim sh As Shape
Dim save_group As ShapeRange
Dim test_string As String

return_value = False
If in_shape.Type = msoCallout Or _
(in_shape.Type = msoAutoShape And in_shape.AutoShapeType = msoShapeOval) Then
' test_string = in_shape.AlternativeText
' test_string = Mid(test_string, 11)
test_string = in_shape.TextFrame.Characters.Text
If test_string = Trim(dimension_letter) Then
return_value = True
End If
ElseIf in_shape.Type = msoGroup Then
For Each sh In in_shape.GroupItems
return_value = match_letter(sh, dimension_letter)
If return_value Then Exit For
Next
End If
match_letter = return_value
End Function

 

[Peter]

For some reason a Textframe of a single Textbox does not
always relate to a Shape, but sometime is does!

Try:

In the function arguments declare "in_shape As object"

On error resume next
'code

test_string = in_shape.TextFrame.Characters.Text
if err.number <> 0 then
test_string = in_shape.Parent.Characters.Text
end if

"AlternativeText" was new for VB6, so it will not work in
XL97

Regards
Peter T

-----Original Message-----
The oval shapes on the drawing layer are grouped, some under one group
and some under another group. This function is called for each top
level group. The following function works in Excel 97 and 2000 but
fails on the line that says "test_string =
in_shape.TextFrame.Characters.Text" with a Type mismatch error in Excel
XP. An alternate version that uses instead the 2 commented lines works
in 2000 and XP but not Excel 97. I would like to find something that
works in all versions. Any ideas?

Code:

dimension_letter As String) As Boolean

' This function will act differently depending on the type of in_shape.
' If in_shape is a oval or callout, then the text will

be compared to the dimension_letter

' that we are seeking. If it is a match we will return True otherwise return false.
' If in_shape is a group, then this function will call itself recursively to look for
' a shape in this group that is a match. If a shape in the group matches, return
' True, otherwise continue to search.

Dim sh As Shape
Dim save_group As ShapeRange
Dim test_string As String

return_value = False
If in_shape.Type = msoCallout Or _
(in_shape.Type = msoAutoShape And

in_shape.AutoShapeType = msoShapeOval) Then

 

[pH7]

Hi Peter – thanks for taking a look at my problem.

*For some reason a Textframe of a single Textbox does not
always relate to a Shape, but sometime is does!
*

This sounds like it should have worked if my objects were text boxes
but that was what I tried first and it had the same problem. I shoul
have emphasized in my original post that if the ovals were not member
of a group the function works OK.

Did you really mean “test_string = in_shape.Parent.Characters.Text”
in_shape.Parent is the worksheet and therefore does not have th
Characters method

 

[Peter]

Did you really mean

"test_string = in_shape.Parent.Characters.Text"?

Yes I did, I know it sounds odd and does not imply
that "parent" is the worksheet. When I tried your code
with a single Callout (Type = 2) with text it failed in
XL97 but worked with parent as I described.

This is my solution to a related observation David
McRitchie reported in subject "Retreiving the name of a
shape when clicked" in this NG.

I've also noticed that same can (but not necessarily)
apply with any "type" of single shape containing text.

if the ovals were not members of a group the function

works OK.
Are you sure none of your grouped objects are sub-grouped,
your code does not check "Type" of individual group items.

Regards,
Peter

 

[pH7]

*>Did you really mean

Yes I did, I know it sounds odd and does not imply
that "parent" is the worksheet. When I tried your code
with a single Callout (Type = 2) with text it failed in
XL97 but worked with parent as I described.

The reason I asked is that I got an error when I tested on XP becaus
it said the parent didn't have the method Characters.

*This is my solution to a related observation David
McRitchie reported in subject "Retreiving the name of a
shape when clicked" in this NG.

I will go check that thread

*I've also noticed that same can (but not necessarily)
apply with any "type" of single shape containing text.

works OK.
Are you sure none of your grouped objects are sub-grouped,
your code does not check "Type" of individual group items.

The structure I have is

Code
-------------------
Group1
Picture1
Group2
Oval1
Oval2
Oval2