Executing a procedure from a toolbar button

You can customize Excel's toolbars to include buttons that execute procedures when clicked. The procedure for assigning a macro to a toolbar button is virtually identical to the procedure for assigning a macro to a menu item.

Why Call Other Procedures?

If you're new to programming, you may wonder why anyone would ever want to call a procedure from another procedure. You may ask, "Why not just put the code from the called procedure into the calling procedure and keep things simple?"

One reason is to clarify your code. The simpler your code, the easier it is to maintain and modify. Smaller routines are easier to decipher and then debug. Examine the accompanying procedure, which does nothing but call other procedures. This procedure is very easy to follow.

Sub Main()

Call GetUserOptions Call ProcessData Call CleanUp Call CloseltDown

End Sub

Calling other procedures also eliminates redundancy. Suppose that you need to perform an operation at ten different places in your routine. Rather than enter the code ten times, you can write a procedure to perform the operation and then simply call the procedure ten times.

Also, you may have a series of general-purpose procedures that you use frequently. If you store these in a separate module, you can import the module to your current project and then call these procedures as needed — which is much easier than copying and pasting the code into your new procedures.

Creating several small procedures rather than a single large one is often considered good programming practice. A modular approach not only makes your job easier but also makes life easier for the people who wind up working with your code.

Assume that you want to assign a procedure to a toolbar button on a toolbar. Here are the steps required to do so:

1. Choose the View ^ Toolbars ^ Customize command. Excel displays the Customize dialog box.

When the Customize dialog box is displayed, Excel is in a special "customization" mode. The menus and toolbars are not active, but they can be customized.

2. Click the Commands tab in the Customize dialog box.

3. Scroll down and click Macros in the Categories list.

4. In the Commands list, drag the second item (Custom Button) to the desired toolbar.

5. Right-click the new button to display a shortcut menu.

6. Enter a new name for the button in the Name text box. This is the ToolTip text that appears when the mouse pointer moves over the button. This step is optional; if you omit it, the ToolTip displays Custom.

7. Right-click the new button and select Assign Macro from the shortcut menu.

Excel displays its Assign Macro dialog box.

8. Select the procedure from the list of macros.

9. Click OK to close the Assign Macro dialog box. 10. Click Close to close the Customize dialog box.

After you follow the process above, the new toolbar button always appears on the assigned toolbar—even when the workbook that contains the macro is not open. In other words, changes that you make when choosing the View^Toolbars^Customize command are permanent. Clicking the new toolbar button item opens the workbook if it's not already open.

I cover custom toolbars in Chapter 22.

I cover custom toolbars in Chapter 22.

0 0

Post a comment