Solved: How can I copy a UserForm (and Module)?

[Paul_Hossler]

Is there some way to make a copy of a UserForm or a Module within the project?

I've seen some clever code in here that manipuldates the VBE(?), so I'm hoping it's possible.

Lot of times I have a UserForm all arranged (picture, buttons, frames, etc.) and I'd like to copy it to maintain the same style (template) for the rest of the UserForms.

If it were just changing the label text or captions, etc. I could easily change them in VBA, but sometimes I need some more commandbuttons, or an EditBox and it's easier to just have another UserFOrm with the same basic layout.

Same for a Module.

Here's hoping: pray2:


Paul

[mdmackillop]

Right click and select Export. You can then Import again as a copy. You may need to rename the original to avoid name conflicts.

[lucas]

One way is to make a template of all of the controls on the controls toolbox.

select all of the controls on the userform and drag them to the control toolbox. Then all you have to do is insert a new userform, make it the same size as the original and drag the controls from the toolbox all at one time.....check alignment on the upper left one and you got it.

From the help file:


Note Dragging a control (or a number of "grouped" controls) from the form back to the Toolbox creates a template of that control, which can be reused. This is a useful feature for implementing a standard interface for your applications.


You can even right click on the tab of the toolbox and add sheets or tabs to hold your templates......

I don't know how to automate it though.

[lucas]

Malcolms method will keep the code with the controls....I thought you were looking for an alternative to that.

[Paul_Hossler]

Maclom --

I try that, but I was wondering if there was a way to manipulate the VBE directly

Other thing seems to be that you have to rename the original before you import the exported copy back in.

Thanks

Lucas -- I did want to keep the code with the copy of the controls, but I'll have to experiment with that template approach. That's a good tip

Thanks

Paul

[Bob Phillips]

Const vbext_ct_StdModule As Long = 1
Const vbext_ct_ClassModule As Long = 2
Const vbext_ct_MSForm As Long = 3

'----------------------------------------------------------------
Sub CopyModule(ByVal modName As String)
'----------------------------------------------------------------
Dim VBMod As Object
Dim SavedName As String
Dim i As Long

With ActiveWorkbook.VBProject

Set VBMod = .VBComponents(modName)

Select Case VBMod.Type

Case vbext_ct_StdModule:

VBMod.Export "C:\" & VBMod.Name & ".bas"
.VBComponents.Import "C:\" & VBMod.Name & ".bas"

Case vbext_ct_MSForm:

SavedName = "C:\" & VBMod.Name & ".frm"
VBMod.Export SavedName
VBMod.Name = "OldForm"
.VBComponents.Import SavedName
Case vbext_ct_ClassModule:
End Select
End With
End Sub

Sub TestIt()

CopyModule "Userform1"
CopyModule "Module1"
End Sub

[Paul_Hossler]

Thanks everyone -- all good ideas

I learned enougth here to do some better Google-ing and found some good information on Chip's page if any else is interested

http://www.cpearson.com/excel/vbe.aspx

Paul