How to execute a line-drawing subroutine/macro?

[JDoe314]

I am a complete novice in excel and VBA scripting. I need to know how to execute this subroutine:

Sub Connect(r1 As Range, r2 As Range) 
    ActiveSheet.Shapes.AddLine r1.Left + r1.Width / 2, r1.Top + r1.Height / 2, _ 
    r2.Left + r2.Width / 2, r2.Top + r2.Height / 2 
End Sub

called like:

Connect Range("A3"), Range("D7")

that I found in an excel forum. My intention is to eventually be able to select two cells and then run a sub to draw a line between them. I suppose this would mean having some sort of thing to extract the cell locations from the selected cells, and then running the aforementioned subroutine with the selected cell locations as inputs.

[patel45]

find attached example, you have to select the whole range, not only the 2 cells

[JDoe314]

Hi!

Thanks a lot for this. However, my computer is flagging it with some message saying that it doesn't trust the file. Just to be on the safe side, could you tell me the code you used? I hate to be so skeptical, but I also want to work on my VBA coding skills.

Also, I've been working on it myself, and I got the line drawing part to work by calling the subroutine in another macro. I want to get it to draw arrows instead of lines and take the selected cells as a range.

Sub Connect(r1 As Range, r2 As Range)
    ActiveSheet.Shapes.AddLine r1.Left + r1.Width / 2, r1.Top + r1.Height / 2, _
    r2.Left + r2.Width / 2, r2.Top + r2.Height / 2
End Sub

Sub Macro4()
'
' Macro4 Macro
'

'
    Connect Range("A3"), Range("D7")
End Sub

Thanks!

[patel45]

do not have to take into account the safety messages

Sub ArrowMakerLeftToRightDown()
'Select Range and run this macro
 With Selection
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, _
    .Left, .Top, .Left + .Width, .Top + .Height).Select
 End With
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub
Sub ArrowMakerRightToLeftDown()
'Select Range and run this macro
 With Selection
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, _
    .Left + .Width, .Top, .Left, .Top + .Height).Select
 End With
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub

Sub ArrowMakerLeftToRightUp()
'Select Range and run this macro
 With Selection
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, _
    .Left, .Top + .Height, .Left + .Width, .Top).Select
 End With
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub
Sub ArrowMakerRightToLeftUp()
'Select Range and run this macro
 With Selection
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, _
    .Left + .Width, .Top + .Height, .Left, .Top).Select
 End With
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub

Sub ArrowMakerLeftToRightTOP()
'Select Range and run this macro
 With Selection
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, _
    .Left, .Top, .Left + .Width, .Top).Select
 End With
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub
Sub ArrowMakerRightToLeftTOP()
'Select Range and run this macro
 With Selection
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, _
    .Left + .Width, .Top, .Left, .Top).Select
 End With
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub
Sub ArrowMakerLeftTopToDown()
'Select Range and run this macro
 With Selection
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, _
    .Left, .Top, .Left, .Top + .Height).Select
 End With
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub
Sub ArrowMakerLeftDownToTop()
'Select Range and run this macro
 With Selection
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, _
    .Left, .Top + .Height, .Left, .Top).Select
 End With
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub

[JDoe314]

Thanks a lot. In the meantime, I was able to actually make a program myself, although much simpler than yours. I will take a look at your code so I can understand some of the subtleties. Anyhow, for the benefit of anyone stumbling across the thread in the future, here is my bare-bones version of the program:

Sub Connect(r1 As Range, r2 As Range)
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, r1.Left + r1.Width / 2, r1.Top + r1.Height / 2, _
    r2.Left + r2.Width / 2, r2.Top + r2.Height / 2).Select
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub

Sub Macro4()
'
' Macro4 Macro
'

'

With Selection

    Connect Range(.Cells(1).Address(0, 0)), Range(.Cells(.Cells.Count).Address(0, 0))
End With
End Sub

I was actually inspired by your idea of using a range selection to identify the cells of interest.