Connector arrows that stick to cells: How to?

[gwideman]

I would like to find a way to draw arrows between cells in such a way that the arrow stays connected to those cells even when one of the cells is moved. I am using Excel 2010.

If I simply draw an arrow (Insert > Shapes >Arrow) between two cells, then it remains stuck to that cell if I resize intervening rows or columns, or insert/delete intervening rows or columns. But if I select the cell and drag it to a different cell, or I move its row or column, then the arrow fails to follow the cell it was originally connected to. Instead it remains pointing to the same position on the sheet. So that's not the answer.

If instead I first insert a shape (Insert > Shapes > ellipse ) into the source and destination cells, I can then insert an arrow that connects to those two ellipses. If I move one of the ellipses, the arrow stays stuck to it. But then if I move the cell containing the ellipse, the ellipse moves, but the connected arrow appears not to follow the ellipse to the new cell. However, if I make a minor adjustment to the ellipse (say a tiny move or resize), the arrow repaints to show it's connected. This is apparently a bug: The arrow retains data that it is connected, but fails to repaint without some edit that forces some particular kind of recalc. Unfortunately, various moves like Save/Load, or resizing the columns or the entire sheet fail to prompt these wrong-rendered arrows to update.

So -- I am hoping that someone has explored this further, and either knows an alternative way to achieve arrows that connect to cells robustly, or has a clever way to work around the bug I just described.

(Also, I do realize that what I'm looking for somewhat overlaps with "Trace Precedents", but that functionality would be very awkward to use in this scenario, not the least problem being that the cells I want to put arrows between don't have a formula dependency, and even if they did, it's difficult to turn on the arrows and get then to stay on while continuing to work.)

Thanks. Graham

 

[Doflamingo]

Hi,

Try this code

It works if you rename a cell ''start'' and the other one ''stop''

Sub AddLine()
Dim l1 As Long, l2 As Long, r1 As Long, r2 As Long
l1 = Range("Start").Left
l2 = Range("Start").Top + Range("Start").RowHeight
r1 = Range("Stop").Left
r2 = Range("Stop").Top
With ActiveSheet.Shapes.AddLine(l1, l2, r1, r2).Line
.ForeColor.RGB = RGB(0, 0, 0)
End With
End Sub


Regards

 

[gwideman]

Hi Doflamingo. I appreciate your answering, but so far as I can tell, all this does is use VBA to attach a line to two cells, just like a user could do manually in the UI. Subsequently moving the cell still fails to move the connected endpoint with it, which I described was the problem I'm trying to solve.

Hi,

Try this code

It works if you rename a cell ''start'' and the other one ''stop''

Sub AddLine()
Dim l1 As Long, l2 As Long, r1 As Long, r2 As Long
l1 = Range("Start").Left
l2 = Range("Start").Top + Range("Start").RowHeight
r1 = Range("Stop").Left
r2 = Range("Stop").Top
With ActiveSheet.Shapes.AddLine(l1, l2, r1, r2).Line
.ForeColor.RGB = RGB(0, 0, 0)
End With
End Sub


Regards

 

[EERS]

I would like to find a way to draw arrows between cells in such a way that the arrow stays connected to those cells even when one of the cells is moved. I am using Excel 2010.

If I simply draw an arrow (Insert > Shapes >Arrow) between two cells, then it remains stuck to that cell if I resize intervening rows or columns, or insert/delete intervening rows or columns. But if I select the cell and drag it to a different cell, or I move its row or column, then the arrow fails to follow the cell it was originally connected to. Instead it remains pointing to the same position on the sheet. So that's not the answer.

If instead I first insert a shape (Insert > Shapes > ellipse ) into the source and destination cells, I can then insert an arrow that connects to those two ellipses. If I move one of the ellipses, the arrow stays stuck to it. But then if I move the cell containing the ellipse, the ellipse moves, but the connected arrow appears not to follow the ellipse to the new cell. However, if I make a minor adjustment to the ellipse (say a tiny move or resize), the arrow repaints to show it's connected. This is apparently a bug: The arrow retains data that it is connected, but fails to repaint without some edit that forces some particular kind of recalc. Unfortunately, various moves like Save/Load, or resizing the columns or the entire sheet fail to prompt these wrong-rendered arrows to update.

So -- I am hoping that someone has explored this further, and either knows an alternative way to achieve arrows that connect to cells robustly, or has a clever way to work around the bug I just described.

(Also, I do realize that what I'm looking for somewhat overlaps with "Trace Precedents", but that functionality would be very awkward to use in this scenario, not the least problem being that the cells I want to put arrows between don't have a formula dependency, and even if they did, it's difficult to turn on the arrows and get then to stay on while continuing to work.)

Thanks. Graham

hi Graham,
I am also trying the similar one. Connect header rows of 2 different tables.

Did you find any solution?