Active Control Name 1

[riskassure]

Hi,

Does anyone know the VBA code for the following question:

I have four buttons A, B, C, and D on an Excel spreadsheet. Each button is associated with a macro. But instead of four individual macros, I want to associate them with the same macro. When the button is clicked, the macro will run and a message box will display the name of the button. How do I do this? I tried using the code

Msgbox ActiveControl.Name

But it gave me an error message Run-time error '404': Object required.

Any help will be really appreciated!!

~~CW~~

 

[Loomah]

hi
one possible way of doing this is to create your buttons and name them using some (accepted) convention. please make sure you use active x controls and not buttons from the forms toolbox.

add a single line of code to each button's click event to call your single reusable piece of code and pass the command btton to this code by adding a parameter to your called code.

i've only created 3 buttons but the code is shown below for each

Private Sub cmdONE_Click()
Call DisplayName(cmdONE)
End Sub

Private Sub cmdTHREE_Click()
Call DisplayName(cmdTHREE)
End Sub

Private Sub cmdTWO_Click()
Call DisplayName(cmdTWO)
End Sub

in a different code module add the displayname routine below

Sub DisplayName(CmdButt As CommandButton)
MsgBox CmdButt.Name
End Sub

basically all you are doing is passing the information of the button you clicked to another process that can then do whatever 'stuff' you want it to dependant on the button you used.

not sure i see the point but i'm nopt privvy to your reasons for asking the question!!

enjoy!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

You can directly use one procedure in case of buttons from the Forms toolbox. The procedure has to be in standard module. Right-click each button to assign the macro. Use [tt]Application.Caller[/tt] in the procedure to get the name of clicked button.

combo

 

[N1GHTEYES]

Combo - thanks for that. It never occurred to me to use application.caller to get the name of the button.

Tony

 

[combo]

Thanks. It works only for the light-weighted excel controls (and calling cells with UDFs).

combo

 

[riskassure]

Application.Caller works perfectly!! Thank you Combo!

~~CW~~