BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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
- Adding lines and connectors to join shapes together (this blog)
- 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
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.
Adding lines and connectors to join shapes together
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.
Adding lines
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
Types of connector
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!
Adding a connector
To add a connector:
- Add a connector shape, as you would do a line.
- Tell it which shape to begin at.
- Tell it which shape to end at.
- Finally, reroute the connections (this basically makes sure that the connector is where it should be on screen, given the current position of the two shapes it's linking together).
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!
- Working with shapes in VBA
- Working with shapes - getting started
- Naming, referring to and positioning shapes
- Formatting shapes and adding text
- Adding lines and connectors to join shapes together (this blog)
- Working with multiple shapes
- Assigning macros to shapes
- Exercises on shapes