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]

hi,

Right place. ;-)

Excel VBA Help has some info in the Shape object, with respect to Connectors, that you might want to read.

Skip,

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

 

[N1GHTEYES]

Hi Skip - sorry about the confusion. I wrote an apology and thought I'd posted it on the office forum, but for some reason it did not appear. Weird.

Anyway... I realise I can find out which shapes are at the ends of a given connector, but that is not the info I want. What I need is the collection of connectors which connect into and out of each shape. See the difference?

If, from a given shape, I can list its connectors, then I write the following:

for a given shape
create a list of all its downward connectors
for each connector
find the shape at the end of the connector
recurse
If however, I only have info from the connector objects themselves, I have to go through EVERY connector in the diagram to check whether it is connected to the shape I'm interested in.

I COULD use that method to create my own list of connectors which connect downward and upward to each shape, but that seems like a nugatory process if the info I need already exists. And I know it does exist. Excel MUST know which connectors are attached to each shape (otherwise, how would it know which connectors to move when the user grabs a shape and moves it?). The problem I'm having is figuring out how to access that info.

I've searched Help, the Object Browser and Google, but so far I can't see which property allows me to start with a shape and then access the collection of connectors which are connected to it.


Tony

 

[mintjulep]

The only way I see to get there is:

Loop through ALL shapes and identify which shapes are .connector.
For each shape that is a .connector get its two .EndConnectedShape and build a table.

Beware that start and end of a connector are relative to the order that it was created, not to the hierarchy of your tree.

 

[mintjulep]

Excel MUST know which connectors are attached to each shape

Nope.

It knows which shapes are connected to each connector.

Much easier problem for Excel to keep track of that way since a connector must have two and only two ends, and each end can be attached to one and only one (or zero) objects.

 

[SkipVought]

A Shape with a Connector property of TRUE is a Connector. So

di sp as shape

for each sp in ActiveSheet.Shapes
  with sp
    if .connector then debug.print .name, .BeginConnectedShape.name, .EndConnectedShape.name
  end with
next

Does that help?


Skip,

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

 

[N1GHTEYES]

mint: I still think that, internally at least, Excel know which connectors are connected to a given shape. If a user grabs a shape and moves it, the connectors to which THAT shape is connected move with it. So Excel must know which connectors are connected to THAT shape. I guess it COULD be looping through every connector every time a shape is moved to see which are connected to the moving shape, but I guess they are more efficient than that. The question is whether that info is available. Of course it might simply not be exposed, but there is also the possibility that it is exposed via some arcane route (e.g shape.shaperange.diagram.somepropertyicantthinkof.connectorscollection or something like that)

Skip: that would help me to identify the connectors, and, if I have to go down the route of creating my own list, I will use it. For no, the trick needed is to start with a shape (or shaperange) object and use the object hierarchy to know which connectors IT has.

Thanks for the inputs folks.

Tony

 

[xlbo]

there doesn't seem to be a connectors collection for each shape

there is a property of a shape that tells you if it is a connector and if it is you can get what it is connected to but you can;t seem to do it the other way round...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[N1GHTEYES]

xlbo: that's the conclusion I'm heading towards. I'm pretty sure there is no such collection at the top level in the Shape object itself. However, before now I've found the items I was looking for buried deeper in the hierarchy. So maybe something is not in shape, but it is in Shape.SomethingObscure.UnlikelyItem.HereItIs.

Finding what you are after, when it is buried like that, can sometimes be achieved using the help (if the help authors happen to have thought of the same search terms as you), but often it seems like a very hit-or-miss process. Similarly, one might stumble upon it when browsing via the Object Browser, but it is not guaranteed.

I was hoping some of the clever bods here just happened to know the answer & could help me to avoid wasting time on what might turn out to be a wild goose chase. However, if you, Skip & mint all reckon that no such collection is exposed then I think I'll assume that Excel just keeps it to itself, and do things differently.

It is a bit more work, but I guess what I'll do is maintain a list of all the connectors in the diagram, including which objects they link from and to, and at what sites. Then I'll take that list and use it to generate a collection of "starting shapes" (keyed by name) each item of which contains a collection of the "end shapes" to which it is connected. That way, when I need to, I can instantly track down a given leg as required pretty much instantly.

Thanks anyway folks.

Tony

 

[PHV]

I'd have a look at the ConnectorFormat object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[N1GHTEYES]

PHV: According to the help, the ConnectorFormat object specifically applies to shapes which are connectors. It will tell me what shapes a given connector is connected to. Unfortunately, that is only part of the answer. What I need is a property, of ANY shape which a connector could be connected to, which gives me access to the collection of connectors connected to that shape.

Unfortunately, as far as I am aware, if the user clicks on a particular shape, I have no direct way, in code, of knowing which connectors go into or out of that shape (at least, not without going through ALL the connectors in the diagram and noting which ones happen to be connected to that shape). Obviously, Excel has such a list, but it seems like it does not expose it.

So, for example (partially in pseudocode), what I'd like to be able to do is:

have a user click on a shape, then hit some specific key combination, say Alt+S, and have that call the procedure "Select_Leg", as follows:

Dim namecount As Long
Dim MyNames() As String
Sub Select_Leg()
    'check if the current selection is a shape and if so, find it's name = startshape
    'then...
    ReDim MyNames(1 To ActiveSheet.Shapes.Count)
    Get_Leg startshape
    ReDim Preserve MyNames(1 To namecount) As String
    ActiveSheet.Shapes.Range(MyNames()).Select
    'else quit
End Sub
Sub Get_Leg(thisshape As Shape)
    Dim con As Shape
    Dim dependentshape As Shape
    namecount = namecount + 1
    MyNames(namecount) = thisshape.Name
    For Each con In thisshape.connectors.out
        namecount = namecount + 1
        MyNames(namecount) = con.Name
        Set dependentshape = con.ConnectorFormat.EndConnectedShape
        Get_Leg dependentshape
    Next con
End Sub

That way the user can, for example easily manipulate the layout of the diagram by choosing to move the whole branch which depends from any given item.

Unfortunately, what seems to be missing is the object Shape.Connectors.Out (or, for that matter Shape.Connectors.In).

Tony

 

[mintjulep]

Note my caution about start and end earlier in the thread.

 

[N1GHTEYES]

Noted, but not really understood. If I draw a connector from shape A to shape B, then the arrow on the connector (I am using arrowed connectors) will point from A to B. So I want the "begin" shape to be A and the "end" shape to be B. If, as you say, the "begin" is determined by which shape it was attached to first, then that will be A, which is what I want.

Am I missing something?

Tony

 

[SkipVought]

sub GetShapes
dim sp as shape

for each sp in ActiveSheet.Shapes
  with sp
    if .connector AND .name = selection.name then debug.print .name, .BeginConnectedShape.name, .EndConnectedShape.name
'now do stuff with these THREE shapes!!!
  end with
next
end sub

Skip,

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

 

[N1GHTEYES]

Skip: Thanks for the input. But I'm not clear on how it helps. If my user (actually, I am the user, I'm building something mainly to use for myself at the moment) selects a connector then your code would allow me to identify the shapes connected to it, but I'm not clear how that is meant to help. Just knowing the name of the selected connector I could go straight to the connected shapes anyway - I don't need to loop through the whole set of shapes to find it. And when I have found the shapes to which it is connected, again, so what? - what I'm trying to find is ALL the shapes which depend from a given (user selected) shape. Having found the name of a shape which a connector ends at does not help me find the NEXT connector.

I don't see how your suggestion helps me do that.

If Excel really does not expose its internal shape property which stores which connectors are connected to a given shape, then I think the best process is:

1 Build a collection of all the connectors, keyed by name, each item of which includes the name of the up- and down- stream shape.

2 Go through each item in that collection to build another collection holding all the other shapes (again, keyed by name) where each item lists the downstream and upstream connectors attached to each shape.

3 Then I can use a recursive routine, similar to what I put in a previous entry, to go from the user-selected shape to all the shapes downstream of it.

If you are suggesting a more efficient way of selecting a whole dependent branch, I'm afraid I'm not following it. Maybe my brain is distracted by the upcoming weekend.


Tony

 

[combo]

The way you pointed seems to be the only one working. However, a lot depends on the background you are working in. It's quite simple on a chartsheet (add a chart from an empty cell, set its location as a separate sheet), double-click fires selection:

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim shSelected As Shape, shX As Shape
If ElementID = xlShape And Arg1 > 0 Then
    Set shSelected = Me.Shapes(Arg1)
    shSelected.Select
    For Each shX In Me.Shapes
        If shX.Connector Then
            If shX.ConnectorFormat.BeginConnectedShape.Name = shSelected.Name Then shX.ConnectorFormat.EndConnectedShape.Select False
            If shX.ConnectorFormat.EndConnectedShape.Name = shSelected.Name Then shX.ConnectorFormat.BeginConnectedShape.Select False
        End If
    Next shX
End If
Cancel = True
End Sub

combo

 

[N1GHTEYES]

combo: Very sneaky! After a first quick glance, I think I see where you are coming form. However, I've almost finished coding this up by the other method. If I get it working before I go home, and if it is not too slow, I'll probably stick with it. Otherwise, I'll try the chart method. In any case, I'll try to remember it for future reference. Have a star.

As a matter of interest, I've just checked the basics (creation of new chart sheet, adding boxes, adding connectors, moving around etc), but when I zoom out the background area seems to stay a fixed size. Is there an easy way of changing that? I need to be able to zoom back a llooonnnggg way to see the layout of the whole diagram.

Tony

 

[mintjulep]

If I draw a connector from shape A to shape B, then the arrow on the connector (I am using arrowed connectors) will point from A to B. So I want the "begin" shape to be A and the "end" shape to be B. If, as you say, the "begin" is determined by which shape it was attached to first, then that will be A, which is what I want.

That's fine if you can always count on your user drawing the connectors in the right direction.

Since you say you are the only user, that might be good enough.

If you are using arrows then you have a visual check that the arrows are pointing in the right direction. So it might be safer to use .connectorformat.IsThereAnArrowAtThisEnd instead of .BeginConnectedShape and .EndConnectedShape.

 

[N1GHTEYES]

My point is that the direction of the arrows determines dependency. And the direction of the arrow is itself determined by the order in which it is drawn.

Of course, the user could connect A to B when he intended to connect B to A, but the code can't be expected to be telepathic. If he draws A->B then B is a dependent as far as the code is concerned, and that is how it should be.

Tony

 

[mintjulep]

And the direction of the arrow is itself determined by the order in which it is drawn.

As determined by the current settings for drawing connectors. Which can be over-ridden for individual connectors.

The only thing in life that you really count on is that if there is a way for a user to mess something up, then they will mess it up. So try to limit the possible ways that something can be messed up, and accommodate those that you can't eliminate.