power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Launch a VBA function or macro from within a worksheet (2 of 3):
Create a shortcut button on your toolbars or menu

ShareThis

Creating a macro shortcut button is faster way to execute macros from within Excel.  Excel 2003 and earlier allow you to place shortcut buttons directly on your built-in toolbars, or to create a custom toolbar.  Excel 2007 has a Quick Access Toolbar across the top of the program window where you can create custom macro buttons.

In Excel 2007, the shortcut bar is either across the top border of the program window or under the Ribbon, depending on which option you have selected in Excel Options.  Follow these steps to add a shortcut button which will execute your VBA Monte Carlo function.

First you will need to create a subroutine which calls the function, since the set-up dialogue recognizes only subroutines as macros.  In the example below, CoinToss() is the Monte Carlo function.  FritzMacroButton() is the subroutine which calls the function.




Now that the subroutine has been created, we'll assign it as a macro to a button on the shortcut bar.  Click the button at the right end of the shortcut bar to reveal the drop-down menu.  Select "More Commands...".

This opens the Customize section of the Excel Options dialogue.  Select Macros in the "Choose commans from:" list, and your current workbook in the "Customize Quick Access Toolbar:" list (unless you want to be able to run the macro from within any workbook).  Then, find the desired macro in the list box on the left, and add it to the Quick Access Toolbar by clicking the "Add" button.  Click "OK" and the shortcut button should appear on your Quick Access toolbar.

Unlike earlier versions of Excel which allow customization of menus, Excel 2007 does not allow customization of the Ribbon interface.  As this page is being written shortly before the release of Excel 2010, it is reported that the new version of Excel will allow customization of the Ribbon menus.  It may be possible therefore in Excel 2010 and later to add custom macro menu items to the Ribbon.

In Excel 2003 and earlier, add a macro button by first selecting "Tools - Customize" from the drop-down main menu bar.  This opens the "Customize" dialogue box shown below.  Select the Commands tab, then scroll down in the Categories list until you see Macros.  Click on Macros.  You can then drag the Custom Button item from the Commands list onto any open toolbar. 

With the Customize dialogue box still open, right click on the custom button you've placed on the toolbar.  This opens a drop-down menu where you can assign a macro and perform other functions to customize the button.

Similarly in Excel 2003 and earlier, you can drag the Custom Menu Item from the Commands list in the Customize dialogue box and drop it anywhere within your menu structure.  Right-clicking on the menu item while the Customize dialogue box is still open gives you options for assigning a macro to the item and customizing it in other ways.

 

 

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.