A nice touch to finishing up a new workbook is to add some custom buttons to it.
This is a shape turned into a button. Shapes only have a click action macro attached to it so there’s no MouseDown function like it is for ActiveX and userform objects. The closest one can get is to simulate a button click and add a pause to it.
Start by inserting a shape and play around with the formatting until it looks like a decent button.
Format the text in the Home tab.
Decide how the button should look both ordinary and when pressed. Record a macro while doing the changes, then copy the useful parts of the code to a “Button pressed” Sub.
When tweaking the icon make it offset a bit down, and also make the box around it larger so the icon don’t get cropped out:
For icons I recommend this awesome site: http://iconizer.net
Give the shape and icon names so they can easily be called from the method.
Here’s my finished code, placed in Module1:
Option Explicit Sub btnSearch() Call btnAnim(True, "searchButton", "myIcon") ' *** SEARCH CODE HERE *** Call btnAnim(False, "searchButton", "myIcon") End Sub Function btnAnim(pressed As Boolean, btn As String, Optional icon As String) If pressed Then With ActiveSheet.Shapes(btn) .Shadow.Visible = msoFalse .ThreeD.BevelTopType = msoBevelSoftRound .ThreeD.BevelTopInset = 12 .ThreeD.BevelTopDepth = 4 .TextFrame2.MarginTop = 9 End With If icon <> vbNullString Then With ActiveSheet.Shapes.Range(icon) .PictureFormat.Crop.PictureOffsetY = 2 End With End If Application.ScreenUpdating = True Application.Wait Now + TimeSerial(0, 0, 1) Else With ActiveSheet.Shapes(btn) .Shadow.Type = msoShadow21 .ThreeD.BevelTopType = msoBevelCoolSlant .ThreeD.BevelTopInset = 13 .ThreeD.BevelTopDepth = 6 .TextFrame2.MarginTop = 7.0866141732 .TextFrame2.MarginBottom = 7.0866141732 End With If icon <> vbNullString Then With ActiveSheet.Shapes.Range(icon) .PictureFormat.Crop.PictureOffsetY = 0 End With End If Application.ScreenUpdating = True End If End Function
To make it appear like the button is pressed we use Wait.
Application.Wait Now + TimeSerial(0, 0, 1)
Unfortunately Excel can only wait for seconds at a time. If you want to wait less then use the Windows sleep API, or even omit waiting at all. More about Sleep here: http://www.exceltrick.com/formulas_macros/vba-wait-and-sleep-functions/
BtnAnim’s first parameter tells if it’s a click or release, the second parameter is the name of the shape. Not all buttons may have an icon so the last parameter is optional.
The “With” block properties are from the recorded changes I did in the design part.
And that’s it, now go and impress someone!
If no icons are used then the name of the shape can be replaced with “Application.Caller”, which gets the name of the shape that were clicked. And, you can also just animate the button and then run the button’s code. Just remove the If/else lines in btnAnim.
Sub btnSearch() Call btnAnim ' *** SEARCH CODE HERE *** End Sub Function btnAnim() With ActiveSheet.Shapes(Application.Caller) ...
Last tip; if coding buttons seems like too much hustle then just add a transparent icon to an ordinary button and group them. May be a little faster.