Thinking In Windows

Elements, Collections, Objects, Properties, Methods, and Procedures

Making Your Macro Easier to Use

Once you record a macro, you can attach it to a menu item or a button or other graphic object, making it as accessible and convenient as the built-in menu commands or buttons in Microsoft Excel.  Not only does this speed up your work, it also makes it easier for other users to accomplish the same tasks simply by running your macros.

Adding a Macro to a Toolbar

If you add a macro to a Toolbar, then you can choose it the same way you choose any other menu command in Microsoft Excel.

    To add a macro to the Tools menu

  1. From the Tools menu, choose Customize . . .
  2. In the pop-up window, choose the Commands tab
  3. Choose "Macros" from the Categories list box
  4. Since we haven't created a special button of our own, drag the "custom button" from the Commands list box onto the toolbar of your choice
  5. Right click the button that you just dragged to the toolbar
  6. Click "Assign Macro" on the popup menu
  7. In the Macro Name box, enter the name of the macro that you created for the last workshop (or choose the name of the macro listed, if you are in the Excel file in which you created that macro)
  8. Click OK and then Close

Now, in that process, because we hadn't previously built our own button, we used a default provided by Microsoft and dragged it to the toolbar.  We could also create our own button and place it on the worksheet itself.  Let's try that!

Before you do this procedure you must have the Forms toolbar displayed.  Use the "View . . . Toolbars" menus and choose "Forms" to display this toolbar on your Excel page.

    To create a button on a sheet and assign a macro to it

  1. Click the button icon on the Forms Toolbar (if the "Show tooltips" box was checked in the "Customize . . ." dialogue, "Options" tab, then when your cursor moves over the objects on the Forms Toolbar, a tooltip will show you which icon is the "button" icon).
  2. Now when you move your cursor to the Excel page it has changed into a cross-hair.  Move the cross-hair to the location on the page where you want one corner of the button to begin.
  3. Hold the right mouse button down and drag until the button is the size and shape that you want (don't worry if it's not perfect right now, you'll get another chance to fix it later).  When you release the right mouse button, the Assign Macro dialog box appears.
  4. To assign an existing macro (like the one you've created) to the button, type or select the name of the macro you created in the Macro Name / Reference box, and then choose the OK button.
  5. Well, that button is automatically named "button" but you can change it to something more meaningful by "editing" the text naming the button.  If you haven't already clicked out of the button to your Excel spreadsheet, then the cursor should be in the button text and you can delete and / or modify the text as you see fit.  Otherwise, move your cursor to the button until the cursor changes into a hand, then right click and chose "Edit text" from the pop-up menu.  Once you've made your change, then move the cursor anywhere outside of the button area and click on your speadsheet.  Now, you're all set to use that button to run your macro!

Next week, we'll use our own graphic object as a "button" and assign the macro we created to it.  Finally, we'll sum up the entire process of this workshop.  See you then!

If you have a question, then click here

Return to main menu