A nice touch to finishing up a new workbook is to add some custom buttons to it.

buttonanim

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.

edittext

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:

crop

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

Some explanation:

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!

Tweaks:

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.

btncheck

Advertisements