• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15235
  • Last Modified:

Get Command Button name in excel 2010 using VBA

This just has to be a no  brainer, but can't find a solution.

All I want to do is click on a CommandButton in excel and get the name of the command button I just pressed pressed.

In access I would just use something like : Screen.ActiveControl.Name

I have tried things like:

Private Sub ClearDataLocGrp01_Click()
Dim ButtonName
ButtonName = Application.Caller
end sub

But get an error = 2023 error from the Application.Caller line.

Also tried using a shape object, but can't figure it out. It's has to be easy.
0
askolits
Asked:
askolits
  • 5
  • 2
1 Solution
 
Anthony BerenguelCommented:
Are you trying to get the name of the button or the caption/text that the button displays?
0
 
Anthony BerenguelCommented:
This might be too simple but why not just do this
Private Sub ClearDataLocGrp01_Click()
Dim ButtonName
ButtonName = "ClearDataLocGrp01"
msgbox "you just clicked the " & buttonname & " button."
end sub

Open in new window

Of course you would have to set this up for every button as necessary.
0
 
askolitsAuthor Commented:
Button name.  
In this case it would be. "ClearDataLocGrp01".
I will then pass that name to another function to be handled. I have multiple buttons with similar names calling the same function. Each one ends with a different numeric suffix. Will then enumerate through the button name suffixes to perform various actions based on it's suffix.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
askolitsAuthor Commented:
ButtonName = "ClearDataLocGrp01" ?

The idea is to get the name of the button from the calling object, not to put the button's text name string directly into the code.
0
 
MacroShadowCommented:
Why not:

Private Sub cmdYourCommandButton_Click()
    MsgBox "You just clicked " & Me.cmdYourCommandButton
End Sub

Open in new window

0
 
askolitsAuthor Commented:
"me." will refer to the active sheet not the calling object.
0
 
askolitsAuthor Commented:
I figured out the issue.

dim ButtonName
ButtonName = Application.Caller

This does work 'providing' you use the correct control type.
Excel has two choices for the same control.
One is a 'form' command button control, the other is an 'ActiveX' command button  and they act differently.

I used the ActiveX, I should have just used the Form Control.

Two CommanButton ChoicesSo, when I used the Form control, I could assign my generic macro (function) and I could get the calling command button name.

So there you go.
0
 
askolitsAuthor Commented:
Figured it out myself, but may be useful for others.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now