This page has 1 thread | 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 six 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 Working with multiple shapes (this blog) 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 six 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
Working with multiple shapes (this blog)
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

Working with multiple shapes

There are two main ways to work with a number of shapes at the same time: by using the ShapeRange object, or by looping over a collection of shapes.

Most Microsoft examples use the first method (the ShapeRange object); I find the second much easier to work with, however. Examples of both are shown below.

Using the ShapeRange object

You can use an object of type ShapeRange to get access to a set of shapes, allowing you to set properties and apply methods to a set of shapes simultaneously. Here's an example of its use:

We want to colour these circles pink, and give them a red border.

One way to do this is to draw the shapes, then select them:

Sub AddCircles()

Dim ws As Worksheet

Dim c1 As Shape

Dim c2 As Shape

Set ws = ActiveSheet

'add two circles

Set c1 = ws.Shapes.AddShape(msoShapeOval, 10, 10, 50, 50)

Set c2 = ws.Shapes.AddShape(msoShapeOval, 70, 10, 50, 50)

'now format them both

ws.Shapes.SelectAll

Now that the shapes are selected, we can apply the ShapeRange method to the current selection to return a set of shapes (I did warn you that I preferred the other method!):

'get a reference to this set of shapes

Dim sr As ShapeRange

Set sr = Selection.ShapeRange

'colour these shapes pink with red border

sr.Fill.ForeColor.RGB = RGB(250, 220, 240)

sr.Line.ForeColor.RGB = RGB(250, 100, 150)

End Sub

You can abbreviate this as follows:

'now format them both

ws.Shapes.SelectAll

'colour these shapes pink with red border

Selection.ShapeRange.Fill.ForeColor.RGB = RGB(250, 220, 240)

Selection.ShapeRange.Line.ForeColor.RGB = RGB(250, 100, 150)

The problem with this approach is that it doesn't allow for autocompletion:

Commands beginning with Selection don't support Intellisense.

Because of the above limitation, it's easier to create an intermediate ShapeRange variable and use this to refer to a set of shapes.

Looping over a collection of shapes

It's just so much easier (I humbly submit) to loop over all of the shapes, colouring each in turn:

Sub AddCircles()

Dim ws As Worksheet

Dim c1 As Shape

Dim c2 As Shape

Dim s As Shape

Set ws = ActiveSheet

'add two circles

Set c1 = ws.Shapes.AddShape(msoShapeOval, 10, 10, 50, 50)

Set c2 = ws.Shapes.AddShape(msoShapeOval, 70, 10, 50, 50)

'format each of these shapes

For Each s In ws.Shapes

s.Fill.ForeColor.RGB = RGB(250, 220, 240)

s.Line.ForeColor.RGB = RGB(250, 100, 150)

End Sub

I accept that the above code probably runs more slowly, but you would have to have a serious number of shapes on a worksheet for this to matter!

Checking the type of shapes

One useful thing to be able to do is to check what type each shape is as you loop over it. You can do this by testing a shape's Type, and then more specifically its AutoShapeType:

We'll write code to colour the ovals, but not the rectangle.

Here's some code to achieve the above:

Sub FormatOnlyCircles()

Dim ws As Worksheet

Dim c1 As Shape

Dim c2 As Shape

Dim r1 As Shape

Dim s As Shape

Set ws = ActiveSheet

'add two circles ...

Set c1 = ws.Shapes.AddShape(msoShapeOval, 10, 10, 50, 50)

Set c2 = ws.Shapes.AddShape(msoShapeOval, 70, 10, 50, 50)

'... and a rectangle

Set r1 = ws.Shapes.AddShape(msoShapeRectangle, 10, 70, 110, 50)

'format just the circles

For Each s In ws.Shapes

'first check for autoshapes

If s.Type = msoAutoShape Then

'now check this autoshape is a "circle"

If s.AutoShapeType = msoShapeOval Then

s.Fill.ForeColor.RGB = RGB(250, 220, 240)

s.Line.ForeColor.RGB = RGB(250, 100, 150)

End If

End Sub

Although this blog has concerned itself almost exclusively with autoshapes, there are lots of other shapes that you can add!

Just some of the other shape types that you can add to a workbook!

Having spent all of this time looking at how to create shapes, let's now take a quick look at how to assign macros to them.

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 Working with multiple shapes (this blog) Assigning macros to shapes Exercises on shapes

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	yaron-soli
When:	15 Sep 20 at 11:51

Reply to yaron-soli

HI,

I have multiple shapes in a sheet (RoundedRectangle) and want each time to pick another one and to change its text box. I've given a name to each RoundedRectangle (i have almost 30 of them). How can i do that?

From:	Andy B
When:	15 Sep 20 at 12:33

Reply to Andy B

I don't understand quite what you're trying to do?

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

BLOGS BY AUTHOR

Working with multiple shapes

Using the ShapeRange object

Looping over a collection of shapes

Checking the type of shapes

Head office

London

Manchester