Adding a Line in Excel VBA, Changing the thickness and drawing "up"

Question

I am trying to draw lines with Excel VBA and it is working nicely except for 2 things, 1) How do I make the line thicker? 2) The lines that go up and to the right do not get drawn. The ones that are horizontal or go down and to the right draw just fine.

Here is my code:

Sub DrawLine(FromCell As Range, ToCell As Range)
 With FromCell.Parent.Shapes.AddLine(1, 1, 1, 1)
  .Left = FromCell.Left + (FromCell.Width)
  .Top = FromCell.Top + (FromCell.Height)
  .Width = (ToCell.Left) - .Left
  .Height = (ToCell.Top + (ToCell.Height)) - .Top
 End With
End Sub

Sub GoDraw()
 Call DrawLine(Range("D3"), Range("H3"))
 Call DrawLine(Range("D3"), Range("H7"))
 Call DrawLine(Range("D3"), Range("H11"))

 Call DrawLine(Range("D7"), Range("H3"))
 Call DrawLine(Range("D7"), Range("H7"))
 Call DrawLine(Range("D7"), Range("H11"))

 Call DrawLine(Range("D11"), Range("H3"))
 Call DrawLine(Range("D11"), Range("H7"))
 Call DrawLine(Range("D11"), Range("H11"))

End Sub

CtrlDot · Accepted Answer · 2014-05-08 04:33:14Z

1

Here is an article I wrote a few years ago on programming shapes with VBA. See the "Adding Connectors and Lines" section.

http://www.breezetree.com/articles/excel-2007-autoshapes-vba.htm

The lines that go "up and to the right" are called elbow connectors, and you can set that by setting the MsoConnectorType variable when adding a line.

answered May 8, 2014 at 4:33

CtrlDot

3,1223 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

S Meaden Over a year ago

Awesome! I had no idea Excel did this sort of Visio stuff!

Adam12344 Over a year ago

Hey Nick, thanks for the link. Great article. I don't think I was very clear when I said lines that go "up and to the right". I don't want the connectors that change directions, I need a straight line that goes from, for example, A3 to C1. For some reason the lines will only draw if they are horizontal or downward sloping, but not upward sloping.

CtrlDot Over a year ago

I think I understand what you mean now. Unfortunately, Excel provides very little control over routing the end points of free-standing lines. What I do is set Application.ScreenUpdating = False, add one small rectangle at the start point, another small rectangle at the end point, add a connector between them, and then delete the rectangles and set Application.ScreenUpdating = True.

Collectives™ on Stack Overflow

Adding a Line in Excel VBA, Changing the thickness and drawing "up"

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related