Quick Jump
Introduction | Example | Tutorial | Applications | Comments

Introduction - Simulate Button Click with Rectangle

Because they are far more customizable, many people like to use rectangles or other shapes as VBA buttons in their spreadsheets. You assign a macro to your rectangle, just like you would a command button. The only problem is, when you click your rectangle, it doesn’t press down like a normal button would. This tutorial shows you how to make these rectangles sort of respond like command buttons when clicked.


Example

Simulate Button Click

Sub SimulateButtonClick()
Dim vTopType As Variant
Dim iTopInset As Integer
Dim iTopDepth As Integer

'Record original button properties
    With ActiveSheet.Shapes(Application.Caller).ThreeD
        vTopType = .BevelTopType
        iTopInset = .BevelTopInset
        iTopDepth = .BevelTopDepth
    End With

'Button Down
    With ActiveSheet.Shapes(Application.Caller).ThreeD
        .BevelTopType = msoBevelSoftRound
        .BevelTopInset = 12
        .BevelTopDepth = 4
    End With
    Application.ScreenUpdating = True

'Button Up - set back to original values
    With ActiveSheet.Shapes(Application.Caller).ThreeD
        .BevelTopType = vTopType
        .BevelTopInset = iTopInset
        .BevelTopDepth = iTopDepth
    End With
    
'---------------
'Your Macro Here
'---------------
End Sub

Tutorial

To test this macro, follow these steps:

  • Insert a shape onto your worksheet and customize it however you like. That’s the beauty of using shapes as buttons - they’re fully customizable. Here’s the rectangle I’ll use as a button:
    Excel AutoShape Rectangle

  • Open your VBA Editor and paste the Button Click example in a new module.
  • Go back to your worksheet, right-click your rectangle and select “Assign Macro.”
    Assign Macro to Excel AutoShape Rectangle

  • Assign the SimulateButtonClick macro to your shape.
    Assign Macro to Excel AutoShape Rectangle

Now, each time you click your rectangle, it’ll briefly look like it’s been clicked. Here’s a GIF to show you what happens:

Simulated Button Click
Depending on your internet connection, you may have to wait several loops for all the frames of the GIF to load.

There you have it! A simple indented flash is all you need to make your rectangular shape behave kind of like a command button when it is clicked. All that’s left to do is replace the 'Your Macro Here comment in the Button Click example with whatever you want your button to do when it’s clicked.

The beauty of this macro is you can assign it to all your shapes! The Application.Caller command feeds the name of the shape into the macro, so you don’t have to change the code for each shape.

Like usual, there’s more than one way to make a button look like it’s been pressed. I chose to use 3D beveling, but you can also use shadows to give a similar illusion. Likewise, if you want your simulation to last longer, you can add the Sleep command before the “Button Up” code. Here’s a slight modification using Sleep to place a 1/4 second pause between the button down and button up commands:

Simulate Button Click with Sleep

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub SimulateButtonClick2()
Dim vTopType As Variant
Dim iTopInset As Integer
Dim iTopDepth As Integer

'Record original button properties
    With ActiveSheet.Shapes(Application.Caller).ThreeD
        vTopType = .BevelTopType
        iTopInset = .BevelTopInset
        iTopDepth = .BevelTopDepth
    End With

'Button Down
    With ActiveSheet.Shapes(Application.Caller).ThreeD
        .BevelTopType = msoBevelSoftRound
        .BevelTopInset = 12
        .BevelTopDepth = 4
    End With
    Application.ScreenUpdating = True

'Pause while Button is Down
    Sleep 250
    Application.ScreenUpdating = True
    
'Button Up - set back to original values
    With ActiveSheet.Shapes(Application.Caller).ThreeD
        .BevelTopType = vTopType
        .BevelTopInset = iTopInset
        .BevelTopDepth = iTopDepth
    End With
    
'---------------
'Your Macro Here
'---------------
End Sub

Application Ideas

The reason I make my shape buttons behave like this purely for user experience. I have found that users want some sort of positive confirmation that they actually clicked a button. Provide a little on-screen click simulation is one way to do that. Anywhere you have an AutoShape that you want to behave like a button, you can use this macro. It’s the little things that make for a great user experience!


Comments

Share this tutorial with the people you know and let me know how you plan on using the macro by leaving a comment below! Follow me on Google+ for more great VBA content.


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.