Introduction | Example | Tutorial | Applications

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

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit


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!

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.