July 3, 2011, 5:23 a.m.
posted by donnie
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.
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.
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.
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.