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!

  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 (this blog)
  5. Adding lines and connectors to join shapes together
  6. Working with multiple shapes
  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.

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:

Shape ForeColor property

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

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:

A dashed border

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:

Formatted second word

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:

Example of call-out balloon

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

Next s

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.

This blog has 0 threads Add post