Solved: Copying UserForm between project files

[feathers212]

Is there a way to have a single UserForm copied from one project into another using VBA coding?

Thanks,
Heather

[Bob Phillips]

Workbooks("Book2").VBProject.VBComponents("UserForm1").Export _
Filename:="C:\temp\UserForm1.frm"

ActiveWorkbook.VBProject.VBComponents.Import _
Filename:="C:\temp\UserForm1.frm"

Kill "C:\temp\UserForm1.frm"

[feathers212]

xld,

Tried out the code, but when I run it I get a "Run-time error '9': Subscript Out of Range" It seems to be hitting this on the first line of the code (on the export).

I'm coding in 2003, but need compatability w/ 97 and 2000

[lucas]

Is the name of the workbook book2
is the name of the userform userform1

[feathers212]

No, I did change those to reflect my forms/worksheets. Here is my code:


Private Sub Submit_Ticket_Click()
Dim oApp As Object
Dim oMail As Object
Dim WB As Workbook
Dim FileName As String
'Print Preview of Ticket
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True
'Email Ticket
'Turn off screen updating
Application.ScreenUpdating = False
'Export Copy of Verification Form
Workbooks("Electronic Pull Ticket").VBProject.VBComponents("Verified_By"). _
Export FileName:="C:\Verified_By.frm"
'Make a copy of the active sheet and save it to a temporary file
Sheets("Pull Ticket").Copy
With ActiveSheet
.Unprotect
.Shapes("Submit_Ticket").Delete
.Protect
.Supervisor_Verification.Enabled = True
End With
'Import Copy of Verification Form
ActiveWorkbook.VBProject.VBComponents.Import FileName:="C:\Verified_By.frm"
Kill "C:\Verified_By.frm"
Set WB = ActiveWorkbook
FileName = "Pressroom Pulled Job.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = "heather.wold@bpc.com"
.Subject = "Verify Pressroom Pulled Job Counts"
.Attachments.Add WB.FullName
'.Importance = olImportanceHigh
.Display
End With

'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub

Edit: Line breaks added to the code for easier reading

Basically, I am copying a specific sheet ("Pull Ticket") from the "Electronic Pull Ticket" file. This single sheet file (called "Pressroom Pulled Job") is being sent as an email attachment. I need the "Verified_By" form to go along in the attached file as it is used in another step outside of this form.

[lucas]

Sub a()
Workbooks("Test.xls").VBProject.VBComponents("UserForm1").Export _
Filename:="F:\temp\UserForm1.frm"
End Sub

you have to call it out as a filename.xls test.xls in my sample

[feathers212]

Now I'm getting a "Run-Time Error '1004': Programmatic access to Visual Basic Project is not trusted" still on that export code line:

Workbooks("Electronic Pull Ticket.xls").VBProject.VBComponents("Verified_By").Export _
FileName:="C:\Verified_By.frm"

[lucas]

Menu item:
Tools-Macro-Securtity
select the Trusted publishers Tab
Check the box at the bottom that says:
Trust access to visual basic project

[Bob Phillips]

I didn't add the .xls because I was working on new workbooks.

[feathers212]

Worked perfectly with the security changes. I'm assuming that I would have to have that option checked on any and all computers that this file is going to be used on. Is there any possible way that this could be incorporated into the code?

Thanks again for helping me through the main problem!:)

[Bob Phillips]

No, of course not, otherwise there would be no point in having it.

[lucas]

Well.....Bob is right but you might read through this thread
http://www.vbaexpress.com/forum/showthread.php?t=10640&highlight=Trust

[feathers212]

Hmmm, yea, makes a lot of sense that you shouldn't go around disabling security features. Didn't think about that point at first...oops!

Thanks again to both of you for helping me wrap up on this coding. Now hopefully the end users don't want me to go changing everything again!:doh:

[lucas]

Be sure to mark your thread solved using the thread tools at the top of the page. If you have more questions later you can still post to this thread