Need macro's behavior to depend on button pushed

**JChandler22** · 03-26-2010, 03:25 PM

Hi All,

I have a spreadsheet with several different sections of data. I'd like to have a button within each section to run a macro (what the macro does is less important), but I'd like to just create one single macro which will work on all of the sections.

In short, I'm wondering if there's a way to create a single macro, have multiple buttons linked to that one macro, but have the behavior of the macro vary depending on which button is pushed.

For instance, the macro called myMacro will shade the top left cell of section X green. Three different sections - A, B, and C - each have a button linking to myMacro. If the button in section C is pushed, the top left cell of section C will be shaded green; if the button in section A is pushed, the top left cell of section A will be shaded green.

Is there a way for a macro to detect which button was pushed, and modify behavior accordingly? I'd like to avoid having to create a separate macro for each section if at all possible. I've done some forum and google searches to try to find answers on this but I've come up short. If anyone has any helpful resources on this topic I'd greatly appreciate it.

Many thanks in advance,
Jeff

**royUK** · 03-26-2010, 03:52 PM

You can create a Public Variable that is set by each button & used to determine the action

**wotadude** · 03-28-2010, 04:13 PM

Using Button from "Forms Toolbar" name the buttons as required

 Sub MyUniversalMacro()
 ' Run button specfic procedure

    Dim Choice As String

    Select Case Application.Caller
        Case "Button 3", "Button 4"
            Choice = "running macro for button """
            ' add button specific code here for buttons 3 or 4 .............
        Case "Button 5"
            Choice = "running macro for button """
            ' add button specific code here .............
        Case Else
            Choice = "No secific procedure for button """
            ' add code here for any other button .............
    End Select

    MsgBox Choice & Application.Caller & """", , "The Choosen One"
    
End Sub

**JChandler22** · 03-29-2010, 09:57 AM

These are great suggestions. Thank you both!

**royUK** · 03-29-2010, 11:46 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save