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

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!

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

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

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

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!

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

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

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

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

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

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!

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

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!

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

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

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

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

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

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

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

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor


"Dave Peterson" wrote:

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

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

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson

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

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor


"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson

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

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

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

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

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

Not that I can do.

Trefor wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor

"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

Trefor,

Here's a little routine I run prior to running any macros that require the
Trusted VBA Setting. Just checks to make sure the setting is correct before
even starting. In my case, the macro ran for sometimes 15 minutes before it
finally crashed so it's good to test setting first.

Private Sub Check_Trusted_VBA()
Dim trustMe, trustMess

trustMe = VBAIsTrusted

If trustMe = False Then
trustMess = "PRIOR TO RUNNING THIS UTILITY" & Chr(10)
trustMess = trustMess & " You need to adjust your Macro Security
Settings as shown below:" & Chr(10) & Chr(10)
trustMess = trustMess & "Click on Excel Menu: Tools Macro
Security" & Chr(10)
trustMess = trustMess & "Then Click on 'Trusted Publishers' Tab" & Chr(10)
trustMess = trustMess & "'Check' the 2nd box: 'Trust Access to Visual
Basic Project'" & Chr(10)
trustMess = trustMess & "Then 'Save' this File and Re-Run the Utility."
& Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "By doing this, you are allowing this utility to
copy macros" & Chr(10)
trustMess = trustMess & "to the Newly created Workbook which are needed
for it to work." & Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "Most Macros don't need this feature to work,
but this one does." & Chr(10)
trustMess = trustMess & "After running this macro, you can always
'Uncheck' that box if you have security concerns." & Chr(10)
trustMe = MsgBox(trustMess, vbCritical)
End


End If

End Sub


"Trefor" wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

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

You didn't post how VBAIsTrusted is determined.

In newer versions (xl2002+???), you could do something like:

Dim wkbk As Workbook
Set wkbk = Workbooks("someworkbooknamehere.xla")
If wkbk.VBProject.Protection = 0 Then
MsgBox wkbk.Name & "'s project is NOT protected"
End If



MikeZz wrote:

Trefor,

Here's a little routine I run prior to running any macros that require the
Trusted VBA Setting. Just checks to make sure the setting is correct before
even starting. In my case, the macro ran for sometimes 15 minutes before it
finally crashed so it's good to test setting first.

Private Sub Check_Trusted_VBA()
Dim trustMe, trustMess

trustMe = VBAIsTrusted

If trustMe = False Then
trustMess = "PRIOR TO RUNNING THIS UTILITY" & Chr(10)
trustMess = trustMess & " You need to adjust your Macro Security
Settings as shown below:" & Chr(10) & Chr(10)
trustMess = trustMess & "Click on Excel Menu: Tools Macro
Security" & Chr(10)
trustMess = trustMess & "Then Click on 'Trusted Publishers' Tab" & Chr(10)
trustMess = trustMess & "'Check' the 2nd box: 'Trust Access to Visual
Basic Project'" & Chr(10)
trustMess = trustMess & "Then 'Save' this File and Re-Run the Utility."
& Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "By doing this, you are allowing this utility to
copy macros" & Chr(10)
trustMess = trustMess & "to the Newly created Workbook which are needed
for it to work." & Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "Most Macros don't need this feature to work,
but this one does." & Chr(10)
trustMess = trustMess & "After running this macro, you can always
'Uncheck' that box if you have security concerns." & Chr(10)
trustMe = MsgBox(trustMess, vbCritical)
End

End If

End Sub

"Trefor" wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

Hi Dave,
I forgot the other function in my haste. Here it is:

Private Function VBAIsTrusted() As Boolean
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function


"Dave Peterson" wrote:

You didn't post how VBAIsTrusted is determined.

In newer versions (xl2002+???), you could do something like:

Dim wkbk As Workbook
Set wkbk = Workbooks("someworkbooknamehere.xla")
If wkbk.VBProject.Protection = 0 Then
MsgBox wkbk.Name & "'s project is NOT protected"
End If



MikeZz wrote:

Trefor,

Here's a little routine I run prior to running any macros that require the
Trusted VBA Setting. Just checks to make sure the setting is correct before
even starting. In my case, the macro ran for sometimes 15 minutes before it
finally crashed so it's good to test setting first.

Private Sub Check_Trusted_VBA()
Dim trustMe, trustMess

trustMe = VBAIsTrusted

If trustMe = False Then
trustMess = "PRIOR TO RUNNING THIS UTILITY" & Chr(10)
trustMess = trustMess & " You need to adjust your Macro Security
Settings as shown below:" & Chr(10) & Chr(10)
trustMess = trustMess & "Click on Excel Menu: Tools Macro
Security" & Chr(10)
trustMess = trustMess & "Then Click on 'Trusted Publishers' Tab" & Chr(10)
trustMess = trustMess & "'Check' the 2nd box: 'Trust Access to Visual
Basic Project'" & Chr(10)
trustMess = trustMess & "Then 'Save' this File and Re-Run the Utility."
& Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "By doing this, you are allowing this utility to
copy macros" & Chr(10)
trustMess = trustMess & "to the Newly created Workbook which are needed
for it to work." & Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "Most Macros don't need this feature to work,
but this one does." & Chr(10)
trustMess = trustMess & "After running this macro, you can always
'Uncheck' that box if you have security concerns." & Chr(10)
trustMe = MsgBox(trustMess, vbCritical)
End

End If

End Sub

"Trefor" wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

MikeZz / Dave,

I wasnt actually having trouble trapping the error for the import into the
unprotected project. I did need to ensure the Project was Trusted and have
now added the following code which appears to work fine. I intend these this
flag set for me and my users, so this should be a once off check.

' Import temp file
WBTemp2.VBProject.VBComponents.Import FileName:=TempFile

If Err = 1004 Then
msg = "An error has occured most likely because an Excel security
setting is not correct." & Chr(10) & Chr(10)
msg = msg + "From 'Tools Macro Security' make sure 'Trust access
to Visual Basic Project' has been ticked" & Chr(10) & Chr(10)
msg = msg + "This operation will now abort, please retry after the
setting has been corrected." & Chr(10) & Chr(10)
MsgBox msg, vbExclamation
End If

My problem is on the export of the form a Trusted, but Protected Project.
With a Trusted and Protected Project the following does not cause an error,
but does not export the file either. My current work around is to pre-export
the Form and then Import in VBA as and when I need, but I was looking for a
more elegant solution.

' Export Form to Temp file
ThisWorkbook.VBProject.VBComponents("RSCForm").Exp ort FileName:=TempFile

--
Trefor


"MikeZz" wrote:

Hi Dave,
I forgot the other function in my haste. Here it is:

Private Function VBAIsTrusted() As Boolean
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function


"Dave Peterson" wrote:

You didn't post how VBAIsTrusted is determined.

In newer versions (xl2002+???), you could do something like:

Dim wkbk As Workbook
Set wkbk = Workbooks("someworkbooknamehere.xla")
If wkbk.VBProject.Protection = 0 Then
MsgBox wkbk.Name & "'s project is NOT protected"
End If



MikeZz wrote:

Trefor,

Here's a little routine I run prior to running any macros that require the
Trusted VBA Setting. Just checks to make sure the setting is correct before
even starting. In my case, the macro ran for sometimes 15 minutes before it
finally crashed so it's good to test setting first.

Private Sub Check_Trusted_VBA()
Dim trustMe, trustMess

trustMe = VBAIsTrusted

If trustMe = False Then
trustMess = "PRIOR TO RUNNING THIS UTILITY" & Chr(10)
trustMess = trustMess & " You need to adjust your Macro Security
Settings as shown below:" & Chr(10) & Chr(10)
trustMess = trustMess & "Click on Excel Menu: Tools Macro
Security" & Chr(10)
trustMess = trustMess & "Then Click on 'Trusted Publishers' Tab" & Chr(10)
trustMess = trustMess & "'Check' the 2nd box: 'Trust Access to Visual
Basic Project'" & Chr(10)
trustMess = trustMess & "Then 'Save' this File and Re-Run the Utility."
& Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "By doing this, you are allowing this utility to
copy macros" & Chr(10)
trustMess = trustMess & "to the Newly created Workbook which are needed
for it to work." & Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "Most Macros don't need this feature to work,
but this one does." & Chr(10)
trustMess = trustMess & "After running this macro, you can always
'Uncheck' that box if you have security concerns." & Chr(10)
trustMe = MsgBox(trustMess, vbCritical)
End

End If

End Sub

"Trefor" wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

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 wrote:

MikeZz / Dave,

I wasnt actually having trouble trapping the error for the import into the
unprotected project. I did need to ensure the Project was Trusted and have
now added the following code which appears to work fine. I intend these this
flag set for me and my users, so this should be a once off check.

' Import temp file
WBTemp2.VBProject.VBComponents.Import FileName:=TempFile

If Err = 1004 Then
msg = "An error has occured most likely because an Excel security
setting is not correct." & Chr(10) & Chr(10)
msg = msg + "From 'Tools Macro Security' make sure 'Trust access
to Visual Basic Project' has been ticked" & Chr(10) & Chr(10)
msg = msg + "This operation will now abort, please retry after the
setting has been corrected." & Chr(10) & Chr(10)
MsgBox msg, vbExclamation
End If

My problem is on the export of the form a Trusted, but Protected Project.
With a Trusted and Protected Project the following does not cause an error,
but does not export the file either. My current work around is to pre-export
the Form and then Import in VBA as and when I need, but I was looking for a
more elegant solution.

' Export Form to Temp file
ThisWorkbook.VBProject.VBComponents("RSCForm").Exp ort FileName:=TempFile

--
Trefor

"MikeZz" wrote:

Hi Dave,
I forgot the other function in my haste. Here it is:

Private Function VBAIsTrusted() As Boolean
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function


"Dave Peterson" wrote:

You didn't post how VBAIsTrusted is determined.

In newer versions (xl2002+???), you could do something like:

Dim wkbk As Workbook
Set wkbk = Workbooks("someworkbooknamehere.xla")
If wkbk.VBProject.Protection = 0 Then
MsgBox wkbk.Name & "'s project is NOT protected"
End If



MikeZz wrote:

Trefor,

Here's a little routine I run prior to running any macros that require the
Trusted VBA Setting. Just checks to make sure the setting is correct before
even starting. In my case, the macro ran for sometimes 15 minutes before it
finally crashed so it's good to test setting first.

Private Sub Check_Trusted_VBA()
Dim trustMe, trustMess

trustMe = VBAIsTrusted

If trustMe = False Then
trustMess = "PRIOR TO RUNNING THIS UTILITY" & Chr(10)
trustMess = trustMess & " You need to adjust your Macro Security
Settings as shown below:" & Chr(10) & Chr(10)
trustMess = trustMess & "Click on Excel Menu: Tools Macro
Security" & Chr(10)
trustMess = trustMess & "Then Click on 'Trusted Publishers' Tab" & Chr(10)
trustMess = trustMess & "'Check' the 2nd box: 'Trust Access to Visual
Basic Project'" & Chr(10)
trustMess = trustMess & "Then 'Save' this File and Re-Run the Utility."
& Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "By doing this, you are allowing this utility to
copy macros" & Chr(10)
trustMess = trustMess & "to the Newly created Workbook which are needed
for it to work." & Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "Most Macros don't need this feature to work,
but this one does." & Chr(10)
trustMess = trustMess & "After running this macro, you can always
'Uncheck' that box if you have security concerns." & Chr(10)
trustMe = MsgBox(trustMess, vbCritical)
End

End If

End Sub

"Trefor" wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

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).

--
Trefor


"Dave Peterson" wrote:

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 wrote:

MikeZz / Dave,

I wasn€„¢t actually having trouble trapping the error for the import into the
unprotected project. I did need to ensure the Project was Trusted and have
now added the following code which appears to work fine. I intend these this
flag set for me and my users, so this should be a once off check.

' Import temp file
WBTemp2.VBProject.VBComponents.Import FileName:=TempFile

If Err = 1004 Then
msg = "An error has occured most likely because an Excel security
setting is not correct." & Chr(10) & Chr(10)
msg = msg + "From 'Tools Macro Security' make sure 'Trust access
to Visual Basic Project' has been ticked" & Chr(10) & Chr(10)
msg = msg + "This operation will now abort, please retry after the
setting has been corrected." & Chr(10) & Chr(10)
MsgBox msg, vbExclamation
End If

My problem is on the export of the form a Trusted, but Protected Project.
With a Trusted and Protected Project the following does not cause an error,
but does not export the file either. My current work around is to pre-export
the Form and then Import in VBA as and when I need, but I was looking for a
more elegant solution.

' Export Form to Temp file
ThisWorkbook.VBProject.VBComponents("RSCForm").Exp ort FileName:=TempFile

--
Trefor

"MikeZz" wrote:

Hi Dave,
I forgot the other function in my haste. Here it is:

Private Function VBAIsTrusted() As Boolean
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function


"Dave Peterson" wrote:

You didn't post how VBAIsTrusted is determined.

In newer versions (xl2002+???), you could do something like:

Dim wkbk As Workbook
Set wkbk = Workbooks("someworkbooknamehere.xla")
If wkbk.VBProject.Protection = 0 Then
MsgBox wkbk.Name & "'s project is NOT protected"
End If



MikeZz wrote:

Trefor,

Here's a little routine I run prior to running any macros that require the
Trusted VBA Setting. Just checks to make sure the setting is correct before
even starting. In my case, the macro ran for sometimes 15 minutes before it
finally crashed so it's good to test setting first.

Private Sub Check_Trusted_VBA()
Dim trustMe, trustMess

trustMe = VBAIsTrusted

If trustMe = False Then
trustMess = "PRIOR TO RUNNING THIS UTILITY" & Chr(10)
trustMess = trustMess & " You need to adjust your Macro Security
Settings as shown below:" & Chr(10) & Chr(10)
trustMess = trustMess & "Click on Excel Menu: Tools Macro
Security" & Chr(10)
trustMess = trustMess & "Then Click on 'Trusted Publishers' Tab" & Chr(10)
trustMess = trustMess & "'Check' the 2nd box: 'Trust Access to Visual
Basic Project'" & Chr(10)
trustMess = trustMess & "Then 'Save' this File and Re-Run the Utility."
& Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "By doing this, you are allowing this utility to
copy macros" & Chr(10)
trustMess = trustMess & "to the Newly created Workbook which are needed
for it to work." & Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "Most Macros don't need this feature to work,
but this one does." & Chr(10)
trustMess = trustMess & "After running this macro, you can always
'Uncheck' that box if you have security concerns." & Chr(10)
trustMe = MsgBox(trustMess, vbCritical)
End

End If

End Sub

"Trefor" wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson

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

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


Trefor wrote:

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).

--
Trefor

"Dave Peterson" wrote:

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 wrote:

MikeZz / Dave,

I wasn€„¢t actually having trouble trapping the error for the import into the
unprotected project. I did need to ensure the Project was Trusted and have
now added the following code which appears to work fine. I intend these this
flag set for me and my users, so this should be a once off check.

' Import temp file
WBTemp2.VBProject.VBComponents.Import FileName:=TempFile

If Err = 1004 Then
msg = "An error has occured most likely because an Excel security
setting is not correct." & Chr(10) & Chr(10)
msg = msg + "From 'Tools Macro Security' make sure 'Trust access
to Visual Basic Project' has been ticked" & Chr(10) & Chr(10)
msg = msg + "This operation will now abort, please retry after the
setting has been corrected." & Chr(10) & Chr(10)
MsgBox msg, vbExclamation
End If

My problem is on the export of the form a Trusted, but Protected Project.
With a Trusted and Protected Project the following does not cause an error,
but does not export the file either. My current work around is to pre-export
the Form and then Import in VBA as and when I need, but I was looking for a
more elegant solution.

' Export Form to Temp file
ThisWorkbook.VBProject.VBComponents("RSCForm").Exp ort FileName:=TempFile

--
Trefor

"MikeZz" wrote:

Hi Dave,
I forgot the other function in my haste. Here it is:

Private Function VBAIsTrusted() As Boolean
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function


"Dave Peterson" wrote:

You didn't post how VBAIsTrusted is determined.

In newer versions (xl2002+???), you could do something like:

Dim wkbk As Workbook
Set wkbk = Workbooks("someworkbooknamehere.xla")
If wkbk.VBProject.Protection = 0 Then
MsgBox wkbk.Name & "'s project is NOT protected"
End If



MikeZz wrote:

Trefor,

Here's a little routine I run prior to running any macros that require the
Trusted VBA Setting. Just checks to make sure the setting is correct before
even starting. In my case, the macro ran for sometimes 15 minutes before it
finally crashed so it's good to test setting first.

Private Sub Check_Trusted_VBA()
Dim trustMe, trustMess

trustMe = VBAIsTrusted

If trustMe = False Then
trustMess = "PRIOR TO RUNNING THIS UTILITY" & Chr(10)
trustMess = trustMess & " You need to adjust your Macro Security
Settings as shown below:" & Chr(10) & Chr(10)
trustMess = trustMess & "Click on Excel Menu: Tools Macro
Security" & Chr(10)
trustMess = trustMess & "Then Click on 'Trusted Publishers' Tab" & Chr(10)
trustMess = trustMess & "'Check' the 2nd box: 'Trust Access to Visual
Basic Project'" & Chr(10)
trustMess = trustMess & "Then 'Save' this File and Re-Run the Utility."
& Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "By doing this, you are allowing this utility to
copy macros" & Chr(10)
trustMess = trustMess & "to the Newly created Workbook which are needed
for it to work." & Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "Most Macros don't need this feature to work,
but this one does." & Chr(10)
trustMess = trustMess & "After running this macro, you can always
'Uncheck' that box if you have security concerns." & Chr(10)
trustMe = MsgBox(trustMess, vbCritical)
End

End If

End Sub

"Trefor" wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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


--

Dave Peterson



--

Dave Peterson

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

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.

--
Trefor


"Dave Peterson" wrote:

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


Trefor wrote:

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).

--
Trefor

"Dave Peterson" wrote:

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 wrote:

MikeZz / Dave,

I wasn€„¢t actually having trouble trapping the error for the import into the
unprotected project. I did need to ensure the Project was Trusted and have
now added the following code which appears to work fine. I intend these this
flag set for me and my users, so this should be a once off check.

' Import temp file
WBTemp2.VBProject.VBComponents.Import FileName:=TempFile

If Err = 1004 Then
msg = "An error has occured most likely because an Excel security
setting is not correct." & Chr(10) & Chr(10)
msg = msg + "From 'Tools Macro Security' make sure 'Trust access
to Visual Basic Project' has been ticked" & Chr(10) & Chr(10)
msg = msg + "This operation will now abort, please retry after the
setting has been corrected." & Chr(10) & Chr(10)
MsgBox msg, vbExclamation
End If

My problem is on the export of the form a Trusted, but Protected Project.
With a Trusted and Protected Project the following does not cause an error,
but does not export the file either. My current work around is to pre-export
the Form and then Import in VBA as and when I need, but I was looking for a
more elegant solution.

' Export Form to Temp file
ThisWorkbook.VBProject.VBComponents("RSCForm").Exp ort FileName:=TempFile

--
Trefor

"MikeZz" wrote:

Hi Dave,
I forgot the other function in my haste. Here it is:

Private Function VBAIsTrusted() As Boolean
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function


"Dave Peterson" wrote:

You didn't post how VBAIsTrusted is determined.

In newer versions (xl2002+???), you could do something like:

Dim wkbk As Workbook
Set wkbk = Workbooks("someworkbooknamehere.xla")
If wkbk.VBProject.Protection = 0 Then
MsgBox wkbk.Name & "'s project is NOT protected"
End If



MikeZz wrote:

Trefor,

Here's a little routine I run prior to running any macros that require the
Trusted VBA Setting. Just checks to make sure the setting is correct before
even starting. In my case, the macro ran for sometimes 15 minutes before it
finally crashed so it's good to test setting first.

Private Sub Check_Trusted_VBA()
Dim trustMe, trustMess

trustMe = VBAIsTrusted

If trustMe = False Then
trustMess = "PRIOR TO RUNNING THIS UTILITY" & Chr(10)
trustMess = trustMess & " You need to adjust your Macro Security
Settings as shown below:" & Chr(10) & Chr(10)
trustMess = trustMess & "Click on Excel Menu: Tools Macro
Security" & Chr(10)
trustMess = trustMess & "Then Click on 'Trusted Publishers' Tab" & Chr(10)
trustMess = trustMess & "'Check' the 2nd box: 'Trust Access to Visual
Basic Project'" & Chr(10)
trustMess = trustMess & "Then 'Save' this File and Re-Run the Utility."
& Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "By doing this, you are allowing this utility to
copy macros" & Chr(10)
trustMess = trustMess & "to the Newly created Workbook which are needed
for it to work." & Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "Most Macros don't need this feature to work,
but this one does." & Chr(10)
trustMess = trustMess & "After running this macro, you can always
'Uncheck' that box if you have security concerns." & Chr(10)
trustMe = MsgBox(trustMess, vbCritical)
End

End If

End Sub

"Trefor" wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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

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

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.

Trefor wrote:

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.

--
Trefor

"Dave Peterson" wrote:

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


Trefor wrote:

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).

--
Trefor

"Dave Peterson" wrote:

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 wrote:

MikeZz / Dave,

I wasn€„¢t actually having trouble trapping the error for the import into the
unprotected project. I did need to ensure the Project was Trusted and have
now added the following code which appears to work fine. I intend these this
flag set for me and my users, so this should be a once off check.

' Import temp file
WBTemp2.VBProject.VBComponents.Import FileName:=TempFile

If Err = 1004 Then
msg = "An error has occured most likely because an Excel security
setting is not correct." & Chr(10) & Chr(10)
msg = msg + "From 'Tools Macro Security' make sure 'Trust access
to Visual Basic Project' has been ticked" & Chr(10) & Chr(10)
msg = msg + "This operation will now abort, please retry after the
setting has been corrected." & Chr(10) & Chr(10)
MsgBox msg, vbExclamation
End If

My problem is on the export of the form a Trusted, but Protected Project.
With a Trusted and Protected Project the following does not cause an error,
but does not export the file either. My current work around is to pre-export
the Form and then Import in VBA as and when I need, but I was looking for a
more elegant solution.

' Export Form to Temp file
ThisWorkbook.VBProject.VBComponents("RSCForm").Exp ort FileName:=TempFile

--
Trefor

"MikeZz" wrote:

Hi Dave,
I forgot the other function in my haste. Here it is:

Private Function VBAIsTrusted() As Boolean
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function


"Dave Peterson" wrote:

You didn't post how VBAIsTrusted is determined.

In newer versions (xl2002+???), you could do something like:

Dim wkbk As Workbook
Set wkbk = Workbooks("someworkbooknamehere.xla")
If wkbk.VBProject.Protection = 0 Then
MsgBox wkbk.Name & "'s project is NOT protected"
End If



MikeZz wrote:

Trefor,

Here's a little routine I run prior to running any macros that require the
Trusted VBA Setting. Just checks to make sure the setting is correct before
even starting. In my case, the macro ran for sometimes 15 minutes before it
finally crashed so it's good to test setting first.

Private Sub Check_Trusted_VBA()
Dim trustMe, trustMess

trustMe = VBAIsTrusted

If trustMe = False Then
trustMess = "PRIOR TO RUNNING THIS UTILITY" & Chr(10)
trustMess = trustMess & " You need to adjust your Macro Security
Settings as shown below:" & Chr(10) & Chr(10)
trustMess = trustMess & "Click on Excel Menu: Tools Macro
Security" & Chr(10)
trustMess = trustMess & "Then Click on 'Trusted Publishers' Tab" & Chr(10)
trustMess = trustMess & "'Check' the 2nd box: 'Trust Access to Visual
Basic Project'" & Chr(10)
trustMess = trustMess & "Then 'Save' this File and Re-Run the Utility."
& Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "By doing this, you are allowing this utility to
copy macros" & Chr(10)
trustMess = trustMess & "to the Newly created Workbook which are needed
for it to work." & Chr(10)
trustMess = trustMess & Chr(10)
trustMess = trustMess & "Most Macros don't need this feature to work,
but this one does." & Chr(10)
trustMess = trustMess & "After running this macro, you can always
'Uncheck' that box if you have security concerns." & Chr(10)
trustMe = MsgBox(trustMess, vbCritical)
End

End If

End Sub

"Trefor" wrote:

Dave,

Is there a way to do this without getting an error and to sucessfully copy a
Form from a password protected project to a new workbook?

--
Trefor


"Dave Peterson" wrote:

Everytime I tried it, I got that error and the export failed. I have no idea
why you didn't get the error when it failed for you.

Trefor wrote:

Dave,

Yes, the VBE project. I put an On error Goto 0 before the line I mentioned
and I don't et an error. Either way is it possible to copy a Userform from a
protected project?

--
Trefor

"Dave Peterson" wrote:

You mean that the project is protected in the VBE (Tools|VBAProject
Properties|Protection tab), right?

When that project is protected, I got an error:
Run-time error '50280':
Can't perform operation since the project is protected.

I'm guessing that you have some error handling in your procedure ("On error
resume next"???) that's masking the error from you if you don't see it.

==========

Some alternatives.

#1. Create a template file that you use. This will have all the code you ever
need in it--including the userforms and all the events (worksheet, workbook,
....).

Then if you're creating a workbook and copying code, you can start with that
workbook template and just add worksheets (or copy|paste the data).

#2. Export the forms manually and save them in a nice spot. Then drop the
exporting and just import from these files you previously saved.

#3. Drop the workbook's project's protection completely.

Trefor wrote:

Dave,

OK, must gremlins ;)

Last question I hope. I am working with a password protected AddIn and I am
trying to copy a sheet (which contains some macro's) and a Form to a new
workbook that is not going to have any protection. I can copy the sheet with
my macro's no problem and now that I have tick the security option you
pointed out the Form copy part works fine in test with my AddIn unprotected.
If I protect the AddIn in the Form copy runs through without error, but it
doesn't actually Export for the File.

This is the line in question:

ThisWorkbook.VBProject.VBComponents("MyForm").Expo rt FileName:=TempFile

Does this mean I can't export a Form from a write protected AddIn or is
there another way of performing the copy?

--
Trefor

"Dave Peterson" wrote:

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

--

Dave Peterson

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

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!

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

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

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

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

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

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?

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

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