Returning Position Points from the currently selected cell

[Guest]

Consider the following code that makes a short, horizontal line:

ActiveSheet.Shapes.AddLine(10, 10, 25, 10).Select

AddLine needs coordinates (position points) in order to make a line. Now
consider any currently selected cell (Excel). A selected cell makes a square
or rectangle or some 4-sided shape (polygon?). How to return the four
coordinates from the currently selected cell? I will then use those
coordinates to draw a line the exact with of the cell.

I'm trying to automate the drawing of a Gantt chart in Excel using lines.
Selecting a range of cells and drawing a line between the extremeties will be
helpful. I don't think I want to use a bar chart--I've seen some examples of
those. Thanks.

Troy

 

[Jim Cone]

Troy,

Something like this?...
'-------------------------
'Adds a horizontal line thru the middle of D10:E20
Sub Test()
Dim shpLine As Excel.Shape
With Range("D10:E20")
Set shpLine = ActiveSheet.Shapes.AddLine(.Left, .Top + (.Height / 2), _
.Left + .Width, .Top + (.Height / 2))
End With
shpLine.Line.Weight = 1.5
Set shpLine = Nothing
End Sub
'---------------------------------------
Jim Cone
San Francisco, USA

 

[Guest]

Thanks Jim, this is very helpful!

Troy,

Something like this?...
'-------------------------
'Adds a horizontal line thru the middle of D10:E20
Sub Test()
Dim shpLine As Excel.Shape
With Range("D10:E20")
Set shpLine = ActiveSheet.Shapes.AddLine(.Left, .Top + (.Height / 2), _
.Left + .Width, .Top + (.Height / 2))
End With
shpLine.Line.Weight = 1.5
Set shpLine = Nothing
End Sub
'---------------------------------------
Jim Cone
San Francisco, USA