Solved

Get Command Button name in excel 2010  using VBA

Posted on 2014-01-16
8
10,795 Views
Last Modified: 2014-01-21
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
Comment
Question by:askolits
  • 5
  • 2
8 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
Comment Utility
Are you trying to get the name of the button or the caption/text that the button displays?
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
Comment Utility
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
 

Author Comment

by:askolits
Comment Utility
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
 

Author Comment

by:askolits
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Why not:

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

Open in new window

0
 

Author Comment

by:askolits
Comment Utility
"me." will refer to the active sheet not the calling object.
0
 

Accepted Solution

by:
askolits earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:askolits
Comment Utility
Figured it out myself, but may be useful for others.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now