Connector arrows that stick to cells: How to?

gwideman

New Member
Joined
Jan 29, 2019
Messages
2
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top