Excel - beginner here. Simple question ...

[doasidont]

Have a number of buttons on my worksheet. How can I determine and display the name of the button clicked? Thanks.

[Br1an_g]

Private Sub CommandButton1_Click()
 
Dim MyButtonName As String
 
MyButtonName = "Button"
 
MsgBox "Button Name: " & MyButtonName, vbOKOnly + vbInformation, "Button Name"
 
End Sub

HTH

[doasidont]

Thanks, but U don't think that helps me. There are several buttons on the spreadsheet. All execute the same code. I need to determine which button was clicked to do the executing. How do I determine the name of the button that was clicked? Thanks.

[VBAhack]

I think you need to clarify something. If the buttons were created by using the forms toolbar - button, then each button can have a macro assigned to run when the button is clicked (performing a right click on the button and pick Assign Macro to see which macro executes. Also, when you right click a button, the name of the button shows up on the spreadsheet near the upper left corner). If you just want to know which button is clicked (programatically), don't know, maybe there is a button click event or something you could tap into.

If you are talking about a VBA form (that you define in the VBA IDE), that's another story. In that case, each button has its own button click code and I'm pretty sure there's a me.name (or something) property associated with it.

Bottom line - which do you have? VBA form or toolbar - form - button?

VBAhack

[doasidont]

I've tried using the Forms Toolbar and the Control Toolbox. My problem is I don't know how to identify the control (button) that was clicked from within the code that it executes. All of the buttons will execute the same code but the parameter passed to the code will change depending on which button was clicked. Thanks.

[VBAhack]

OK. So, I think your situation is the following. Each button has a unique macro that executes when the button is clicked. In other words, 5 buttons, 5 separate macros. Each of the 5 macros, calls the exact same routine but passes a different parameter. If this is the case, here's a possibility. Since you can (by right clicking the button) you can find out the button name and the macro it executes, you can put the following in each macro (amount of work depends on how many buttons you have):

Msgbox "You clicked xxxx button" 'fill in xxxx with the button name
Application.Statusbar = "You clicked xxx button" 'doesn't stop processing

This essentially hard codes the button name into each macro, which can be some work if you have a lot of buttons. If you really want to do it programatically, don't know, maybe RobDog can help.

VBAhack

[doasidont]

That would work but I don't want the user to have to enter the button name, or the parameter(s). To make my question simple: What instruction(s) can I put in a macro that is assigned to a button so that the name of that button is displayed when that button is clicked?

[Br1an_g]

you would need to assign a variable from each button.

e.g.

'global variable
Public Button_CLicked as String
 
sub Button1_Click()
 
'assign variable
buttonClicked = "Button1"
 
'call function
Display_Button_Name
 
End Sub
 
sub Button2_Click()
'assign variable
buttonClicked = "Button2"
 
'call function
Display_Button_Name
 
End Sub

[doasidont]

Thanks. I see how that could work and I'll use that. I just thought there would be a way to determine which 'shape' was active or selected (i.e., which one was clicked) and then use the 'shape' properties to get the name of the button. I know you can loop through all of the shapes on the sheet and get the name of each one, but how do you know which one has been clicked.

[doasidont]

I'll go with storing the button name in common storage.

[VBAhack]

I think you are out of luck unless you use Userforms, which probably means a re-architecture on your part. Your only other hope is to somehow capture the button click event - probably rather involved, if even possible.

VBAhack

[VBAhack]

Wait, I think I found the answer you are looking for. Add the following line in the macro that is executed by each button:

Msgbox Application.Caller

This will display the name of the button that is clicked.

VBAhack

[Br1an_g]

nice 1 VBAHack

[doasidont]

Now that's what I'm talkin' 'bout! Application.Caller works. Thanks.