0

I created a macro in excel to insert a red arrow via keyboard command. I figured out thru research how to make it stop selecting the cell that was selected when I wrote the macro, but I can't figure out how to make it insert the arrow next to my current selection every time. It currently inserts the arrow in the same spot as my original line from when I recorded the macro. Is there a way around this?

Here is the code:

Sub Red_Arrow_Insert()

 Red_Arrow_Insert Macro
 Insert Red Arrow

 Keyboard Shortcut: Ctrl+Shift+A

    ActiveCell.Select
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 264, 50.25, 353.25, 139.5 _
        ).Select
        Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .Weight = 1.5
    End With
    ActiveCell.Select
End Sub
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
Jim O
  • 1
  • 1
  • 1
  • 1
  • read this : https://msdn.microsoft.com/en-us/library/office/ff834664.aspx – teddy2 Sep 07 '16 at 18:43
  • Also, I suggest reading through [how to avoid using `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) It's doing it in the same cell, because your `ActiveCell` never changes. – BruceWayne Sep 07 '16 at 18:45
  • Sorry, that might be a bit above my level, I am not much of a programmer. Does that mean that my request can be done because everything always has to be defined relative to the upper left corner of the document? – Jim O Sep 07 '16 at 18:55

1 Answers1

1

This request can be done by using the Top and Left property of the ActiveCell and using those numbers to place the arrow, since the Top and Left property are measured against the upper left corner of the document and assuming the arrow will always be a static length.

See the refactored code below:

Sub Red_Arrow_Insert()

'Red_Arrow_Insert Macro
'Insert Red Arrow
'Keyboard Shortcut: Ctrl Shift + A

Dim l As Long, t As Long

l = ActiveCell.Left
t = ActiveCell.Top

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, t + 89.25, l + 89.25, l, t).Select

With Selection
    With .ShapeRange.Line
        .EndArrowheadStyle = msoArrowheadOpen
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Weight = 1.5
    End With
End With

ActiveCell.Select 'assumes you want to activate the last active cell.

End Sub

n.b. -- I used 89.25 as the length since it was the difference in points in your original code. Change as needed.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Scott - Thanks this is almost exactly what I need. Is there something in there that is controlling the length of the arrow? It seems okay if I am in the upper left 100 or so cells of the grid, but when I go down to say S,200, the line becomes very long. – Jim O Sep 07 '16 at 19:20
  • 1
    @JimO - play with the `t + 89.25, l + 89.25, l, t` arguments. It's basically begin and end point of arrow, so you need to do the math to figure out exactly how to make it the length you want. – Scott Holtzman Sep 07 '16 at 19:23
  • I think you meant to use `ActiveSheet.Shapes.AddConnector(msoConnectorStraight, l - 89.25, t - 89.25, l, t).Select`. Your current line has t and l switched in the start positions, and you have the arrow pointing away from the cell. (But I don't know what the OP wants to happen when the cell is near the top or left of the document - `t - 89.25` or `l - 89.25` may become negative.) – YowE3K Sep 07 '16 at 20:02