Copy a userform to a different workbook with VBA

[kuhni]

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

 

[Bob Phillips]

Kuhni,

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

Workbooks("Book1").VBProject.VBComponents("Userform1").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]

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

 

[Bob Phillips]

Kuhni,

Let's see your code.

--

HTH

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

 

[kuhni]

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").Export
Filename:="C:\temp.frm"
5 Workbooks(targetFile).VBProject.VBComponents.Import
Filename:="C:\temp.frm"
6 Kill "C:\temp.frm"
7 End Sub

The error occurs in line 4: Runtime Error 1004.

Thanks,
Kuhni

 

[Bob Phillips]

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