This page has 0 threads | 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 four 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 (this blog) 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 four 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 (this blog)
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

Formatting shapes and adding text

Formatting shapes is easy provided that you understand that there are two main properties that you can play about with:

Property	What it allows you to change
Fill	The background colour or pattern for a shape
Line	The border line for a shape

One other oddity: it's the ForeColor of a shape's fill that you'll usually want to change:

It's the foreground colour of a shape's fill pattern that'll usually be of most interest.

An example of formatting a shape's fill pattern

Here's some code to create a pretty circle:

'add a circle

Set s = ws.Shapes.AddShape(9, 10, 10, 50, 50)

'format this circle

With s.Fill

'give the shape a pinkish colour

.ForeColor.RGB = RGB(255, 240, 255)

'transparency goes from 0 (opaque) to 1

'(completely transparent)

.Transparency = 0.2

End With

This would create a circle which is 20% transparent and has a different background colour:

The circle created by the above code.

An example of formatting a shape's border

You could extend the macro above to change the border colour and style of the shape:

'now make the border purple and dotted

With s.Line

.ForeColor.RGB = RGB(100, 0, 100)

.DashStyle = msoLineDashDot

.Weight = 5

End With

The circle is now beginning to look a bit silly:

Perhaps our choices weren't so good after all ...

To do detailed formatting of shapes, a good way forward is to record a macro and then play about with the code generated.

Adding text to shapes

Any shape added has a TextFrame property, which gives you access to the text within it. This in turn has a collection of Characters!

'add and format text in call-out

CalloutBalloon.TextFrame.Characters.Text = "Wise Owl blog on shapes"

CalloutBalloon.TextFrame.Characters.Font.Color = 1

CalloutBalloon.TextFrame.Characters.Font.Size = 14

CalloutBalloon.TextFrame.HorizontalAlignment = xlHAlignCenter

CalloutBalloon.TextFrame.VerticalAlignment = xlVAlignCenter

Thus the code above sets the text in the relevant shape called CalloutBalloon, then changes its font and alignment. You can format only part of the text in a shape, as this example shows:

Here we've forwarded only the characters from 6 to 9.

Here's some code to achieve the above:

Sub AddShapeToCell()

Dim s As Shape

Dim ws As Worksheet

Set ws = ActiveSheet

'add a circle

Set s = ws.Shapes.AddShape(9, 10, 10, 140, 30)

'make it nearly white

s.Fill.ForeColor.RGB = RGB(245, 245, 255)

'show text within it

s.TextFrame.Characters.Text = "Wise Owl training"

s.TextFrame.Characters.Font.ColorIndex = 3

With s.TextFrame.Characters(6, 3)

'colour the owl differently

.Font.Color = RGB(100, 200, 0)

End With

End Sub

This changes the colour of the 3 characters from position 6 onwards.

Note that this is one of those occasions when (to keep you on your toes) VBA numbers things in a collection from 1, not 0.

A complete example - creating our Wise Owl call-out

Here's the example from the beginning of this blog:

As promised, below is the code to create these two shapes!

The code to produce it could be as follows:

Dim w As Worksheet

Sub DeleteShapesOnSheet()

'delete any previous shapes added

Dim w As Worksheet

Dim s As Shape

'refer to a given worksheet

Set w = ActiveSheet

'delete all of the shapes on it

For Each s In w.Shapes

s.Delete

End Sub

Sub CreateWiseOwlBalloon()

'the call-out containing speech text

Dim CalloutBalloon As Shape

'the Wise Owl logo

Dim Logo As Shape

'set reference to a worksheet

Set w = ActiveSheet

'delete any shapes added, to start with blank sheet

DeleteShapesOnSheet

'add the callout balloon

Set CalloutBalloon = w.Shapes.AddShape(108, 50, 10, 150, 120)

'format it to look better: fill and border colours

CalloutBalloon.Fill.ForeColor.RGB = RGB(227, 214, 213)

CalloutBalloon.Line.ForeColor.RGB = RGB(0, 0, 0)

'add and format text in call-out

CalloutBalloon.TextFrame.Characters.Text = "Wise Owl blog on shapes"

CalloutBalloon.TextFrame.Characters.Font.Color = 1

CalloutBalloon.TextFrame.Characters.Font.Size = 14

CalloutBalloon.TextFrame.HorizontalAlignment = xlHAlignCenter

CalloutBalloon.TextFrame.VerticalAlignment = xlVAlignCenter

'now add the owl logo (initially a rectangle)

Set Logo = w.Shapes.AddShape(1, 30, 160, 80, 80)

Logo.Fill.UserPicture "C:\ajb files\wise-owl-logo.jpg"

Logo.Line.Visible = msoFalse

End Sub

Running the CreateWiseOwlBalloon routine should create the two shapes shown (although you'll need to substitute your own picture file path to get it to work).

Time now to look at some specialist shapes: lines and connectors.

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 (this blog) 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 0 threads

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

Formatting shapes and adding text

An example of formatting a shape's fill pattern

An example of formatting a shape's border

Adding text to shapes

A complete example - creating our Wise Owl call-out

Head office

London

Manchester