Ask a new question

How can I move userforms and macros from one Excel 2010 workbook to another?

I have created a series of userforms and a macro in one Excel 2010 workbook using some test data.

I wanted to use the (now) working macro and forms with live data, however when I open a new blank workbook and click on the macro icon in the QAT Excel opens my testing workbook, and the macro and useforms only operate within the old testing workbook!

What is the process for moving the forms and the macro to another workbook with a different xls or xlsx filename?

You can only store macros in a macro enabled workbook, so that lets out xlsx format (use xlsm).

Open the two workbooks, Open the VBA Editor (ALT+F11).

 If it is not displayed already, display the Project Explorer window (View > Project Explorer or CTRL+R), then locate the module(s) and Forms that you want to copy in that window and drag them from one project to the other.

As for the viability of the macros for use in the other workbook, that would depend on what they do. We can but guess.
Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks Graham,

 

I will give your suggestion a try when I get my laptop with the Excel 2010 system back tomorrow. By the way the macros will still operate fine - I didn't want to risk screwing up my live data hence I used a test workbook (different file name) with identical worksheet names and data placement, but now I am happy with the code I would like to migrate it to the live environment - a sound way of working I believe.   

 

In the meantime I was interested in your comment about XLSM and only being able to store macros in this file format. The reason I say this is I actually developed the macros and user forms in Excel 2007 and I've always used XLS as far as I can remember. The macro works fine in my test data worksheet (XLS file type), and I can edit the code, modify it and store it all in an XLS file.

 

Do you know what is the real impact of the different file types (XLS and XLSM at least), or are they really only cosmetic and useful perhaps as a reminder? 

 

regards, Bernard

Windows 10 Pro 64 bit and Windows 11

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Yes you can (continue to) save macros to the "old" XLS" format. Graham meant that you cannot save macros in the new default XLSX format. In the new formats you have to explicitly select the XLSM file type if you want to save macros.

The old, XLS, file type is a "binary" format.  Think of it (very) loosely as being similar to a compiled program. The contents cannot be easily read using simple tools like notepad.  The new formats, XLSX and XLSM, are "XML based". That means they are primarily text based. If you change the file extension to ZIP, ie by simply adding .ZIP to the end of the file name, you can explore the underlying structure of the file. Most of the elements are text based so they can be read by simple programs like notepad.

.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks Rohn, I see what you mean.

 

Graham,

 

I copied my data from the old XLS file into a new workbook and then did as you suggested by draging and droping the 4 forms and 1 module, however I still did not see any macro after I had saved this new worksheet as an XLSM file type. So I went back into it and discovered that I had some code in one of the sheets - this was the code that activated/displayed the first form - I needed to copy and paste this from the old workbook/sheet into the new workbook/sheet and then - lo and behold - when I go into macros I see my macro and it all works like a treat.

 

So it looks like one needs to copy the forms, modules and also any code in the sheets - I admit I'm no expert in all this stuff so maybe your earlier feedback implied what I just said or maybe I have some strange code due to my ignorance of how to do these things. Anyway thanks for your help.

 

Bernard  

Windows 10 Pro 64 bit and Windows 11

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 1,748 Applies to: