MAKING EXCEL WORK FOR YOU

Heading

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


Add a CommandButton to run your VBA code


The following instructions apply to Excel 2007 onwards, first you need to make sure that the Developer Tab is available

If you are using Microsoft Excel 2007 or later you need to make sure that the Developer Tab is available. There is no Forms toolbar, instead, you must use the Developer tab of the ribbon and then click Insert in the Controls group. The resulting palette shows a number of different controls. In earlier versions you need to have the Forms Toolbar visible. To do this from the View menu click ToolBars and select Forms

The next requirement is the same for all versions of MS Excel

You should click the Button icon, and then follow the four steps listed below.

1. Click on the Button tool.

2. Click in your workbook where you want a corner of the button to appear, but don't release the mouse button.

3. Move the mouse to specify the size of the button you want.

4. Release the mouse button.

Excel immediately displays the Assign Macro dialog box, offering you the opportunity to assign a macro to the button. You will see that the dialog box shows a list of previously defined macros, along with a suggested name for the macro to be assigned to this button. The suggested name is comprised of the default name of the button itself (something like Button1) combined with the action that will start the macro (Click). This macro name (Button1_Click) will appear very familiar to people that have programmed in Visual Basic before, since it conforms to the standard way of naming event handlers. Event handlers are nothing but programming code designed to handle a specific event, such as an object like a button being clicked with the mouse.

To finish with the Assign Macro dialog box, select a macro you want assigned to this new button and then click on OK. You can then change the title appearing on the button by clicking your mouse within the button text and entering a new title.

Once the button is finished in this manner, the macro associated with this button will be run whenever anyone clicks on it with the left mouse button. If you use the right mouse button instead, you will see a menu that allows you to delete the button or change the macro assigned to the button.

ActiveX controls can be added from the Controls ToolBox or the Excel 2007 Developer tab as above. These are more complex than Forms Controls and have more properties and can contain code.