© Roy Cox (2005 - 2020)

Related articles Option Explicit Where to put your code How to run your macro
How to run your Macro

Page Title

Making Excel work for you.
Adding a button to your worksheets is the simplest way for users to run your macros. You have a few options: You can add a Forms Control Button Alternatively us an ActiveX Button Use a Shape and assign the macro add a custom button to the Ribbon or QAT

How to Show the Developer tab in your Office application

For the first two options, you need to ensure that the Developer Tab is available by following these simple steps: 1 . Right click anywhere in the Ribbon 2 . Click Customize Ribbon to display the Excel Options Dialog. 3 . Select Popular Commands in the Drop Down 4 . To the right of the Dialog you will see Customize the Ribbon 5 . Under the Main Tab find and select the Developer check box. 6 . Click OK and you will see the Developer Tab added to the Ribbon. I would suggest that you use Forms controls because they are simpler to work with. ActiveX controls allow for more flexible design and should be used when you need more control than a basic Forms control can give. In addition, many computers by default won’t trust ActiveX and it will be disabled, this sometimes needs to be manually added to the Trust Centre. ActiveX is a Microsoft based technology and, as far as I’m aware, is not supported on the Mac. This is something you’ll have to also consider should you, or anyone you provide a workbook to, decide to use it on a Mac. There is one major difference that is important to know. ActiveX controls show up as objects that you can use in your code, try inserting an ActiveX control into a worksheet, bring up the VBA editor (ALT + F11) and you will be able to access the control programmatically. You can’t do this with form controls ,macros must instead be explicitly assigned to each control, but form controls are a little easier to use. If you are just doing something simple, it doesn’t matter which you use but for more advanced use ActiveX has more possibilities. ActiveX is also more customisable, e.g. you can change the background colour of an ActiveX CommandButton but not a Forms Button. To add an ActiveX CommandButton or Forms Button to your worksheet, on the Developer tab, click Insert. You will see the Insert Dialog, from this you can select the control that you want to useto use.

ActiveX CommandButton

1 . In the ActiveX Controls group, click Command Button. 2 . Drag a CommandButton onto your worksheet. Whilst doing this you can use the drag handles to resize the control. 3 . Right click CommandButton1, this is the default name assigned to the control, make sure Design Mode is selected in the Developer Tab You need to click Design Mode whenever you want to move an ActiveX control, edit the caption, change the control’s colour, etc. 4 . Click View Code, the Visual Basic Editor appears. 5 . Place your cursor between Private Sub CommandButton1_Click() and End Sub. 6 . Add your code. You can simply type or paste your code. I would suggest actually writing your code in a Standard Module , then simply type “Call” and your macro’s name or just enter your macro’s name. In this way you can use the same macro for multiple buttons if required. 7 . Close the Visual Basic and make sure Design Mode is deselected.

Forms Button

You can add a Forms control button in much the same way as an ActiveX one. 1 . On the Developer tab, in the Controls group first click Insert, and then under Form Controls click the Button icon in Forms Controls group 2 . Click the worksheet location where you want the upper-left corner of the control to appear. Use the drag handles to draw your control. You can change the size and position later. 3 . The Assign Macro dialog box will immediately appear, giving you the opportunity to assign a macro to the button. You will see that the dialog box shows a list of previously created 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). 4 . 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 caption appearing on the button by clicking your mouse within the button text and entering a new caption. 5 . You have now created a button for your macro which will be run whenever anyone clicks on it with the left mouse button. 6 . 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. 7 . To specify the control properties of the button, right-click the button, and then click Format Control. A menu that allows you to delete the button or change the macro assigned to the button will appear. You can also change various Properties of the button here- Size, Text, Font, etc. The Properties tab is very useful because you can choose whether the button will print or not and adjust the positioning of the control.

Use a Shape as a Button

In much the same way as a Forms Control you can assign a macro to a shape or any other object such as an image. To start go to Ribbon -> Insert -> Shapes and select your choice of the many shapes available. I like the rounded rectangle, but choose any that you like. Now right click on the shape and then from the menu you can choose what you want to do like adding suitable text to the shape. Most importantly for this tutorial if you look further down the menu you will see Assign macro. Click and the Assign Macro Dialog Box is displayed so you can choose the macro to run as you did with the Forms Button before.

Ribbon or QAT(Quick Access Toolbar)

You can assign a macro to a custom tab on the Ribbon or a custom button on the Quick Access Toolbar(QAT) in Excel and then run it by clicking that custom button. The Quick Access Toolbar is a customisable toolbar that contains a set of commands that are independent of the tab on the Ribbon that is currently displayed, use this to add frequently used commands. You can move the Quick Access Toolbar from one of the two possible locations, and you can add buttons that represent commands to the Quick Access Toolbar. To assign a button the QAT to run your macro follow these steps: 1 . Click the down arrow button on the right side of the Quick Access Toolbar to display the Excel Options Dialog Box with the Customize the Quick Access Toolbar selected. 2 . Select More Commands on the drop-down menu. 3 . Choose Macros from the Choose commands from drop-down list. 4 . Select the macro you want from the list displayed below the Choose commands from drop- down list and then click Add. 5 . The macro is added to the Customize Quick Access Toolbar list 6 . Click OK to accept the changes and close the Excel Options dialog box You now have a button on the QAT that will run your macro.

Create Custom Ribbon Tab

If you want to assign a macro to a custom group on a custom Ribbon tab, try this: Right click anywhere in the Ribbon 1 . Click Customize Ribbon to display the Excel Options Dialog or press Alt+FTC. Excel displays the Customize Ribbon pane in the Excel Options dialog box. 2 . Click Macros in the Choose Commands From drop-down list box on the left. 3 . Excel lists the names of all the macros created in the current workbook in the Choose Commands From list box. 4 . Click the name of the custom group on the custom tab to which you want to add the macro in the Main Tabs list box on the right. 5 . If you haven’t already created a custom tab and group for the macro or need to create a new one, follow these steps: 6 . Click the New Tab button at the bottom of the Main Tabs list. 7 . Excel adds both a New Tab (Custom) and New Group (Custom) item to the Main Tabs list while at the same time selecting the New Group (Custom) item. 8 . Click the New Tab (Custom) item you just added to the Main Tabs. 9 . Click the Rename button at the bottom of the Main Tabs list box and then type a display name for the new custom tab before you click OK. 1 0 . Click the New Group (Custom) item right below the custom tab you just renamed. 1 1 . Click the Rename button and then type a display name for the new custom group before you click OK. 1 2 . In the Choose Commands From list box on the left, click the name of the macro you want to add to the custom group now selected in the Main Tabs list box on the right. 1 3 . Click the Add button to add the selected macro to the selected custom group on your custom tab and then click the OK button to close the Excel Options dialog box. After you add a macro to the custom group of a custom tab, the name of the macro appears on a button sporting a generic icon (a programming diagram chart) on the custom tab of the Ribbon. Now to run the macro you simply click this button.


Personally, I would not use either of these last two methods. They will only run the macro if the workbook containing that macro is open. If it is closed it will open the workbook to run it which is not what you want. You could avoid this by saving the macro workbook as an Excel add-in. It is
If you find this article useful You can help maintain my site by donating. Simply click the image below
Buy me a coffeeBuy me a coffee