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
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!
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!):
'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"
The macro you create must be contained in the same workbook as the shape.
To do this, set the shape's OnAction property:
'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"
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|
25 Aytoun Street