562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
|
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.
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.
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!
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.
Parts of this blog |
---|
Some other pages relevant to the above blogs include:
From: | yaron-soli |
When: | 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?
From: | Andy B |
When: | 15 Sep 20 at 12:33 |
I don't understand quite what you're trying to do?
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.