Copy module to new workbook, create a button in the new workbook and assigns a new workbook macro

[Eagle72]

Hi Everyone,

I am copying a module from one workbook to another so that in the future, the new workbook can run a macro from within itself rather than referring to an external workbook (which may or may not be open in the future). Then I create a new workbook by moving a sheet from one to the other and create a new button in the new workbook. That button is supposed to run a macro in that new workbook. The problem is that the macro assigned to the button refers to the macro from the original workbook, not the new one. I'm running Windows 7 and Excel 2013.

Sub Macro1()
    ActiveWorkbook.VBProject.VBComponents("module1").Export ("F:\Module11.bas")
    ActiveSheet.Move
    ActiveWorkbook.VBProject.VBComponents.Import ("F:\Module11.bas")
    ActiveSheet.Buttons.Add(410.6, 17.4, 213, 35.2).Select
    Selection.OnAction = "ButtonTest"
End Sub

If my original workbook was named Book2, the button in the new workbook has this macro assigned to it: 'Book2!ButtonTest' rather than the native 'ButtonTest'.

Please help.

 

[Eagle72]

Hi Everyone,

I am copying a module from one workbook to another so that in the future, the new workbook can run a macro from within itself rather than referring to an external workbook (which may or may not be open in the future). Then I create a new workbook by moving a sheet from one to the other and create a new button in the new workbook. That button is supposed to run a macro in that new workbook. The problem is that the macro assigned to the button refers to the macro from the original workbook, not the new one. I'm running Windows 7 and Excel 2013.

Sub Macro1()
    ActiveWorkbook.VBProject.VBComponents("module1").Export ("F:\Module11.bas")
    ActiveSheet.Move
    ActiveWorkbook.VBProject.VBComponents.Import ("F:\Module11.bas")
    ActiveSheet.Buttons.Add(410.6, 17.4, 213, 35.2).Select
    Selection.OnAction = "ButtonTest"
End Sub

If my original workbook was named Book2, the button in the new workbook has this macro assigned to it: 'Book2!ButtonTest' rather than the native 'ButtonTest'.

Please help.

I found a solution. replacing the Selection.OnAction command with the one below solves the problem:

Selection.OnAction = ActiveWorkbook.Name & "!ButtonTest"