Get the name of a group given the reference to a shape within the group
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 |
Get the name of a group given the reference to a shape within the group
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 -- Jim Rech Excel MVP |
Get the name of a group given the reference to a shape within the group
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" wrote in message ... 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 |
Get the name of a group given the reference to a shape within the group
'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. -- Jim Rech Excel MVP |
Get the name of a group given the reference to a shape within the group
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 Dave Peterson wrote in message ... 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 wrote: 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 |
All times are GMT +1. The time now is 02:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com