Copy Module to new workbook & add button

JBRTaylor

New Member
Joined
Sep 29, 2010
Messages
12
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
Code:
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
 
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.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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>
 
Upvote 0
I tried it with:

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Hi Guys
I have been playing around with this and have come up with another problem. the following bit of code was working great:
Code:
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
 
Upvote 0
Assuming you have changed:

Code:
Set wkbDest = Workbooks("Book1.xlsm")

you could use:

Code:
Selection.OnAction = wkbDest.Name & "!msg"
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top