Connectors List? 1

[N1GHTEYES]

I have a worksheet with a whole slew of shapes (rectangles) connected by connectors. Don't ask why, it really doesn't matter and it would take too long to explain. The key point is that it looks a bit like a family tree.

I want to be able to manually select any one rectangle, then use a subroutine to select all dependent shapes i.e. all of the shapes on that "branch" of the tree.

I could do this easily if I could find out how, in code, to list all the connectors which are connected to a particular shape and know which are "ends" and which are "starts". I assume this info is available somewhere in the object hierarchy, but I'm blowed if I can find it, and so far the "help" hasn't been very helpful.

Can anyone please point me to the right object / property / collection / method or whatever?

BTW, I am using Office version 2003.

Thanks,

Tony

 

[SkipVought]

Tony,

Kind of a Rube Goldbergish approch. Sorry.

Skip,

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

 

[N1GHTEYES]

mint: Ah, you've enlightened me. I didn't realise that. I thought that if you want your arrow to point from A to B you had to draw it from A to B. I take it you are saying that is not the only way to do it?

Oh, I see, I could change the "begin style" and "end style" of the arrow object, and if I did that then the beginconnectedshape and endconnectedshape would stay where they were, so the pointy end would now be the begining not the end. So, the penny finally drops. Sorry to be so slow.

If I ever make this into something for other people to use, it looks like my best bet would be to take control of the creation of the boxes and connectors. Actually, thinking about it, if I ever get to that point, I might be best just writing it in VB rather than Excel.

Tony

 

[N1GHTEYES]

This is a quick note to let you know the outcome.

After a day or so of being distracted elsewhere, I managed to get back to this and came up with the following:

The situation is:

A worksheet with a whole bunch of boxes connected by connectors.
The user selects one of more boxes and clicks "Alt+S".
The code then selects all of the connectors and boxes in the leg(s) below the user-selected items. So the user can now quickly rearrange the diagram one leg at a time - however far up- or down- the leg he chooses to grab.

My coding of it is almost certainly over-engineered yet somehow flawed, so feel free to suggest improvements if you like, but I've tested it and it does work.

The link to the "Alt+S" command is created using the line:

Application.OnKey "%s", "Select_Leg"

in the Workbook_Open event of the Thisworkbook module.

The code in the main module is:

Dim namecount As Long
Dim MyNames() As Variant
Dim shpconlist As ShapeConnections

Sub Select_Leg()
    On Error Resume Next
    CreateConnectorList
    Dim shp As Shape
    ReDim MyNames(1 To ActiveSheet.Shapes.Count) As Variant
    For Each shp In Selection.ShapeRange
        Get_Leg shp
        If Err.Number <> 0 Then Err.Clear
    Next shp
    ReDim Preserve MyNames(1 To namecount) As Variant
    ActiveSheet.Shapes.Range(MyNames()).Select
    namecount = 0
End Sub

Public Sub CreateConnectorList()
On Error Resume Next
Dim wksht As Worksheet
Set wksht = Worksheets("Kids Cascade")
Dim shp As Shape
Set shpconlist = New ShapeConnections
For Each shp In wksht.Shapes
    If shp.connector Then
        If shp.ConnectorFormat.BeginConnected Then
            shpconlist.Add shp.ConnectorFormat.BeginConnectedShape.Name, shp.Name, True
        End If
        If subshape.ConnectorFormat.EndConnected Then
            shpconlist.Add shp.ConnectorFormat.EndConnectedShape.Name, shp.Name, False
        End If
    End If
Next shp
End Sub

Sub Get_Leg(thisshape As Shape)
    Dim con As Variant
    Dim i As Long
    Dim dependentshape As Shape
    namecount = namecount + 1
    MyNames(namecount) = thisshape.Name
    For i = 1 To shpconlist.Item(thisshape.Name).down.Count
        con = shpconlist.Item(thisshape.Name).down(i)
        namecount = namecount + 1
        MyNames(namecount) = con
        Set dependentshape = ActiveSheet.Shapes(con).ConnectorFormat.EndConnectedShape
        Get_Leg dependentshape
    Next i
End Sub

The class module code for the Shapeconnections collection class is:

Private mycol As Collection

Private Sub Class_Initialize()
Set mycol = New Collection
End Sub

Public Sub Add(shapename As String, connectorname As String, down As Boolean)
On Error Resume Next
'This process adds a new connector to the down collection or up collection for item mycol(shapename) (depending on the value of "down")
Dim thiscon As ShapeCon
Set thiscon = mycol(shapename)
If Err.Number <> 0 Then
    Err.Clear
    Set thiscon = New ShapeCon
    mycol.Add Item:=thiscon, key:=shapename
End If
If down Then
    thiscon.down.Add Item:=connectorname, key:=connectorname
Else
    thiscon.up.Add Item:=connectorname, key:=connectorname
End If
Set mycol(shapename) = thiscon
End Sub

Public Property Get Item(shapename As String) As ShapeCon
Set Item = mycol(shapename)
End Property

and for the Shapecon class:

Public down As Collection
Public up As Collection

Private Sub Class_Initialize()
Set down = New Collection
Set up = New Collection
End Sub

In effect, what is happening is:
Select_Leg first creates a list of all the connections in a ShapeConnections object using the Createconnectors sub.
For each selected shap, it then recursively adds the name of each downwardly - dependent shape to a shared array called MyNames() using the sub Get_Leg.
It then creates a shaperange from the array of names in MyNames and selects the shaperange.

Messy huh? As I say, not the tidiest or most robust bit of code every produced, but it does what I need for now.

Thanks everyone for your help getting to this point.

Tony

 

[combo]

Thanks for the star, hope that you have no problems in working in a worksheet.
In reference to applying a chart sheet as a background:
The chart sheet is an one page sheet, the zoom can be set within 10-400%. The grayed background (guess that you meant this) is a non-printed area outsde chart. The default layout is a landscape, can be changed to portrait.

combo

 

[N1GHTEYES]

Combo: as you can see from my previous post, I pretty much have it doing what I need for now on a worksheet. So I think I'll stick with it. However, you've certainly opened my eyes for futre reference about the potential benefits of a chart as a background. Thanks.

Tony