Get Command Button name in excel 2010  using VBA

Posted on 2014-01-16
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.
Question by:askolits
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
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?
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.

Author Comment

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.
Technology Partners: 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!


Author Comment

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.
LVL 27

Expert Comment

ID: 39786015
Why not:

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

Open in new window


Author Comment

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

Accepted Solution

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.

Author Closing Comment

ID: 39796421
Figured it out myself, but may be useful for others.

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 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