WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 529 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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!

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.

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

Dim ws As Worksheet

Dim c1 As Shape

Dim c2 As Shape

Set ws = ActiveSheet

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:

Dim ws As Worksheet

Dim c1 As Shape

Dim c2 As Shape

Dim s As Shape

Set ws = ActiveSheet

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)

Next s

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 If

Next s

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.

This blog has 1 thread
15 Sep 20 at 11:51

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?

Andy B
15 Sep 20 at 12:33

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