Trouble with an assign macro to a shape for exported file

[atf32]

Using Excel 2010, window 7

I recently used the Illustration toolbar (Shapes) to create a button and then assigned a macro to it. I did so because it provides more color options, etc. This works well in the workbook and looks great. And the called macro is in the module for the worksheet. However, part of the function of the workbook is to export a few of the tabs, including the one with my button and then rename the tab accordingly. My problem is that the button on the exported file does not work. I actually have to manually reassign the macro to the button to get it to work.

Can someone please confirm whether or not this is doable and if so provide a sample of how to do this?

 

[AlphaFrog]

Something like this can assign a macro to a button

ActiveSheet.Buttons(1).OnAction = "Macro1"

If you can't modify the above to suit you needs:
-Can you show your code?
-Is there only one button on the sheet?
-What's the name of the macro and sheet name.

 

[atf32]

I tried to modify your code, like you said. I used the following:

 Private Sub Worksheet_Activate()
    ActiveSheet.Shapes.Range(Array("BtnHide_SBTRows")) = "CALL_Code1"
End Sub [\Code]

Note: 1) "BtnHide_SBTRows" is the name of the shape that I am using as a button
        2) "CALL_Code1" is the called procedure that does something

Not sure, but I think that since the name of the worksheet changes during the export, that that becomes the disconnect of the assigned macro.  However, if I use a Form control button or ActiveX Button I don't have this issue.  Just can't use the options to make the button pop.

 

[AlphaFrog]

Can you record a macro where you manually assign the macro to the shape and post that code?

 

[atf32]

Sure:

 Sub attach_Code()' attach_Code Macro
'
    ActiveSheet.Shapes.Range(Array("BtnHide_SBTRows")).Select
    Selection.OnAction = "Sheet16.CALL_Code1"
End Sub [\Code]

 

[atf32]

That worked!!! Thank you for pointing me in the right direction.