Complete Excel button design guide to trigger VBA macros
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.
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:
- Write a public function in any VBA module and set a range variable as an argument.
- 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:
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.
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
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.
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
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")
.OnAction = "MyMenu"
.FaceId = 351
.Caption = "MyMenu"
Step 3. Add VBA code for its action.
Public Sub MyMenu()
MsgBox "You just clicked my custom right click menu button"
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.
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.
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.
Method 1. Add a link to the file
- Right click on shape / picture
- Select 'link' from drop-down menu
- Fill out pop up window as follows
Method 2 – Embed file (unfortunately hover comment not possible)
- Select 'insert' tab
- Click 'object' button
- Select 'create from file' tab
- Complete the pop-up window as follows
- 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.
If you liked this article, please share. This information is hard to find so please share to help others.