Adding lines to worksheet using VBA...

[hollifd]

I am trying to add lines to sheet1 using VBA. I have not been able to figure out how to change the color and thickness of the lines. Can someone help?

I want to be able to add a line using VBA, give the line a Name so that I can refer to it in code and move it around to the exact position that I want. Ideally, I want to create a line with an arrow facing to the right. After creating the line, I want to adjust the top, left, width, name, color, line weight, etc. I cannot seem to get the syntax correct for adjusting all of these properties.

Here is the code that I am using...

<pre>
Dim MyName as string
Set myDocument = Worksheets(1)
Counter = Counter + 1
With myDocument.Shapes.AddLine(10, 50, 100, 50)
MyName = .Name
.Height = 0
End With
</pre>

Thanks for any help,
David

 

[VoG]

If you add a line whilst recording a macro you should get the correct syntax.

 

[hollifd]

In 2007, it appears that the Macro recorder no longer records the actions associated with creating lines.

Anyone else have any code samples for me to create a simple line with an arrow and allow me to give the line a name, color, line thickness, top, left, width values?

Thanks,
David

 

[VoG]

From the recorder in Excel 2010

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 144.75, 99, 482.25, 232.5 _
        ).Select
        Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .Weight = 2
    End With
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
End Sub

 

[hollifd]

Almost there...
How can I give it a name so that I can refer to it in code.

I tried

.Name and it does not like that.

Thanks,
David

 

[hollifd]

Nevermind...

I got it. This does it for me...

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 144.75, 99, 482.25, 232.5).Select
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
Selection.Name = "MyShapeNameGoesHere"


Thanks for all of your help.
David