Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 84 Connect Buttons to Macros Easily

Instead of giving every button its own macro,
it's sometimes more convenient to create a single
macro that manages all the buttons.

Users
generally prefer to run macros via either a shortcut key or a button
they can simply click, instead of having to hunt through menus and
dialog boxes. The most popular way to access a button is from the
Forms toolbar, available by selecting View Toolbars
Forms. These buttons, in our opinion, are the best choice
for running macros, especially recorded macros, because recorded
macros often require the user to be on a specific worksheet when the
macro is run. Simply put, recorded macros always use ActiveSheet if
you recorded the macro without changing sheets. This means that if
the user is not on the required worksheet (in other words, the same
one you were on when recording), the recorded macro will often
"bug out" and/or make changes on
the wrong sheet. By using a button on a worksheet, you can force the
user to navigate to that worksheet button to set the right conditions
for the macro before clicking it.

Why a button from the Forms toolbar and not the Control Toolbox toolbar? Buttons are almost always used to detect a mouse click and then run a specified macro. You should use a command button from the Control Toolbox toolbar only when you need to determine other events such as a double-click, a right-click, and so on. The controls on the Control Toolbox toolbar are known as ActiveX controls, and using them to only run a macro adds unnecessary overhead to Excel, especially if you use a lot of buttons. It is akin to using a sledgehammer to bang in a nail.

When
you have a lot of buttons in a workbook and each button is used to
run a specified macro, you can attach the macros to the buttons by
right-clicking the button border and choosing Assign Macro. Then find
the correct macro in the Assign Macro dialog, as shown in Figure 7-2.

Figure 7-2. Print button highlighted and Assign Macro dialog active, with macro highlighted

Because each button is usually used to run a different macro, often
you must scroll through the entire macro list to find the correct
one. There is a really simple way you can assign all the buttons to
the same macro but still have each button run a different macro.

Place the following code into any
standard module. Select Tools Macro Visual
Basic Editor (Alt/Option-F11) and create a new module by selecting
Insert Module and then entering the following code:

Sub WhichButton( )
Run Application.Caller
End Sub

Now
you need to give each button the same name as the macro it should
run. To name a button from the Forms toolbar, simply left-click it,
then replace the name shown in the Name box (at the left of the
Formula bar) with the name of the macro the button should run. Do the
same for all buttons. Now, if you named a button Macro1 and then
assigned it to the macro WhichButton, when clicked
it will run the macro Macro1.