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

Question

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!

FaneDuru · Accepted Answer · 2021-09-17 17:19:35Z

1

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")...

answered Sep 17, 2021 at 17:19

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FaneDuru Over a year ago

@Elio Fernandes I am afraid you do not understand what is it about, no offence... In the above way you can assign the "Macro2" Sub to a "Button 1" shape situated on Sheet1, being wherever you want to be. I mean, in a standard module on in another sheet code module, if you understand what that mean... Selecting the shape is a bad habit, anyhow, but the above code does not select anything.

Niels Bjørn Hansen Over a year ago

Thank you so much FaneDuru. This worked! The recorded macro threw me off. Thanks for getting me back on track. :-)

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related