How to refer to the calling button in a macro

Hi,

I hope you can help me. I created a button (from the Forms toolbar) and assigned a macro to it.

I want to be able to make changes to the button that calls the macro. How do I refer to it? Using "Selection" does not work.

Thank you!

[Dave Hawley]

Hi

This should give you the gist:

Sub WhoClicked()
Select Case Application.Caller

Case "Button 1"
MsgBox "It was button 1"
Case "Button 2"
MsgBox "It was button 2"
Case "Button 3"
MsgBox "It was button 3"
Case "Button 4"
MsgBox "It was button 4"

Case Else
MsgBox "Only buttons please"
End Select
End Sub


Just add 4 buttons and assign them all to this Procedure. If you want to get really flash call your buttons the same name as the Prodecure they should run, then you can use:

Sub RunWhat()
Dim strMacroName As String
strMacroName = Application.Caller
Run strMacroName
End Sub

That's exactly what I was looking for. Thanks!

[Masasar]

Ok, I've looked everywhere and this old post is as relevent to what I am looking for as I can find.

I have two spreadsheets I am working with; a template and a macro book.

The template has buttons that run from single macro and uses the case caller above to determine which procedures to do.

In my macro book, I need to click on a button in the template in order to complete the process I am working on. I basically need something that can call just the "Button 2" case in template macro, or click on Button 2.

I've tried as many work arounds I can think of, but nothing works. The template cannot be altered and I cannot re-create the Button 2 procedures in my macro book (due to network accessing rights). The only thing I think can be done is calling the case in the template's macro or writing a mouse click procedure (which I am failing at).

Thanks