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 with custom code.
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 to looked pressed down, make it offset and also make the box around it larger so the icon don’t get cropped out, like this:
For icons I recommend this awesome site: http://iconizer.net
[UPDATE] In the new 2016 version there’s built in icons: Insert->icons.
Give the shape and icon names, e.g. “searchButton” and “myIcon”.
Here’s the 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!
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.
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. Set properties for button and shape: “Move but don’t size with cells”.