Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 14496
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

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