How to create autoshapes, lines and connectors in VBA macros
Part five of an eight-part series of blogs

You can use Visual Basic within Excel, PowerPoint or Word to draw shapes, format them and even assign macros to run - this long blog gives lots of ideas of how to proceed!

  1. Working with shapes in VBA
  2. Working with shapes - getting started
  3. Naming, referring to and positioning shapes
  4. Formatting shapes and adding text
  5. Adding lines and connectors to join shapes together (this blog)
  6. Working with multiple shapes
  7. Assigning macros to shapes
  8. Exercises on shapes

This is one small part of our free online Excel VBA tutorial.  To find out how to learn in a more structured way, have a look at our training courses in VBA.

Posted by Andy Brown on 25 January 2014

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Adding lines and connectors to join shapes together

This blog doesn't claim to be exhaustive - there are a LOT of things you can do with shapes:

Some shape commands

Just some of the things you could experiment with!

 

Here we'll just look at two additional types of shape: lines and connectors.

Adding lines

To add a line, specify its start and end point (logical, really):

Line arguments

The arguments when adding a line.

For example, suppose that you wanted to add this red line:

Red line

This line starts at (30,10) and ends at (100,50).

 

Here's the code to add the line above:

Sub AddRedLine()

Dim ws As Worksheet

Dim s As Shape

Set ws = ActiveSheet

Set s = ws.Shapes.AddLine(30, 10, 100, 50)

s.Line.ForeColor.RGB = RGB(255, 0, 0)

End Sub

Types of connector

When adding connectors, it helps to know that there are 3 types:

Three types of connector

As you move each owl around, the connector will stay ... connected to it!

Adding a connector

To add a connector:

  1. Add a connector shape, as you would do a line.
  2. Tell it which shape to begin at.
  3. Tell it which shape to end at.
  4. Finally, reroute the connections (this basically makes sure that the connector is where it should be on screen, given the current position of the two shapes it's linking together).

To draw the connector on the left above (the curved one), first create the two shapes (the textbox and the owl logo):

Sub CreateShapes()

'text box and owl logo, plus connector

Dim rect As Shape

Dim Logo As Shape

Dim conn As Shape

'set reference to a worksheet

Set w = ActiveSheet

'add the text box

Set rect = w.Shapes.AddShape(1, 10, 10, 80, 20)

rect.TextFrame.Characters.Text = "Curve"

rect.Fill.ForeColor.RGB = RGB(227, 214, 213)

rect.TextFrame.Characters.Font.ColorIndex = 1

'now add the owl logo (initially a rectangle)

Set Logo = w.Shapes.AddShape(1, 30, 80, 50, 50)

Logo.Fill.UserPicture "C:\ajb files\wise-owl-logo.jpg"

You'll need to use a different picture, of course, if you want to reproduce this.  Next, add the connector:

'add the connector linking together (doesn't matter where)

Set conn = w.Shapes.AddConnector(msoConnectorCurve, 1, 1, 1, 1)

Note that it's not worth thinking about its position or size, as when you reroute it VBA will redraw it.  Now say what the connector is connecting!

conn.ConnectorFormat.BeginConnect rect, 1

conn.ConnectorFormat.EndConnect Logo, 1

The second argument above specifies whether you're connecting the top, left, right or bottom of the given shape, but it doesn't seem to make much difference what value you use.

Finally, you should update the position of the connector:

'redraw the connector

conn.RerouteConnections

End Sub

The cool thing now is that as you drag either shape around the connector will follow it! 

This blog has 0 threads Add post