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 seven 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.
|
Perhaps the most remarkable thing that you can do with shapes is get them to run macros:
We'll arrange it so that when you click on the shape, you'll see a message box appear!
Here's the message that clicking on the owl will show:
The message our macro will show.
To make this work you need to create a macro first, then assign it to a shape.
Here's a modest macro which displays the hoot message on screen (and reads it out for good measure, although it sounds a bit strange!):
Sub Hoot()
'make the owl hoot!
MsgBox "Tu-whit, tu-whoo"
'just in case you missed it, say it out loud
Application.Speech.Speak "Tu-whit, tu-whoo"
End Sub
The macro you create must be contained in the same workbook as the shape.
To do this, set the shape's OnAction property:
Sub CreateShapes()
'text box and owl logo, plus connector
Dim Logo As Shape
'set reference to a worksheet
Set w = ActiveSheet
'now add the owl logo (initially a rectangle)
Set Logo = w.Shapes.AddShape(1, 10, 10, 50, 50)
Logo.Fill.UserPicture "C:\ajb files\wise-owl-logo.jpg"
'assign a macro to it
Logo.OnAction = "Hoot"
End Sub
That's all that you need to do! Clicking on the shape will now run the Hoot macro.
As a postscript to this blog, I've included some links to some online exercises, so that you can test your VBA ability!
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.