Adding A Control To Activate Your Macro

For this part of the tutorial you will need to refer to the workbook created in parts 1 and 2 of this series.

Now that you have created your workbook with a list of names and ages along with some macros we will now make the execution of those macros easier.

Whilst it’s not a particularly difficult task for the seasoned Excel user to execute a macro by going to ‘Tools’, ‘Macros’, and then selecting the appropriate macro, users new to Excel may find all that a little daunting, so in this short tutorial we will show you how to add a command button, similar to a standard Windows button, to your sheet which will execute your macro.

As we have done previously in this series, the example will only demonstrate adding one button, for one macro, to the Workbook, adding the other two as necessary is an easy task of just repeating the tutorial, and making obvious changes.

Firstly load up the previously created Workbook from part 2 of the tutorial.

Next you will need to have access to the ‘Forms Toolbar’, which can be found under ‘View’, ‘Toolbars’, ‘Forms’ and should look something similar to the one below. The actual appearance may vary depending on how your system is set up, and may appear as menu toolbar rather the floating style toolbar shown. No matter how your toolbar is displayed, they all have the same buttons.

Select the button icon,  and the cursor will now change to a cross hair. Decide where you want to put the top left corner of your button, and click and hold to place the corner, then drag to draw your button, Don’t worry if your size and positioning isn’t exactly right as you can change it all later.

Once you release your mouse button you are presented with a dialog asking you which macro you want to assign to this button, select the macro to Sort By Christian name and click OK.

Once your button is on your form click on any blank part to remove the editing border, and your button should become active.

Congratulations, you now have a command button that will run your macro when clicked. Try clicking it to sort your list of names.


Formatting the appearance of your button

You will notice that your nice new button has been placed on your form with a fairly non-descript title something like Button 1 or Button2 most likely, which is obviously not very descriptive for your users.


Fortunately the majority of the button properties are easy to edit and set and can be accessed by right clicking on the button itself.

The first thing to do is change the title for the button to actually describe its function. So after right clicking, select the option to ‘Edit Text’, you can directly edit the button text to something more suitable such as ‘Sort By Christian Name’

The remainder of the button properties can be accessed by right clicking and selecting ‘Format Control’. Here you will find a whole host of options to change things such as button size, text size and alignment, printing, locking and many other features. Use this option to make your button look exactly how you want.

Note – Be aware that when you right click the button click on the shaded border, NOT inside the button itself otherwise your formatting may not be applied correctly.

Having created one button you can now repeat the process to create two more buttons for sorting by Surname and Age.