Change the connecting point of an arrow head ?


I have made some Shapes on a WorkSheet - like this:


I want to switch the arrows so they are NOT crossing each other and it should be the right (the head) end of the arrows that should be switched.


Let's say the shapes have these names:

"1" for the blue person

"6" for the pink person

"1+6" for the green couple

"1->1+6" for the climbing arrow

"6->1+6" for the descending arrow


I have tried some code like this but with no luck:




Public Sub bytPileTilPAR(f1 As String, f2 As String)
    Dim namePIL As String
    ' trying to guess the name of the couple
    If ActiveSheet.Shapes.Item(f1 & "+" & f2).Name = f1 & "+" & f2 Then
        ' then the name of the arrows - one by one 
        namePIL = f1 & "->" & f1 & "+" & f2
        ActiveSheet.Shapes(f1).Connector.ConnectorFormat.BeginConnect ActiveSheet.Shapes(f1), 1
        ActiveSheet.Shapes(f1).Connector.ConnectorFormat.EndConnect ActiveSheet.Shapes(namePIL), 5
         some code if the couple-name was wrong 
    End If
End Sub




I focus on the TRUE part of the IF-THEN in the first place - I think I can manage to do the other part when the TRUE-part is working right.


How can the arrows be switched around ?


9 Replies
I've changed to DELETE the old arrows and add them again in the right position.


It is a few years since I have controlled shapes using VBA.  One of the tricks I remember using was to generate the diagram by hand and then limit the code to toggling the visibility of alternative shapes to create dynamic shape diagrams.  Since I was drawing the shapes by hand, I needed to read the connection port numbers but not to change the linkage.  Drawing with freeform connection objects was fun though.

In the longer run I plan to automate it very much further - it's to be used in building a family tree


That gets 'interesting' quite quickly.  What is your data structure to hold the family tree?  Assuming the diagram covers the entire tree, as opposed to navigating a restricted view, you have two persistent (still there after switching the application off and on again) representations of the tree (data and diagram).  Which is the master?  It is possible to require the diagram to update when you change the data held within linked tables or to change the data when you add elements to the diagram.  Or you could accept both and use VBA Class modules (a transient representation) and events to synchronise the other whenever either representation is changed.

I have my genealogical research in an MS Access DB and this 'version minus 2' of the tree structure is just to get all the SUBs for showing persons, couples and their children to work right. I too have a lot of SUBs to move selected groups of shapes. All these SUBs are now controlled manually by buttens on the Quick Access Toolbar.
When I implement the 'automated version' - I think it will be better to control the Excel SUBs from inside Access controlled by an appropriate algorithm.
From what I already have done 'automatic generating' the tree from within Access into Excel, I think I'll just redraw the tree when needed.
In fact teh tree quickly becomes very unmanageable anyway due to the small screen in relation to the size of the tree.


If you restrict the view too much, one loses the sense of location as one navigates the tree.  On the other hand, include too many generations and diagram layout issues become a nightmare!  You mention the QAT.  One thing I found useful was the shape's 'on action' event handling which would allow you to click a person icon with the implied question of "tell me more about ...".

I use the OnAction to switch from the tree to that clicked person showing him/her up in Access and then you can edit all infos about that person.
Too I use the ToolTip of the shapes in Excel to show infos - the couple symbol show date/place for the marriage and of minor value - the arrows have ToolTips too.
I now have to look for a implementable algorythm for building the tree ...
best response confirmed by keldsor (Contributor)


I had wondered whether to send you a copy of a program I wrote years ago (2014) to analyse diagrams and plot fragments of the graph dynamically.  The problem is that it would take me a week to remember and get to grips with the techniques I used, so I am not sure how you would fare.


It uses Class modules for the Graph object itself and for the GraphNode and GraphEdge collections that comprise it.  In your case, that might mean having a Class 'FamilyTree' with methods to Add people as instances of the Person class.  Each oPerson ('o' for Object) might have properties such as '.Name', '.DOB' and relationships such as .Parent, .Partner, .EldestChild or .Sibling that have other objects from the Person Class as their values.


If that isn't bad enough, I also adopted someone else's central error checking code, so every module starts with standard set of module name identifiers and initialises the error variable to show an error unless the module subsequently reaches the 'success' setting.  


It is odd how one's own past work can lie so far outside one's comfort zone!

I too know that problem way too well !
I think I'll have to work with this 'manual' building of the tree for a while ... just to experience what problems comes up !
MAYBE I can work out an algorythm working very systematically - but time will show !
I think I remember from my math-lectures, it should be possible to make a tree completely flat = no crossing arrows - so I'll try that manually in the first place.
In the longer run it would be nice to start that algorythm i MSAccess and then switch to the worksheet in Excel to see the magic building the tree automatically !