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
 

Doflamingo

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

Forum statistics

Threads
1,078,500
Messages
5,340,743
Members
399,393
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top