Method/code to determine which button was clicked

[MassSpecGuru]

I have an Excel 2003 workbook that has many buttons on different worksheets within it and I was wondering if there is a way to tell which button was clicked?

 

[Gary McMaster]

If they are "Form" buttons and all calling the same procedure you can use:

MsgBox Application.Caller

Gary

 

[MassSpecGuru]

If they are "Form" buttons and all calling the same procedure you can use:

MsgBox Application.Caller

Gary

Thanks for the reply, Gary - and yes, they are form buttons - but how do I get the Button name into a variable as opposed to displayed in a message box?

 

[Gary McMaster]

I usually use "Select Case" for something like this:

Public Sub Button_Click()

Select Case Application.Caller

    Case "Button 1" 'Use actual button names
        'code for button 1
        MsgBox "Button 1 clicked"
    Case "Button 2"
        'code for button 2
    'case "More buttons"
        
    Case Else
        'Any other buttons that do the same thing
End Select

End Sub

Or if you prefer:

Dim sButton As String

sButton = Application.Caller

MsgBox sButton

Gary

 

[MassSpecGuru]

I usually use "Select Case" for something like this:

Public Sub Button_Click()
 
Select Case Application.Caller
 
    Case "Button 1" 'Use actual button names
        'code for button 1
        MsgBox "Button 1 clicked"
    Case "Button 2"
        'code for button 2
    'case "More buttons"
 
    Case Else
        'Any other buttons that do the same thing
End Select
 
End Sub

Or if you prefer:

Dim sButton As String
 
sButton = Application.Caller
 
MsgBox sButton

Gary

Thanks, Gary, that works just fine!

Actually, while waiting for a reply I tried your second suggestion myself and it worked... well, when run from within Excel. Whenever I try it or your previous suggestion (either with or without debug. print) from within the VB Editor window I get a:

"Run-time error '13': Type mismatch"

error message.

Any ideas how to fix that?

MSG

 

[Gary McMaster]

I don't think I know what you mean. Although I dimensioned it as a string, Application.Caller returns a variant. It can contain more than just the name of the control including some error info.

Have a look in help at Application.Caller. It explains it better than I can.

Gary

 

[Gary McMaster]

Whenever I try it or your previous suggestion (either with or without debug. print) from within the VB Editor window I get a:

"Run-time error '13': Type mismatch"

error message.

After reading your last post more carefully (running within IDE), that error would be expected. Application.Caller is "initialized" by the form button click where its parameters are set up and passed to the procedure being called. If you run it (the called procedure) from within the IDE you have bypassed that step.

Gary