Excel VBA Macro: Determine Which Button, Control or Command Button Was Clicked

Got any Excel/VBA Questions? Free Excel Help

Download Workbook Example of Which Button Was Clicked

Whenever we create or record a Macro we should then allow the user to run the macro either via a shortcut key and/or a button they can simply click. The most popular way of using a button is to use a Button from the Forms toolbar, View>Toolbars>Forms. These, in my opinion, are the best choice for running a macro, especially recorded macros. Why? Because recorded macros often require the user to be on a specific Worksheet when the macro is run. This is simply because recorded macros always use ActiveSheet if you have recorded the macro without changing sheets. This means that if the user is not on the required Worksheet (same one you were on when recoding) the recorded macro will often 'bug-out' and/or make changes on the wrong sheet. By using a button on a Worksheet we can force the user to navigate to that Worksheet button before clicking it.

Why a Button from the forms toolbar and not the control toolbox toolbar? The answer is really quite simply, buttons are almost always used to detect a mouse click and then run a specified macro. We should only use a Command Button from the Control Toolbox Toolbar when we need to determine other events such as, double click, right click, a specific keyboard button is pressed and many others. The controls are known as ActiveX controls and to use them to only run a macro is adding some unnecessary overhead to Excel, especially if we use a lot of buttons. It can be likened to using a sledge hammer to bang in a nail.

When we do have a lot of buttons in a Workbook and each button is used to run a specified macro. Normally this is done by right clicking on the button, choosing "Assign macro" then finding the correct macro in the Assign Macro dialog.

As each button is normally used to run a different macro, it often means scrolling through the list of all macros to find the correct one. Wouldn't it be nice if we could assign all our buttons to the same macro yet still have each button run a different macro? Sounds impossible doesn't it? Well it's not and is a great way to organise macros and buttons. All we need to do is place the code shown below into any standard module. That is, go to Tools>Macro>Visual Basic Editor (Alt+F11)

Sub WhichButton()

Run Application.Caller

End Sub

Then name each button the exact same name as the macro they should run. To name a button from the Forms toolbar we left click on it, then replace the name shown in the Name Box (left of the Formula Bar) with the name of the macro the button should run! Do the same for all buttons all button to the macro WhichButton. For example, if we named a button Macro1 then assigned it to the macro WhichButton, when clicked it would run the macro Macro1.