Get the name of a group given the reference to a shape within the group

[Andrew]

I have a series of drawing objects connected by connector lines. I am
using the following to retrieve the name of the object connected by
the line:

dim sh as shape
For Each sh In ActiveSheet.Shapes
debug.print sh.ConnectorFormat.BeginConnectedShape.Name
Next

However, for grouped objects this returns the name of the sub item
which the connector is attached to rather than the group name, eg.
"Line32" is returned rather than "Group15", where Line32 is contained
within Group15.

Is there any way to determine the name of the group that a particular
object making up that group is in?


Thanks a lot,
Andrew

 

[Jim Rech]

Maybe you can use the ParentGroup object. For example:

Sub a()
Dim Sh As Shape
Dim Sh1 As Shape
On Error Resume Next
For Each Sh In ActiveSheet.Shapes
If Sh.Connector = msoTrue Then
Err.Number = 0
Set Sh1 = Sh.ConnectorFormat.BeginConnectedShape
If Err.Number <> 0 Then GoTo NextShape
Debug.Print Sh1.ParentGroup.Name
If Err.Number = 0 Then GoTo NextShape
Debug.Print Sh1.Name
End If
NextShape:
Next
End Sub

 

[Andrew]

Thanks Jim,

I couldn't get this to work but had a look on MSDN and found that
'ParentGroup' was only made available in XL2002. I guess that means
I'm out of luck for now (only have XL2000).

Thanks anyway,
Andrew

 

[Jim Rech]

'ParentGroup' was only made available in XL2002.

Too bad. I didn't know that but then I hadn't heard of the ParentGroup
object until I worked on your post<g>. Looks like MS plugged a hole with
ParentGroup because I don't see how to get from a control to its group
object without it. Other that maintaining a table of objects and the group
objects they belong to that is.

 

[Dave Peterson]

Maybe you can loop through the objects that are grouped and pick out what you
want:

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim sh1 As Shape
Dim sh2 As Shape
Dim iCtr As Long
Dim itemCount As Long

Set wks = ActiveSheet

For Each sh1 In wks.Shapes
itemCount = 0
On Error Resume Next
itemCount = sh1.GroupItems.Count
On Error GoTo 0
If itemCount > 0 Then
For iCtr = 1 To itemCount
Set sh2 = sh1.GroupItems(iCtr)
If sh1.GroupItems(iCtr).Connector Then
Debug.Print sh2.ConnectorFormat.BeginConnectedShape.Name
End If
Next iCtr
End If
Next

End Sub

 

[Andrew]

Thanks Dave & Jim,

The other idea that I had is to create an initialise sub which looks
for grouped items and sets the sub-shapes to the same name as the
group (but with a suffix appended). Now when I find the sub-shape I
can determine the name of its parent from its own name.

I like your approach too though Dave because - basically I can create
my own 'GroupParent' property this way. This appeals because
eventually once we upgrade to xl2002+ then I can easily update my
code.

Yet to decide which way to go. The other thing I've done in the
meantime is to minimise the number of grouped shapes.

Thanks again - your ideas really appreciated.

Andrew