Assign a macro to a button in Excel so then when your user clicks on the button the macro automatically runs. It only takes a few minutes and this guide will teach you how..
A Macro is a series of commands either recorded, or manually written using the VBA Editor in Excel.
Macros are common place in advanced Excel reports and dashboards – from complicated macro’s that create and burst multiple reports to ones that might only contain a single command, however the one thing they all have in common is that they scare non-technical Excel users!
The world of VBA scripts and seemingly long pieces of code is a foreign one to the non-technical Excel user and if you have ever created Excel reports that are maintained by non-technical users you will know that trying to explain how to run Macro’s is not always very straight-forward, not because it is a particularly complicated procedure, but because non-technical users generally have no interest in learning.
Make it easy for the non-technical users
A lazy Excel analyst might turn around and say “well I’ve explained how to do it so that’s up to them if they don’t want to learn” but we don’t have any lazy Excel analysts on this website, the very fact that you are here developing your skills tells me that.
So what does the dedicated Excel analyst do? That’s right, we make life simple for the non-technical folk out there are we avoid the use of VBA editors or advising on how to run scripts, we stick it all on a button in the Excel file and simply tell them to click the button…much easier for the non-technical folk out there.
First we need a button
Before we can assign a macro to a button the first step is to create a button in the Excel file that the user can click. Where you want to place this button depends on your report.
Sometimes I will have a separate worksheet which just contains the report title, perhaps a few guidelines and then a big “update” button, or other times if the macro I need to user to run is updating a specific chart then I will place it right next to it – common sense is the order of the day here.
So decide on your location and to insert a button you need to navigate to the “DEVELOPER” tab on the ribbon, followed by selecting “INSERT” and then select the button icon, as the below image shows:
When you have selected the button icon you will notice that the mouse pointer (usually an arrow shape) has now become a cross shape, all you do is navigate to where you want to draw the button, left-click and hold down while you drag the pointer to create a button of your desired size and shape:
Now as soon you release the left mouse button a new box will automatically open prompting you to assign a macro to the button, very handy!
This will contain all your saved Macro’s that are in the workbook – all you need to do is select the one that is to be assigned to the button then click on OK.
As you will then see on your worksheet, you will have a button with a macro assigned to it:
The labeling as default will be “Button 1” and we know that’s going to be totally useless to our non-technical users so right-click on the button and select “Edit Text” to change the label to something more appropriate.
You can also change the format of the text by right-clicking the button and selecting “Format Control”, this will lead you to the various options that you can alter.
That’s all there is to assigning a macro to a button in Excel – so why not start implementing into your reports today. Not only will you save time explaining Excel procedures to the non-technical people, who generally are not that interested, but you will also help them out and strengthen your reputation as one of the experienced users of Excel in your business.