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 three 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 (this blog) Formatting shapes and adding text Adding lines and connectors to join shapes together 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 three 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 (this blog)
Formatting shapes and adding text
Adding lines and connectors to join shapes together
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

Naming, referring to and positioning shapes

Once you've added a shape, you'll want to be able to refer to it in the future - and you'll also need to know how to position it exactly where you want on the screen.

Adding a shape without getting a reference to it

Consider the following block of code, which adds a square to the current worksheet:

Sub AddSquare()

Dim ws As Worksheet

'add a square

Set ws = ActiveSheet

ws.Shapes.AddShape 1, 50, 50, 100, 100

End Sub

The problem with this method is that there's no easy way to refer to the shape after adding it. If it's the only shape added to date, this would work:

'move the shape just added

ws.Shapes(1).Left = 200

However, referring to a shape by its index number within the collection of shapes clearly isn't a reliable method.

Adding a shape while at the same time getting a reference to it

A much better way to add a shape is by setting an object variable to refer to it immediately after adding it:

Sub AddSquare()

Dim ws As Worksheet

Dim sq As Shape

'add a square

Set ws = ActiveSheet

Set sq = ws.Shapes.AddShape(1, 50, 50, 100, 100)

'give this shape a unique name

sq.Name = "WOL_0001"

End Sub

Note that (as always in VBA) if you're capturing a reference to something,
you need to include brackets round the argument list. The advantage of the above approach is that you can now refer to the shape that's been added either by the object variable used:

'can then refer to this by variable now ...

sq.Left = 200

Or by its name:

'... or by name later

ws.Shapes("WOL_0001").Left = 300

Shape names are a bit strange in VBA, and are not necessarily unique. Rather than trying to understand the rules, the simplest thing is to generate and assign unique names yourself for shapes that you've created programmatically.

Positioning shapes relative to cells

You've already seen that when you position a shape you do it relative to the top left corner of a worksheet. However, it's easy enough to change this to position a shape relative to a cell:

To position a shape in the shaded cell, we just need to know the two distances shown.

For example, supposing that we want to add a shape within the cell like this:

The shape is half the width of the cell, and half its height.

Here's some sample code to do this:

Sub AddShapeToCell()

Dim c As Range

Dim ws As Worksheet

Set ws = ActiveSheet

Set c = Range("B4")

'add shape to cell, centre-aligned vertically and horizontally

ws.Shapes.AddShape Type:=msoShape10pointStar, _

Left:=c.Left + (c.Width / 4), _

Top:=c.Top + (c.Height / 4), _

Width:=c.Width / 2, _

Height:=c.Height / 2

End Sub

This code will position the shape relative to the top left corner of the cell, rather than of the worksheet.

One thing to watch is that the units for column widths/row heights aren't the same as for shapes!

Now that we know how to refer to our shapes (and how to position them where we want on screen), it's time to make them look pretty!

Parts of this blog
Working with shapes in VBA Working with shapes - getting started Naming, referring to and positioning shapes (this blog) Formatting shapes and adding text Adding lines and connectors to join shapes together Working with multiple shapes 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:	Marcv
When:	11 Feb 21 at 22:38

Reply to Marcv

This post has been very helpful.

I've used the sq.Name = WOL_0001 in my script and it works great for referencing the objects in the code. Once the macro completes is there a command to clear the assigned names from the objects on the sheet so that they can be used again for the next iteration?

My specific code is grouping the objects with user-defined info to be used in a visual layout. When the code is run on the next set of information it works fine until it goes to group the objects; at this point it errors because it's selecting all the already named objects from the first pass, and these are already grouped, causing of the error. The underlying issue is the code is selecting the wrong objects after the first pass. Once they are grouped I have no need for the assigned name reference anymore and need to start the code over.

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

Naming, referring to and positioning shapes

Adding a shape without getting a reference to it

Adding a shape while at the same time getting a reference to it

Positioning shapes relative to cells

Head office

London

Manchester