How to assign macro to button without using "active sheet" in VBA?-CodePudding

I am trying to change the macro assigned to a button from a different sheet than where the button is. This is how far I've gotten:

Sub Macro1()
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.OnAction = "Macro2"
End Sub

However, the above code requres me to be on the active sheet. I have tried the following:

Sub Macro1()
    Sheet1.Shapes.Range(Array("Button 1")).Select
    Selection.OnAction = "Macro2"
End Sub

However, this will give me an "Object doesn't support this property or method" error.

Why doesn't it work when "ActiveSheet" is replaced with "Sheet1"?

And why can't I collect the two lines of code into one line?:

Sub Macro1()
    Sheet1.Shapes.Range(Array("Button 1")).OnAction = "Macro2"
End Sub

Any help would be appreciated!

CodePudding user response：

Please, simple try:

Sheet1.Shapes("Button 1").OnAction = "Macro2"

Of course, a macro named "Macro2" should exist in a standard module. If in a sheet code module, the sheet CodeName is necessary in front of the macro name ("Sheet1.Macro2")...