drawing a line with VBA

[plost33]

How would i have VBA draw a line between a two ranges? say i have a named range of "start" and another name range "stop". i want to draw a red line between them. how can i do that with code. this is beggining my education for a much larger project I want to do later in the future.

 

[plost33]

how would i say the bottom right side?

 

[VoG]

Try

Sub AddLine()
Dim l1 As Long, l2 As Long, r1 As Long, r2 As Long
l1 = Range("Start").Left + Range("Start").ColumnWidth * 6
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(255, 0, 0)
End With
End Sub

you may need to adjust the value 6 to suit.

 

[plost33]

cool. okay now for the final step...how can i delay a macro to run only one loop per second?

 

[VoG]

Try putting this in your loop

Application.Wait Now + TimeSerial(0, 0, 1)

 

[plost33]

is that (hrs, minutes, seconds)??

 

[VoG]

Yes - hours, minutes, seconds - but here's a hint. Click in the word TimeSerial in the Visual Basic Editor and press F1 to display the help

 

[plost33]

when i run the code now, it is now delaying. it does seem to wait a second before any lines are displayed, but they both appear at the same time. I do not have it in a loop yet, but i will as i continue to build this code. will having it in the code as i do below not make it pause 1 second before moving on? also, will excel always wait to display objects, such as lines, all at once, once the code and finished running? I am trying to make a kind of animation which will draw several lines, but only draw one per second.

Sub AddLine()
Dim l1 As Long, l2 As Long, r1 As Long, r2 As Long, x1 As Long, x2 As Long
l1 = Range("GI40F").Left 'left side of range
l2 = Range("GI40F").Top + Range("Start").RowHeight ' bottom left corner of range
r1 = Range("GI47C").Left
r2 = Range("GI47C").Top
x1 = Range("WD69C").Left + Range("Start").ColumnWidth * 4 'right side middle
x2 = Range("WD69C").Top + Range("Start").RowHeight ' bottom left corner of range
With ActiveSheet.Shapes.AddLine(l1, l2, r1, r2).Line
    .ForeColor.RGB = RGB(255, 0, 0) 'red
End With
Application.Wait Now + TimeSerial(0, 0, 1) ' waits 1 second before proceeding
With ActiveSheet.Shapes.AddLine(r1, r2, x1, x2).Line
    .ForeColor.RGB = RGB(0, 0, 255) 'blue
End With

End Sub
[\code]

 

[plost33]

i meant to say it is NOT delaying...

 

[VoG]

It should delay by one second before adding the second line. Try increasing the 1 to say 3 and see if there is a discernible delay.

 

[plost33]

it just waits longer then adds all lines at once rather than one at a time seperated by the 1 or 3 second interval.