This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Working with shapes in VBA Working with shapes - getting started Naming, referring to and positioning shapes Formatting shapes and adding text Adding lines and connectors to join shapes together (this blog) Working with multiple shapes Assigning macros to shapes 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.

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!

Working with shapes in VBA
Working with shapes - getting started
Naming, referring to and positioning shapes
Formatting shapes and adding text
Adding lines and connectors to join shapes together (this blog)
Working with multiple shapes
Assigning macros to shapes
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

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:

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):

The arguments when adding a line.

For example, suppose that you wanted to add this 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:

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

Adding a connector

To add a connector:

Add a connector shape, as you would do a line.
Tell it which shape to begin at.
Tell it which shape to end at.
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!

Parts of this blog
Working with shapes in VBA Working with shapes - getting started Naming, referring to and positioning shapes Formatting shapes and adding text Adding lines and connectors to join shapes together (this blog) Working with multiple shapes Assigning macros to shapes Exercises on shapes

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC