Complete Excel button design guide to trigger VBA macros

Excel buttons to trigger macros

Introduction

Beginning of visual basic

Visual basic 1.0 was released in 1991. 

Command buttons become popular

People rapidly became familiar with Visual Basic's toolbox and command buttons were usually used to trigger macros.  As this toolbox was a hit, it has changed very little over the last 30 years and looks very similar to what is available in Excel's developer tab today.

Although the old-school command buttons are still the most popular option, they have the following issues:

  • Very limited formatting options available.
  • On certain displays the buttons text changes size when clicked.
  • These buttons move with the cells if their position is not locked. If rows are hidden their height can reduce to 0 and disappear.
  • If you lock the buttons position then they can move away from the relevant part of the spreadsheet.
  • You cannot also add hover comments to these buttons when they are used in spreadsheets.

These problems may seem trivial at first but they grow into larger problems as your spreadsheet grows and more people start to use it.  

Now there are better options available

30 years later, the VBA toolbox hasn’t changed much but fortunately Microsoft have provided more elegant options to trigger macros that are not included in the toolbox.

This article details the benefits of 5 alternative methods and provides step-by-step instructions to quickly set these up.

Future-proof your spreadsheet's user-interface

If your spreadsheet is only for you to use then using these grey command buttons to trigger all your macros maybe be ok.  However spreadsheets often  grow in complexity and users beyond what we could have ever imagined. 

When you start developing a spreadsheet, try to imagine how far it could possibly grow in the future.  If it could grow beyond a simple spreadsheet only used by you, then it is prudent to consider all interface options discussed in this article.  

 

Design Principal - Copy what Microsoft does

Microsoft have taken a huge effort over many years designing the most user-friendly interface for Excel.  To accommodate a growing number of menu options they created the ribbon user-interface in 2007.  Benefits of ribbons include:

  • Icons to identify functions by eye rather than memory.
  • Hovering mouse over button changes its colour and provides helpful comments.
  • Ribbon tabs are broken up into manageable groups of buttons
  • Ribbon tabs can accommodate well over a 1000 clickable buttons.

For example the home tab of Excel probably has over 1000 clickable buttons.  Now imagine how messy it would be if these were all command buttons spread out across a spreadsheet with a description next to the button detailing what it does.

Recommended buttons

 

Definitions:

Custom calculations are :

  • functions you have coded in VBA to perform a specific calculation on a groups of cells selected in the formula bar.  In other words, once you have written your function in VBA, you can use it in Excels formula bar just the same as any Excel function.  The next section provides an example of this.
  • usually created to perform calculations that would be very long and messy using Excels standard functions.   

A localised function has its output cells located next to its input cells.  This means the user can work on localised functions without navigating.  Placing a button to trigger the localised function next to the input cells is usually the most effective option as it is easy to find the button and is obvious what it does.  Note if localised functions can be converted to custom calculations, this is usually a smarter solution as discussed below.

General functions are functions other than custom calculations or localised functions. These functions usually can be applied to any cell(s) in the spreadsheet. For example a function that applies a specific format to selected cell(s).

Option 1: Custom Calculations

Often buttons are added to carry out a very specific calculation / operation that was to difficult to achieve with Excel's formula bar.  So a button is added to trigger VBA code to carry out this operation.

The good news is you can easily create your own VBA functions which can be used in Excel's formula bar.  To do this you simply:

  1. Write a public function in any VBA module and set a range variable as an argument.
  2. Type the name of this function into the Excel formula and it works!

This simple example below shows how a button can be removed with a custom calculation:

Excel Custom Calculation

Option 2: Custom Ribbon 

The Excel ribbon can be thought of as the table of contents of buttons in Excel, its hierarchical order makes it easy to find the button you are looking for.  A ribbon should generally include all available functions, similar to how Microsoft implements their Excel ribbons.  Making a ribbon doesn't need to be scary, with Easy Ribbon Builder it only involves filling out a spreadsheet.

Custom ribbon tabs have the following advantages:

  • Ribbon tab is always available regardless of where you are in the workbook
  • Icons to identify functions by eye rather than memory.
  • Ribbon tab does not consume space on your worksheet
  • When the mouse cursor hovers over the button it changes colour and provides a comment describing its function. This allows first time users to learn its function and advanced users can ignore this.

Create custom Excel ribbons

When to use this?

A ribbon should generally include all available functions, similar to how Microsoft implements their Excel ribbons.  Even if you create other types of buttons to trigger macros, it is ideal to also have a ribbon button to trigger this as well.  Localised functions that can clearly be operated with the buttons on the sheet may not need to be included in the ribbon.  Personally, I still include all localised functions in the ribbon and only enable them when the relevant sheet is activated.

How to create a custom ribbon 

Follow these steps to create your own ribbon:
Step 1. Open Easy Ribbon Builder.  If you don’t have this you can either download the free version othe professional version.

Step 2. (not required for free version). Fill out this sheet to define the structure of your ribbon tab, then click the done button.  You can have up to 10 groups of buttons.  Button types available are split, drop-down and simple buttons.  90% of projects only require these buttons, however if you need more complex types then after you complete step 3 you can edit the workbook's XML file using RibbonX.

Creating VBA ribbon structure

Step 3.  Fill out the sheet for each group of buttons then click the done button. There is an inbuilt icon search tool. 

Step 4. Add code for each of your buttons actions

 Excel VBA ribbon button action code

 

Step 5.  Add code to set rules for dynamic changes to your ribbon.  This example code below greys out groups of buttons depending on what sheet is selected.

 

 Option 3 - Right click menu

Right click menus are often the fastest way to trigger a function on a selected group of cells. It is recommended to include all right click menu options on the ribbon as well so users know these exist and they can use ribbon as well.  Microsoft does this.

When to use this?

Whenever you have a macro to perform a function on a custom range of cells selected by the user.

How to create a custom right click menu?

In this example we add a right click menu option called MyMenu as shown in the picture below.

 

 

Step 1. If you want an icon on your menu option, then find out the faceID number from this site.

Step 2.  Add VBA code to configure your right click menu option.  This is usually performed in the workbook_open or the Workbook_SheetBeforeRightClick events.

--------------

Private Sub Workbook_Open()

    Dim MyMenu As CommandBar

    Set MyMenu = Application.CommandBars("Cell")

    With MyMenu.Controls.Add(Type:=msoControlButton)

        .OnAction = "MyMenu"

        .FaceId = 351

        .Caption = "MyMenu"

    End With

End Sub

---------------------

Step 3.  Add VBA code for its action.

----------------------

Public Sub MyMenu()

    MsgBox "You just clicked my custom right click menu button"

End Sub

-----------------------

To configure the menu option to only be available for certain ranges, check out this link.

 

Option 4 - Shapes

Shapes can be set as buttons and can be extensively customised to look exactly as you like.  Yes they take longer than command buttons to initially set up, but once you have done this once you can reuse them as much as you like.  The only significant downside to this approach is you need to prevent users from moving the shapes, which requires protecting the sheet, which you may not want to do.

 When to use these?

  • Process diagrams - Click the shape to do something. You may need to visually differentiate clickable shapes form non-clickable ones as shown in the next part.
  • To carry out a localised function.  
  • Access files - Shapes / pictures with a file icon and hover comment look cool. 

How to turn shapes into clickable buttons?

Step 1:  Click Insert > shapes. Then select the desired shape to put into Excel. Alternatively click insert > pictures to insert a picture to be used as a clickable button.

Step 2:  Format the shapes appearance. You will need to consider how to make the shape look like a clickable button.  Some ways to achieve this is:

A) Make the shape look like a 3D button.  For more information click here.

B) For next / back buttons, make them grey like this:

   

C) Underline your buttons text. 

Secret tip, if arrows deviate from horizontal, then set its height to 0 under format option.  Similarly set vertical arrows width to 0. 

VBA clickable button shape design

 

Step 3.  Create a public subroutine in any VBA module

Step 4.  Assign the subroutine created to the shape.  Do this by right clicking on the shape > Click 'Assign Macro' > Select the subroutine.

Assign a macro to the button

 

 

Link shapes or pictures to files

Shapes / pictures with a file icon and hover comment look cool. This section has two methods to achieve this.

    Excel icon click to access file

    Method 1.  Add a link to the file

    1. Right click on shape / picture
    2. Select 'link' from drop-down menu
    3. Fill out pop up window as follows

    Adding file link to shape

    Method 2 – Embed file (unfortunately hover comment not possible)

    1. Select 'insert' tab 
    2. Click 'object' button 
    3. Select 'create from file' tab
    4. Complete the pop-up window as follows
    5. Click 'OK'

     

    Option 5 - Command buttons

    Ok I may sound like I hate VBA command buttons as they look unprofessional and the text can change size, but they do have their place.   These buttons are quicker to set up than shapes and users can only move them in the design mode. Users are also 100% aware these are clickable buttons.

    Command buttons should be used in User forms (pop-up windows).  Note that hover comments can be added by editing its control tip property.  For some weird reason this property is not available for command buttons added to a spreadsheet.  For a guide to setting up user forms, click here.

    The following situations, you may wish to use command buttons over shapes. 

    • For a small spreadsheet that only you use.
    • Where it is hard to identify shapes as clickable buttons.
    • Where users want to move some shapes but buttons remain fixed.

    To learn about how to add these buttons to your workbook click on this link.

    Please share

    If you liked this article, please share.  This information is hard to find so please share to help others.