Can I use VBA to copy User Form from 1 workbook to another?

[Dave Peterson]

You may want to consider separating your code from the data.

You could create an addin that shows the form and does whatever the form does.

Along with being more elegant, you'd only have to worry about updates to the
code in the addin--not every workbook that ever got the userform.

 

[Trefor]

Dave,

All my code is in an Addin (Trusted and Protected) and run the main
spreadsheet. From a form in the main spreadsheet there is a buttun to create
a new spreadsheet with a subset of the code (attached to a worksheet rather
than a module) and a subset of the sheets and 1 x Userform (which is also
used in the main program).

The user may create hundreds of various different sub-workbooks that need
some code and the form. So my distribution at the moment is the main
spreadsheet and the addin. My work around is now a distribution of main
spreadsheet, the addin and now the form (the later I was trying to avoid).

 

[Dave Peterson]

If there are hundreds of workbooks that need some code and the form, why not
make a single addin for that purpose?

 

[Trefor]

Dave,

I like to use protected AddIn's so so any unhandled error condition does not
through the user into the code and ideally i don't want users messing with
the code.

I have many shares modules for the main user so creating a non-protected
module just to perform this one task (of which there are many others) would
either cause duplication of code or make it overly complicated to manage and
distribute.

With everything in one addin that I can distribute it just makes it easier
for me to manage.

Besides my workaround detailed below sounds like far less work, redesign and
managment overhead. I am talking about a fairly large set of code here with
about 40K lines of code.

 

[Dave Peterson]

Create two addins. Both protected.

One for the main user and one for everyone else. Distribute them the way you
see fit.

I still don't see a reason why the code/userform has to be in each of the 100's
of workbooks.

 

[N Selinger]

I am trying to do this; in fact, I have done it before...somewhere. But, now
all I get is an error at the execution of the Export method, saying
"Application defined or object defined error". What is the problem? For
reference, here is my code snippet:

Dim tmpWB As Workbook
Set tmpWB = Workbooks(ActiveWorkbook.Name)
'assign a temporary workbook object
defloc = ActiveWorkbook.Path
'get active workbook path
tmpWB.VBProject.VBComponents("UserForm1").Export Filename:=defloc &
"\NACform.frm" 'save VBA form object

 

[Chip Pearson]

Your code works fine for me, once I declared the 'defloc' variable.
You can simplify it a bit by changing

Set tmpWB = Workbooks(ActiveWorkbook.Name)
to simply

Set tmpWB = ActiveWorkbook

In order to use the Path property of the workbook, the workbook must
have been saved to disk at least once. It won't work with a new,
unsaved workbook.

You might have a look at www.cpearson.com/Excel/VBE.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

Your code worked fine for me.

Although, I would have used:
Set tmpWB = ActiveWorkbook

But that shouldn't matter to your code.

But I did get a different error if I don't allow programmatic access to the
workbook's project.

In xl2003 menus:
Tools|Macro|Security|trusted publishers
Check the bottom checkbox

 

[kristyrae21]

Hi-

I need help with the following and am not that familiar with VB, but
somehow was put on a project to fix a broken procedure. Can someone
help me with code for this...Here is the scenerio.

I need coding for a button that will copy information from the current
excel workbook the user is in (only sheet1) to another workbook
through visual basic coding.

The column info to be copied over are columns A-F, and as for rows,
the row must be greater than or equal to row 5 since rows 1-4 should
not be copied ever since they are just headers.

However, the stipulation is the rows are only to be copied if there is
a value entered in column D since that is the column they will be
entered “quantities” in.

When I click the button to copy the information over, it should prompt
a box that shows all the other open excel sheets so they can select
which one they want to copy it to.

Any ideas for coding?

 

[Dave Peterson]

I would start by recording a macro when I did it manually.

Then I'd try to make it more general (the recorded macro may not be sufficient).

If you have questions, post back with your code and specific question.