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

[Guest]

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!

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Norman Jones]

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

 

[Guest]

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.

 

[Norman Jones]

Hi Rick,

Did the code which I suggested not work for you?

 

[Guest]

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!

 

[Trefor]

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.

 

[Dave Peterson]

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]

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?

 

[Dave Peterson]

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

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

 

[Trefor]

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").Export 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?

 

[Dave Peterson]

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.

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").Export 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,

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?

 

[Dave Peterson]

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.

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,

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?

 

[Dave Peterson]

Not that I can do.

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?

 

[MikeZz]

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

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?

 

[Dave Peterson]

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]

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

 

[Trefor]

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").Export FileName:=TempFile

--
Trefor

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