Problem with .OnAction

[johnnyL]

Module 1 code Which includes:

Sub CreateAClickableMacroButton()

... Some Code

    MacroToRunWhenButtonIsClicked = "Test"    ' <--- Set this to the name of the macro that you want to run when button is clicked

... Some Code

    .OnAction = MacroToRunWhenButtonIsClicked                       ' Subroutine to run when button is clicked

... Some Code

End Sub


Sub Test()
    MsgBox "You clicked the button"
End Sub

Macros are enabled.
This runs fine when the all of the code is in Module1.

When I Move the Sub Test() subroutine into the sheet that has the button added, it errors when I click the button, "Cannot run the macro ... "The macro may not be available in this workbook or all macros may be disabled."

It longer sees the 'Sub Test()' for some reason.

Is there a way to prefix the name of the subroutine so that the button will see it in the sheet that the button is located in, or does the subroutine HAVE to be in a module?

 

[Michael M]

OnAction refers to a shape object, so you would use

activesheet.Shapes("yourshapename").OnAction = "Test"

 

[Marcelo Branco]

Or maybe...
MacroToRunWhenButtonIsClicked = "Sheet1.Test"
where Sheet1 is codename of the sheet (not the name)

M.

 

[johnnyL]

Sorry I got sidetracked.

Thank You @Marcelo Branco! Your response worked!

Thank you so much!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.