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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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

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

Watch MrExcel Video

Forum statistics

Threads
1,089,751
Messages
5,410,230
Members
403,303
Latest member
pajg

This Week's Hot Topics

Top