Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get Command Button name in excel 2010  using VBA

Posted on 2014-01-16
8
Medium Priority
?
13,644 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39785977
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
ID: 39785994
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
ID: 39786002
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:askolits
ID: 39786012
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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39786015
Why not:

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

Open in new window

0
 

Author Comment

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

Accepted Solution

by:
askolits earned 0 total points
ID: 39786148
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
ID: 39796421
Figured it out myself, but may be useful for others.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

609 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