Exporting & Importing Macro with VBA

[s_woodbury]

Hey all, I'm trying to move a module to a new Workbook and assign it to a shape

I can Export/Import it but when I assign it I'm assigning the macro from the Original, not the New Workbook.

The New Workbook's name will change each time it's run. I don't know how to code a variable into the OnAction Line.

Here's the code I'm using any suggestions is appreciated. I'm stuck!

'Copy Module from ThisWorkBook to the path and folder of ThisWorkBook
MSRMaster.VBProject.VBComponents("Active_Customer_Filter").Export strTempFile1

'Copy the Module we saved earlier from ThisWorkBook to the New Workbook
Application.VBE.ActiveVBProject.VBComponents.Import (strTempFile1)

'Assign Macro to Shape already created
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.OnAction = "Active_Customer_Filter.bas"

 

[My Aswer Is This]

Do you know you can put Macros into your Personal Workbook
And then run the Macro from any Workbook New or Old?

 

[s_woodbury]

Do you know you can put Macros into your Personal Workbook
And then run the Macro from any Workbook New or Old?

Thanks - I read about that but the end user is not myself, it's many users.

 

[s_woodbury]

This solved my problem:

Selection.OnAction = _
"'" & ActiveWorkbook.Name & "'" & "!Active_Customer_Filter.Active_Customer_Filter"

 

[Norie]

How are you creating the new workbook?

 

[s_woodbury]

How are you creating the new workbook?

The new workbook is a .xls data file that the user is prompted to locate on their computer. In the code I save as a .xlsm

 

[Norie]

Why not create a workbook with all the required buttons/code etc. in it?

Then when you create the new workbook use that as a template and copy the data from the user selected file to it.