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

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" <(E-Mail Removed)> wrote in message
news:1E65A028-8BF0-4F2B-BCA8-(E-Mail Removed)...
> 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!

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" <(E-Mail Removed)> wrote in message
news:17E850BA-9218-4B12-AE7B-(E-Mail Removed)...
> 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
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:1E65A028-8BF0-4F2B-BCA8-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:1E65A028-8BF0-4F2B-BCA8-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:1E65A028-8BF0-4F2B-BCA8-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:1E65A028-8BF0-4F2B-BCA8-(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > news:1E65A028-8BF0-4F2B-BCA8-(E-Mail Removed)...
> > > > > 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