Creating a Button that runs a Macro Using VBA

[aryaden]

I would like to create a macro that creates a button to run another macro in a specific sheet in a workbook. I have multiple workbooks with a worksheet (same title in all workbooks) that i need to place a "run macro" button in cell B2 for. The button when clicked would run another macro I have already created.

 

[My Aswer Is This]

I would suggest creating a Macro that does what you want.
Put that Macro in your Personal Workbook.
And activate the script by using a shortcut Key.
This script would run on any workbook you have open.
No need having a Button to run the script.

 

[aryaden]

I would suggest creating a Macro that does what you want.
Put that Macro in your Personal Workbook.
And activate the script by using a shortcut Key.
This script would run on any workbook you have open.
No need having a Button to run the script.

Thanks for the suggestion but after I make changes I am sending the workbook to a client. I wanted them to be able to make changes and run my macro if needed by clicking a button.

 

[mikerickson]

You could loop through the sheets with code like this

With ActiveSheet
    With .Buttons.Add(184, 57, 120, 30)
        .Top = .Parent.Range("B2").Top
        .Left = .Parent.Range("B2").Left
        .OnAction = "myOtherMacro"
    End With
End With

 

[aryaden]

You could loop through the sheets with code like this

With ActiveSheet
    With .Buttons.Add(184, 57, 120, 30)
        .Top = .Parent.Range("B2").Top
        .Left = .Parent.Range("B2").Left
        .OnAction = "myOtherMacro"
    End With
End With

Thank you! I will try that, if I wanted to apply this to a specific worksheet named "Data_1" how would I change the code?

 

[mikerickson]

You would replace ActiveSheet with ThisWorkbook.Sheets("Data_1")

 

[aryaden]

You would replace ActiveSheet with ThisWorkbook.Sheets("Data_1")

Thank you! That worked perfectly