Connecting grouped objects in code

[N1GHTEYES]

In excel (2003) I have some objects. They each consist of a set of 4 textboxes which have been grouped into one object. In other words I have multiple objects, each of which consists of a grouped set of four text boxes.

I am trying to put connectors between them in code.

I recorded a macro in which I did this to one pair of the objects.

When I run the macro, it crashes.

If I create a couple of simple textboxes (not grouped) and record the macro whilst connecting those, it runs OK.

Here is the code I recorded while connecting the grouped items:

Sub setconnections()
    ActiveSheet.Shapes("AutoShape 111").Select
    Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("bx_Pd_4"), 4
    Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("bx_NETD50_4"), 2
End Sub

This is the macro I recorded while connecting just the simple text boxes:

Sub test_box_connections()
    ActiveSheet.Shapes("AutoShape 111").Select
    Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("Text Box 113"), 4
    Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("Text Box 114"), 2
End Sub

Note that the names of the grouped items are Grp_Pd and Grp_NETD50 NOT bx_Pd_4 and bx_NETD50_4. Instead bx_Pd_4 and bx_NETD50_4 are the names of two of the individual text boxes which make up Grp_Pd and Grp_NETD50 respectively.

So the problem seems to be that when I manually make connections, they can connect grouped items because Excel recognises that the sub-items (the internal textboxes) exist, whereas when I do the same thing in code, excel does not recognise that the internal items exist.

I'm a bit stumped. Does anyone have any clues as to how to connect grouped objects?

Tony

 

[SkipVought]

hi,

AVOID using the Select and Activate methods.

Rather REFERENCE the object explicitly.

Sub setconnections()
    with ActiveSheet.Shapes("AutoShape 111")
       .ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("bx_Pd_4"), 4
       .ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("bx_NETD50_4"), 2
    end with
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Thanks Skip - I am aware of your mantra, and I agree with you. I just used the macro recorder to point me in the right direction - and it uses select etc.

The problem is that when I run the code (either the stuff I posted or yours) I get an error:
"The item with the specified name was not found"

However, the names you included ARE the correct names of the sub-items relating to the text boxes within the grouped objects.

The issue seems to be there is a difficulty in either:

1 referring in code to the components of a grouped object
or
2 connecting a connector to a location on a grouped object itself (as opposed to a location on one of its components)

Any ideas?

Tony

 

[SkipVought]

This works for me, regardless of grouping or not...

Sub setconnections()
    with ActiveSheet.Shapes("AutoShape 111").ConnectorFormat
       .BeginConnect ActiveSheet.Shapes("bx_Pd_4"), 4
       .EndConnect ActiveSheet.Shapes("bx_NETD50_4"), 2
    end with
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Hmmm. Puzzling. Maybe there is something weird about the way I connected them? I'm at home right now, so I can't post the code, but if I'm still stuck tomorrow, I'll post what I've used to create and group the objects as well as how I've tried to connect them.

However, I did notice something odd earlier.

When I'm recording a macro, Excel does not allow me to select the sub-objects within a group. However, if I'm not recording a macro, I can do so i.e. the name shown in the name box displays the name of the grouped object or the sub-object, depending on what I click on.

BTW Skip, what version of Excel are you using?

Tony

 

[SkipVought]

Excel 2007.

Here was my process in creating a test.
[tt]
1. Add 2 rectangles

2. Add a Connector Line

3. Group all 3 objects
[/tt]
Then used this code

Sub setconnections()
    With ActiveSheet.Shapes("Elbow Connector 9").ConnectorFormat
       .BeginConnect ActiveSheet.Shapes("Rectangle 1"), 4
       .EndConnect ActiveSheet.Shapes("Rectangle 2"), 2
    End With
End Sub

If I were doing this via code, I'd be ADDING each of these 3 shapes and join them...

Sub test()
    Dim shp(1) As Shape, i As Integer
    
    With ActiveSheet
        For i = 0 To 2
            Select Case i
                Case 0, 1
                    Set shp(i) = .Shapes.AddShape(msoShapeRectangle, 500 * i, 0, 100, 200)
                Case 2
                    With .Shapes.AddConnector(msoConnectorStraight, 0, 0, 5, 5).ConnectorFormat
                        .BeginConnect shp(0), 4
                        .EndConnect shp(1), 2
                    End With
            End Select
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Ah, now I understand why it worked for you and not me.

what I did was:

create 4 text boxes (box1_a to box4_a)
group them into 1 object (grpA)

create another 4 text boxes (box1_b to box4_b)
group them into 1 object (grpB)

create a connector and join grpA to grpB.

When I do this manually, it works.

When I try to do it in code, if I designate the group objects as the start and end points of the line, it says the connection points of the objects are out of range. If instead I designate, say box1_a and box1_b as the connector destinations, it says those objects don't exist.

But if I create the connection while recording a macro, IT writes code which uses the sub objects (boxes rather than groups). But when I then play the macro I've just recorded, it throws the above error.

Tony

 

[SkipVought]

I misunderstood you.

"When I do this manually, it works."

Are your SURE that you did not join a SHAPE from within each group, rather than the GROUP SHAPES?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

I think there was some confusion earlier caused by the use of connect, join and group to mean different things. To clear that up I will try to stick to the following convention:

to join - move objects into adjacent positions
to group - change the status of a set of objects from individuals to a group which Excel treats as a whole
to connect - use a connector object fastened at one end to one object and another object at the other end.

When I connect the groups manually it actually connects sub-objects within the group. The groups are then connected in the sense that when I move one group object, the connector remains connected.

In code, when connecting connectors, the name and connection site number of the connected objects must be specified. When I record a macro while manually connecting two grouped objects (as above) the code Excel writes references the sub-objects as the connector destinations. If I then run this macro, excel throws an error saying the objects can't be found.

If I write similar code, but using the grouped object names as the connector destinations, it still throws an error, but this time it says the item is out of range. It does not specify which item, but I assume it is referring to the connector number. The error even comes up if I set the connector numbers to 1, which implies that Excel does not recognise that a grouped object actually has any connection sites.

So, the question is how do I connect grouped objects in code.

Sorry about any confusion.

Here is some code, together with the errors thrown.
The situation is similar to what I wrote last night. Two grouped objects, each of which consists of 4 text boxes. The grouped object names are Grp_Pd and Grp_NETD50. The individual boxes are named as <box_> <Pd> <_n> or <box_> <NETD50> <_n> where n runs from 1 to 4.

This code:

Sub setconnections()
With ActiveSheet.Shapes("AutoShape 111")
    .ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("bx_Pd_4"), 4
    .ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("bx_NETD50_4"), 2
End With
End Sub

Gives the error "the item with the specifed name wasn't found".

This code is based on the help:

Sub connect_groups()
    Set S = Worksheets("Dependence_Diagram").Shapes
    Set firstRect = S("Grp_Pd")
    Set secondRect = S("Grp_NETD50")
    Set c = S.AddConnector(msoConnectorCurve, 0, 0, 100, 100)
    With c.ConnectorFormat
        .BeginConnect ConnectedShape:=firstRect, ConnectionSite:=1
        .EndConnect ConnectedShape:=secondRect, ConnectionSite:=1
        c.RerouteConnections
    End With
End Sub

It throws an error on the line:
.BeginConnect ConnectedShape:=firstRect, ConnectionSite:=1
The error is "the specified value is out of range"

The following code was recorded while manually connecting the groups (as described above)

Sub joingroups()
    ActiveSheet.Shapes("AutoShape 111").Select
    Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("bx_Pd_3"), 4
    Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("bx_NETD50_3"), 2
End Sub

Even though I recorded this code while actually doing the connection process, when I run the code (after disconnecting the connector manually to set things back to the same initial conditions) it throws an error on the line:
Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("bx_Pd_3"), 4
The error message is "the item with the specified name wasn't found".

Tony

 

[N1GHTEYES]

OK, so we have some progress.

I still don't know how to connect grouped objects in code, but I have found a way round the problem, though it is a bit messy.

Here is some code which works to connect two groups:

Sub alt_join_method()
    ActiveSheet.Shapes("Grp_Pd").Select
    Selection.ShapeRange.Ungroup.Select
    ActiveSheet.Shapes("Grp_NETD50").Select
    Selection.ShapeRange.Ungroup.Select
    ActiveSheet.Shapes("AutoShape 111").Select
    Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("bx_Pd_4"), 4
    Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("bx_NETD50_4"), 2
    ActiveSheet.Shapes.Range(Array("bx_Pd_1", "bx_Pd_2", "bx_Pd_3", "bx_Pd_4")).Select
    Selection.ShapeRange.Group.Select
    Selection.ShapeRange.name = "Grp_Pd"
    ActiveSheet.Shapes.Range(Array("bx_NETD50_4", "bx_NETD50_3", "bx_NETD50_2", "bx_NETD50_1")).Select
    Selection.ShapeRange.Group.Select
    Selection.ShapeRange.name = "Grp_NETD50"
End Sub

Basically what it does is:
split the groups
make the connection
rejoin the groups
rename the groups to their original names

Obviously the above code is hard-coded for the groups in question, so it will need modding to be more general, and yes Skip, I will do it by referencing rather than selecting. But at least it works.

I'm still puzzled though by the fact that the code recorded previously by a macro does not work when you run the blasted thing.

Tony

 

[N1GHTEYES]

Ta Daaaahhhh!!!

I've got it sussed.

After wading through a sea of irrelevant googling, I came across this:

http://www.ozgrid.com/forum/showthread.php?t=41676&page=1

The upshot is that there is a way of referring to the sub-items within a group and you need to do this to make the connection work.

So, the following code uses the existing connector object "Autoshape 111" to connect the grouped objects "Grp_Pd" and "Grp_NETD50" at connection points on their fourth sub-item.

Sub joingroups()
    ActiveSheet.Shapes("AutoShape 111").Select
    Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("Grp_Pd").GroupItems(4), 4
    Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("Grp_NETD50").GroupItems(4), 2
    Selection.ShapeRange.RerouteConnections
End Sub

That proves the principle. Now all I've got to do is turn it into a usable general procedure.

Tony

 

[SkipVought]

This worked to connect 2 rectangles that were each in a different GROUP.

Notice the DIFFERENCE in the syntax...

Sub setconnections()
    With ActiveSheet.Shapes("connector1").ConnectorFormat
       .BeginConnect ActiveSheet.Shapes("Rectangle 2"), 4
       .EndConnect ActiveSheet.Shapes("Rectangle 3"), 2
    End With
End Sub

No ShapeRange!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Ah, so now we have two ways of doing it?

Just to be clear, are you saying that in your example, Rectangle 2 and Rectangle 3 were both internal elements of different grouped objects? If so, that should work for my case. I'll give it a bash & let you know...


OK, I've tried it. Here is the code I used, based on yours, but modded to fit my names:

Sub setconnections()
    With ActiveSheet.Shapes("AutoShape 111")
        .BeginConnect ActiveSheet.Shapes("bx_Pd_4"), 4        
        .EndConnect ActiveSheet.Shapes("bx_NETD50_4"), 2
    End With
End Sub

When I run this with the boxes grouped, it gives an error "the item with the specifed name wasn't found". Note that if I clcik in the sheet on the boxes in question, the names ARE as above.

If I split the groups and run the above I also get an error on the line <.BeginConnect ActiveSheet.Shapes("bx_Pd_4"), 4>. The error is "Object doesn't support this property or method"

Maybe the fact it works for you and not me is a difference between 2003 and 2007?


Tony

 

[N1GHTEYES]

So here is my final routine. See the comments for a full description.

Function ConnectGroups(wksht As Variant, startgroup As Variant, endgroup As Variant, _
                       Optional ConnectorType As Variant, Optional ShowDirection = True, _
                       Optional StartSubitem As Variant, Optional EndSubitem As Variant, _
                       Optional startsite As Variant, Optional endsite As Variant) As Variant
'This function creates a connector and uses it to join two grouped objects.
'The objects must be on the worksheet wksht.
'The connector is drawn from startgroup  to endgroup
'If supplied, the connector is drawn from the sub-item startsubitem to the sub-item endsubitem.
'If these are not supplied, it is drawn from / to the first sub-item in the group(s).
'The connector is drawn from position startsite to endsite if supplied.
'If startsite and endsite are not supplied, it is automatically rerouted using Excel's
'default routing strategy.
'The wrksht, startgroup, endgroup, startsubitem and endsubitem
'may all be provided in one of three ways:
'   as objects,
'   as names, or
'   as indices.
'If the optional Showdirection parameter is true or missing, the connector is drawn with an
'arrow indicating direction from startgroup to endgroup.
'The function returns a reference to the generated connector.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Dim Vars
    Dim cnctr As Shape
    Dim sht As Worksheet
    Dim sourcegrp As Shape, sinkgrp As Shape, substart As Shape, subend As Shape
    Dim startpos As Long, endpos As Long
    Dim contype As MsoConnectorType
    Dim DoReGroup As Boolean
    
    'set references to the required objects
    
    'worksheet
    If VarType(wksht) = vbObject Then
        Set sht = wksht
    Else
        Set sht = Worksheets(wksht)
    End If
    
    'source
    If VarType(startgroup) = vbObject Then
        Set sourcegrp = startgroup
    Else
        Set sourcegrp = sht.Shapes(startgroup)
    End If

    'sink
    If VarType(endgroup) = vbObject Then
        Set sinkgrp = endgroup
    Else
        Set sinkgrp = sht.Shapes(endgroup)
    End If

    'substart
    If IsMissing(StartSubitem) Then
        Set substart = sourcegrp.GroupItems(1)
    Else
        If VarType(StartSubitem) = vbObject Then
            Set substart = StartSubitem
        Else
            Set substart = sourcegrp.GroupItems(StartSubitem)
        End If
    End If
    
    'subend
    If IsMissing(EndSubitem) Then
        Set subend = sinkgrp.GroupItems(1)
    Else
        If VarType(EndSubitem) = vbObject Then
            Set subend = EndSubitem
        Else
            Set subend = sinkgrp.GroupItems(EndSubitem)
        End If
    End If
    
    'sites
    If IsMissing(startsite) Then
        DoReGroup = True
        startpos = 1
    Else
        startpos = startsite
    End If
    
    If IsMissing(endsite) Then
        DoReGroup = True
        endpos = 1
    Else
        endpos = endsite
    End If
        
    'create a connector
    If IsMissing(ConnectorType) Then
        contype = msoConnectorElbow
    Else
        If ConnectorType >= 1 And ConnectorType <= 3 Then
            contype = Int(ConnectorType)
        Else
            contype = msoConnectorElbow
        End If
    End If
    Set cnctr = sht.Shapes.AddConnector(contype, 0, 0, 100, 100)
    
    'use it to join the groups
    With cnctr
        With .ConnectorFormat
            .BeginConnect substart, startpos
            .EndConnect subend, endpos
        End With
        
        'reroute if no sites given
        If DoReGroup Then .RerouteConnections
        
        'show flow direction with an arrow
        If ShowDirection Then .Line.EndArrowheadStyle = msoArrowheadTriangle
    End With
    
    'return a reference to the connector
    Set ConnectGroups = cnctr
End Function

Tony