Complete Excel button design guide to trigger VBA macros
Beginning of visual basic
Visual basic 1.0 was released in 1991. Its toolbox allowed people to develop a user-interface on User Forms.
Command buttons become popular
People rapidly became familiar with this toolbox and command buttons were commonly added to company spreadsheets. As this toolbox was a hit, it has changed very little over the last 30 years and looks very similar to what is available on Excel's developer tab today.
Now there are better options available
30 years later, the VBA toolbox hasn’t changed much and there are much more elegant options for triggering macros. 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.
This article details the pros and cons for 5 different systems to action VBA macros and how you can quickly configure them.
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 used a lot of resource to design 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 :
- Calculations difficult to perform in Excel's formular bar with the standard functions available.
- Actions that evaluate a VBA variable.
A localised function is where the output of the function is in close proximaty to all its inputs.
Note: With localised functions, the user can make changes and see the result without navigating. Using buttons within the spreadsheet for localised functions is usually the most effective option as it is easy to find the button and is obvious what it does.
General functions are functions other than custom calculations or localised functions.
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 that has a range 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 is hovering 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 on the relevant sheet.
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 perform 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 they exist.
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) Include grey next / back buttons
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.