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 five 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.
|
This blog doesn't claim to be exhaustive - there are a LOT of things you can do with shapes:
Just some of the things you could experiment with!
Here we'll just look at two additional types of shape: lines and connectors.
To add a line, specify its start and end point (logical, really):
The arguments when adding a line.
For example, suppose that you wanted to add this red line:
This line starts at (30,10) and ends at (100,50).
Here's the code to add the line above:
Sub AddRedLine()
Dim ws As Worksheet
Dim s As Shape
Set ws = ActiveSheet
Set s = ws.Shapes.AddLine(30, 10, 100, 50)
s.Line.ForeColor.RGB = RGB(255, 0, 0)
End Sub
When adding connectors, it helps to know that there are 3 types:
As you move each owl around, the connector will stay ... connected to it!
To add a connector:
To draw the connector on the left above (the curved one), first create the two shapes (the textbox and the owl logo):
Sub CreateShapes()
'text box and owl logo, plus connector
Dim rect As Shape
Dim Logo As Shape
Dim conn As Shape
'set reference to a worksheet
Set w = ActiveSheet
'add the text box
Set rect = w.Shapes.AddShape(1, 10, 10, 80, 20)
rect.TextFrame.Characters.Text = "Curve"
rect.Fill.ForeColor.RGB = RGB(227, 214, 213)
rect.TextFrame.Characters.Font.ColorIndex = 1
'now add the owl logo (initially a rectangle)
Set Logo = w.Shapes.AddShape(1, 30, 80, 50, 50)
Logo.Fill.UserPicture "C:\ajb files\wise-owl-logo.jpg"
You'll need to use a different picture, of course, if you want to reproduce this. Next, add the connector:
'add the connector linking together (doesn't matter where)
Set conn = w.Shapes.AddConnector(msoConnectorCurve, 1, 1, 1, 1)
Note that it's not worth thinking about its position or size, as when you reroute it VBA will redraw it. Now say what the connector is connecting!
conn.ConnectorFormat.BeginConnect rect, 1
conn.ConnectorFormat.EndConnect Logo, 1
The second argument above specifies whether you're connecting the top, left, right or bottom of the given shape, but it doesn't seem to make much difference what value you use.
Finally, you should update the position of the connector:
'redraw the connector
conn.RerouteConnections
End Sub
The cool thing now is that as you drag either shape around the connector will follow it!
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
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.