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

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


"Keys1970" wrote in message
...
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

Thanks Jim, this is very helpful!

"Jim Cone" wrote:

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


"Keys1970" wrote in message
...
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