Title says it all...
I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm copying
from.

I just want to duplicate those into the new workbook.

Thanks!

I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

I returning to Excel after a long, long absence from it, so I am quite rusty
on many things; but, I would think you could just Export the stuff you want
from your current workbook and then Import it into the new workbook.

Rick

Actually I know how to do it manually. I'm looking for a way to do it in VBA
because I'm using one wb to create another and I want to send the new wb some
code and forms.

Thanks anyway.

"Rick Rothstein (MVP - VB)" wrote:

I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

I returning to Excel after a long, long absence from it, so I am quite rusty
on many things; but, I would think you could just Export the stuff you want
from your current workbook and then Import it into the new workbook.

Rick

Hi Rick,

Did the code which I suggested not work for you?


---
Regards,
Norman


"MikeZz" wrote in message
...
Actually I know how to do it manually. I'm looking for a way to do it in
VBA
because I'm using one wb to create another and I want to send the new wb
some
code and forms.

Thanks anyway.

"Rick Rothstein (MVP - VB)" wrote:

I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

I returning to Excel after a long, long absence from it, so I am quite
rusty
on many things; but, I would think you could just Export the stuff you
want
from your current workbook and then Import it into the new workbook.

Rick

Hi Mike,

Try something like:

'=============
Public Sub Tester()
Dim srcWB As Workbook
Dim destWb As Workbook
Const sStr As String = "C:\myFile.frm"

Set srcWB = Workbooks("MyBook1.xls")
Set destWb = Workbooks("MyBook2.xls")

srcWB.VBProject.VBComponents("Userform1").Export _
Filename:=sStr
destWb.VBProject.VBComponents.Import _
Filename:=sStr
Kill sStr
End Sub
'<<=============

For more informatoion on exporting / importing modules,
see Chip Pearson at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm


---
Regards,
Norman


"MikeZz" wrote in message
...
Title says it all...
I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

Thanks!

OMG Norman,
This is GREAT! It's so easy!

It even works forms and I also assume sheets or any other VBObjects....
Copys the physical form as well as all the code.

Cool THANKS!

"Norman Jones" wrote:

Hi Mike,

Try something like:

'=============
Public Sub Tester()
Dim srcWB As Workbook
Dim destWb As Workbook
Const sStr As String = "C:\myFile.frm"

Set srcWB = Workbooks("MyBook1.xls")
Set destWb = Workbooks("MyBook2.xls")

srcWB.VBProject.VBComponents("Userform1").Export _
Filename:=sStr
destWb.VBProject.VBComponents.Import _
Filename:=sStr
Kill sStr
End Sub
'<<=============

For more informatoion on exporting / importing modules,
see Chip Pearson at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm


---
Regards,
Norman


"MikeZz" wrote in message
...
Title says it all...
I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

Thanks!

Norman,

I tried this but I get a "Run-time error '1004': Programmatic access to
Visual Basic Project is not trusted". Do you know why?

The code is in a pw protected project, but the form I exported to a temp
folder and the new workbook where i am copying the form to is not.

--
Trefor


"Norman Jones" wrote:

Hi Mike,

Try something like:

'=============
Public Sub Tester()
Dim srcWB As Workbook
Dim destWb As Workbook
Const sStr As String = "C:\myFile.frm"

Set srcWB = Workbooks("MyBook1.xls")
Set destWb = Workbooks("MyBook2.xls")

srcWB.VBProject.VBComponents("Userform1").Export _
Filename:=sStr
destWb.VBProject.VBComponents.Import _
Filename:=sStr
Kill sStr
End Sub
'<<=============

For more informatoion on exporting / importing modules,
see Chip Pearson at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm


---
Regards,
Norman


"MikeZz" wrote in message
...
Title says it all...
I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

Thanks!

This is a security setting that belongs to each user.

In xl2003 menus:
Tools|Macro|Security|Trusted publishers tab
It's a checkbox at the bottom of the dialog.


Trefor wrote:

Norman,

I tried this but I get a "Run-time error '1004': Programmatic access to
Visual Basic Project is not trusted". Do you know why?

The code is in a pw protected project, but the form I exported to a temp
folder and the new workbook where i am copying the form to is not.

--
Trefor

"Norman Jones" wrote:

Hi Mike,

Try something like:

'=============
Public Sub Tester()
Dim srcWB As Workbook
Dim destWb As Workbook
Const sStr As String = "C:\myFile.frm"

Set srcWB = Workbooks("MyBook1.xls")
Set destWb = Workbooks("MyBook2.xls")

srcWB.VBProject.VBComponents("Userform1").Export _
Filename:=sStr
destWb.VBProject.VBComponents.Import _
Filename:=sStr
Kill sStr
End Sub
'<<=============

For more informatoion on exporting / importing modules,
see Chip Pearson at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm


---
Regards,
Norman


"MikeZz" wrote in message
...
Title says it all...
I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

Thanks!




--

Dave Peterson

Dave,

Many thanks, can't believe I didn't think to check that first. I would have
set that before, is there something that would have unchecked this?


--
Trefor


"Dave Peterson" wrote:

This is a security setting that belongs to each user.

In xl2003 menus:
Tools|Macro|Security|Trusted publishers tab
It's a checkbox at the bottom of the dialog.


Trefor wrote:

Norman,

I tried this but I get a "Run-time error '1004': Programmatic access to
Visual Basic Project is not trusted". Do you know why?

The code is in a pw protected project, but the form I exported to a temp
folder and the new workbook where i am copying the form to is not.

--
Trefor

"Norman Jones" wrote:

Hi Mike,

Try something like:

'=============
Public Sub Tester()
Dim srcWB As Workbook
Dim destWb As Workbook
Const sStr As String = "C:\myFile.frm"

Set srcWB = Workbooks("MyBook1.xls")
Set destWb = Workbooks("MyBook2.xls")

srcWB.VBProject.VBComponents("Userform1").Export _
Filename:=sStr
destWb.VBProject.VBComponents.Import _
Filename:=sStr
Kill sStr
End Sub
'<<=============

For more informatoion on exporting / importing modules,
see Chip Pearson at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm


---
Regards,
Norman


"MikeZz" wrote in message
...
Title says it all...
I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

Thanks!




--

Dave Peterson

I've only seen it changed when I changed it.

But you could have something that changes the registry. Anything is possible
(but not probable???).

Trefor wrote:

Dave,

Many thanks, can't believe I didn't think to check that first. I would have
set that before, is there something that would have unchecked this?

--
Trefor

"Dave Peterson" wrote:

This is a security setting that belongs to each user.

In xl2003 menus:
Tools|Macro|Security|Trusted publishers tab
It's a checkbox at the bottom of the dialog.


Trefor wrote:

Norman,

I tried this but I get a "Run-time error '1004': Programmatic access to
Visual Basic Project is not trusted". Do you know why?

The code is in a pw protected project, but the form I exported to a temp
folder and the new workbook where i am copying the form to is not.

--
Trefor

"Norman Jones" wrote:

Hi Mike,

Try something like:

'=============
Public Sub Tester()
Dim srcWB As Workbook
Dim destWb As Workbook
Const sStr As String = "C:\myFile.frm"

Set srcWB = Workbooks("MyBook1.xls")
Set destWb = Workbooks("MyBook2.xls")

srcWB.VBProject.VBComponents("Userform1").Export _
Filename:=sStr
destWb.VBProject.VBComponents.Import _
Filename:=sStr
Kill sStr
End Sub
'<<=============

For more informatoion on exporting / importing modules,
see Chip Pearson at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm


---
Regards,
Norman


"MikeZz" wrote in message
...
Title says it all...
I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

Thanks!




--

Dave Peterson


--

Dave Peterson

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


--
N Selinger


"Norman Jones" wrote:

Hi Mike,

Try something like:

'=============
Public Sub Tester()
Dim srcWB As Workbook
Dim destWb As Workbook
Const sStr As String = "C:\myFile.frm"

Set srcWB = Workbooks("MyBook1.xls")
Set destWb = Workbooks("MyBook2.xls")

srcWB.VBProject.VBComponents("Userform1").Export _
Filename:=sStr
destWb.VBProject.VBComponents.Import _
Filename:=sStr
Kill sStr
End Sub
'<<=============

For more informatoion on exporting / importing modules,
see Chip Pearson at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm


---
Regards,
Norman


"MikeZz" wrote in message
...
Title says it all...
I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

Thanks!

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)


On Wed, 23 Sep 2009 17:52:01 -0700, N Selinger
wrote:

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").Expor t Filename:=defloc &
"\NACform.frm" 'save VBA form object

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

N Selinger wrote:

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

--
N Selinger

"Norman Jones" wrote:

Hi Mike,

Try something like:

'=============
Public Sub Tester()
Dim srcWB As Workbook
Dim destWb As Workbook
Const sStr As String = "C:\myFile.frm"

Set srcWB = Workbooks("MyBook1.xls")
Set destWb = Workbooks("MyBook2.xls")

srcWB.VBProject.VBComponents("Userform1").Export _
Filename:=sStr
destWb.VBProject.VBComponents.Import _
Filename:=sStr
Kill sStr
End Sub
'<<=============

For more informatoion on exporting / importing modules,
see Chip Pearson at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm


---
Regards,
Norman


"MikeZz" wrote in message
...
Title says it all...
I'm using VBA to set up a new workbook.
I want the new workbook to have stand alone macros and user forms.

I can create the user forms and macros within the workbook that I'm
copying
from.

I just want to duplicate those into the new workbook.

Thanks!




--

Dave Peterson

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?

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.

kristyrae21 wrote:

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