Copy Module to new workbook & add button

[JBRTaylor]

Hi
I am trying to copy a module containing a macro from one spreadsheet (copymod.xls) to another workbook (book1.xls) and then insert a button to run the macro I have just copied into book1.

Using the code below the macro runs from the copymod sheet and not the book1 sheet as I would like it to. I am quite new to VBA and don’t fully understand all the code yet.

Any help would be greatly appreciated.
Thanks in advance

Sub copymod()
    Workbooks.Add
    Dim FName As String
With Workbooks("copymod")
    FName = "C:\code.bas"
    .VBProject.VBComponents("Module1").Export FName
End With
Workbooks("book1").VBProject.VBComponents.Import FName
Kill "C:\code.bas"
    ActiveSheet.Buttons.Add(54.75, 32.25, 128.25, 61.5).Select
    Selection.OnAction = "msg"
    Application.CommandBars("Forms").Visible = False
    Range("F8").Select
End Sub

 

[JBRTaylor]

Hi PGC01,
Not too sure how your solution is working or not working. Tt appeared to copy the code across but if i then copied the book1 to a new computer i found it was trying to run the macro which resides in the copymod workbook.
I would have to play around with it a bit more to come up with a better explination but Andrews solution has fixed that issue. I'm not very familiar with VBA, maybe after i have been on a course i won't make quite so many "silly mistakes". Thanks for your help and patience.

 

[Domenic]

Yep, it looks like the button indeed gets assigned the macro from the original workbook. I get the same result as Andrew when testing it using his method. Also, the following would confirm this as well...

<font face=Courier New><SPAN style="color:#00007F">With</SPAN> Btn<br>    .Caption = "Button Name" <SPAN style="color:#007F00"></SPAN><br>    .OnAction = "msg"<br>    MsgBox .OnAction<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    </FONT>

 

[pgc01]

I tried it with:

Sub msg()
    MsgBox "Hello from " & ThisWorkbook.Name
End Sub

in Excel 2003 and got "Hello from CopyMod.xls" when I click the button in Book1.xls. That's why I suggested a change.

Thank you Andrew. I tested with excel 2010 and it worked ok. I'll try to test it in another version when I have the opportunity to try to find out why the difference.

 

[pgc01]

Hi PGC01,
Not too sure how your solution is working or not working. Tt appeared to copy the code across but if i then copied the book1 to a new computer i found it was trying to run the macro which resides in the copymod workbook.
I would have to play around with it a bit more to come up with a better explination but Andrews solution has fixed that issue. I'm not very familiar with VBA, maybe after i have been on a course i won't make quite so many "silly mistakes". Thanks for your help and patience.

Jon, thank you for trying but don't worry, it must be some detail that I'm overlooking. I'm glad you've got a solution that works for you.

 

[pgc01]

Yep, it looks like the button indeed gets assigned the macro from the original workbook. I get the same result as Andrew when testing it using his method. Also, the following would confirm this as well...

With Btn
.Caption = "Button Name"
.OnAction = "msg"
MsgBox .OnAction
End With

Well Domenic, since you too confirm that it doesn't work for you the way it should there's only one logical expanation: my excel is much cleverer than all of your excel's, it understands what I want and does it no matter the code I write. (Another possible explanation is that I'm doing some dumb mistake).

 

[Domenic]

PGC,

I finally got a chance to test this using your code, and the same thing happens. That is, the new button in book1.xls gets assigned the macro from copymod.xls. I'm using Excel 2010. Out of curiosity, which version are you using?

 

[pgc01]

Domenic, thank you for testing.

I found the problem. The code of the CopyMod Sub that I was using was in a module in Book1. I added a module to the CopyMod workbook and run the code from there and I too got the button referring to the source module.

So in conclusion, if you create a button in another workbook and assign an OnAction macro unqualified, it will refer to the workbook where the code that creates the button resides and not to the workbook where the button is created.

... and I tested in excel 2010.

 

[Domenic]

That's great, thanks PGC!

 

[JBRTaylor]

Hi Guys
I have been playing around with this and have come up with another problem. the following bit of code was working great:

Selection.OnAction = "Book1.xls!msg"
[\code]
however if i reuse the macro (which copies part of the spreadsheet and the msg macro) the next workbook will be book2 and so on.
Also if poss could i add some code to save the file before it assigns the macro to the button?
Thanks
Jon

 

[Andrew Poulsom]

Assuming you have changed:

Set wkbDest = Workbooks("Book1.xlsm")

you could use:

Selection.OnAction = wkbDest.Name & "!msg"