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!

  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
  6. Working with multiple shapes (this blog)
  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.

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:

Colouring circles

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:

ShapeRange autocompletion - not

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)

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:

Two circles and a rectangle

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!

Other shape types

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 0 threads Add post