Copy a userform to a different workbook with VBA
 

Hi there,

Does anybody know how I can copy a userform I've created in a workbook
to another workbook using VBA? As the user will be able to select the
target workbook, I need to automate this userform export with VBA.

Any ideas? Many thanks for your help!

Kuhni

Copy a userform to a different workbook with VBA
 

Kuhni,

Here is an example that exports it the re-imports it

Workbooks("Book1").VBProject.VBComponents("Userfor m1").Export _
Filename:="C:\temp.frm"
Workbooks("Book3").VBProject.VBComponents.Import _
Filename:="C:\temp.frm"
Kill "C:\temp.frm"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"kuhni" wrote in message
oups.com...
Hi there,

Does anybody know how I can copy a userform I've created in a workbook
to another workbook using VBA? As the user will be able to select the
target workbook, I need to automate this userform export with VBA.

Any ideas? Many thanks for your help!

Kuhni

Copy a userform to a different workbook with VBA
 

Bob,

Many thanks - that seems to be a very nice and easy solution for my
problem. However, I get an runtime error 1004 when adjusting your
sample code to my workbook. Are there any references needed (besides
Microsoft Visual Basic for Applications Extensibility 5.3 and the very
standard references)?

Again many thanks,
Kuhni

Copy a userform to a different workbook with VBA
 

Kuhni,

Let's see your code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"kuhni" wrote in message
ups.com...
Bob,

Many thanks - that seems to be a very nice and easy solution for my
problem. However, I get an runtime error 1004 when adjusting your
sample code to my workbook. Are there any references needed (besides
Microsoft Visual Basic for Applications Extensibility 5.3 and the very
standard references)?

Again many thanks,
Kuhni

Copy a userform to a different workbook with VBA
 

Bob,

Sure - here it is, but it is nearly identical to your code.

1 Sub copyForm()
2 Dim targetFile As String

3 targetFile = "EK-Prices"

4 ThisWorkbook.VBProject.VBComponents("FImport").Exp ort
Filename:="C:\temp.frm"
5 Workbooks(targetFile).VBProject.VBComponents.Impor t
Filename:="C:\temp.frm"
6 Kill "C:\temp.frm"
7 End Sub

The error occurs in line 4: Runtime Error 1004.

Thanks,
Kuhni

Copy a userform to a different workbook with VBA
 

Kuhni,

Just tried your code, works fine.

I don't think this is the problem as you gave line numbers, but did you put

ThisWorkbook.VBProject.VBComponents("FImport").Exp ort
Filename:="C:\temp.frm"

all on one line? Ditto 5?

Also, you should assign targetFile = "EK-Prices.xls". I didn't use that as I
had not yet saved them.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"kuhni" wrote in message
ups.com...
Bob,

Sure - here it is, but it is nearly identical to your code.

1 Sub copyForm()
2 Dim targetFile As String

3 targetFile = "EK-Prices"

4 ThisWorkbook.VBProject.VBComponents("FImport").Exp ort
Filename:="C:\temp.frm"
5 Workbooks(targetFile).VBProject.VBComponents.Impor t
Filename:="C:\temp.frm"
6 Kill "C:\temp.frm"
7 End Sub

The error occurs in line 4: Runtime Error 1004.

Thanks,
Kuhni