none
How to copy a userform from one workbook to another using VBA

    Question

  • Hi Folks,

    Suppose I have a userform in SourceWB (an macro enabled Excel file) and I want to copy it to DestinationWB (another macro enabled Excel file) using VBA.

    I need to copy the whole form, not just its code.  I can use something like the following to copy the form's code, but I need the form itself:

        SourceWB.VBProject.VBComponents("frmChangeFDOB").Export "frmChangeFDOB.txt"
        Y = DestinationWB.VBProject.VBComponents("frmChangeFDOB").CodeModule.CountOfLines
        DestinationWB.VBProject.VBComponents("frmChangeFDOB").CodeModule.DeleteLines 1, Y
        DestinationWB.VBProject.VBComponents("frmChangeFDOB").CodeModule.AddFromFile "frmChangeFDOB.txt"
        DestinationWB.VBProject.VBComponents("frmChangeFDOB").CodeModule.DeleteLines 1, 10
        Kill "frmChangeFDOB.txt"
        Kill "frmChangeFDOB.frx"

    TIA,

    Shane


    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

    Friday, February 24, 2012 10:17 PM

Answers

  • You can use Import:

        SourceWB.VBProject.VBComponents("frmChangeFDOB").Export "frmChangeFDOB.frm"
        DestinationWB.VBProject.VBComponents.Import "frmChangeFDOB.frm"
        Kill "frmChangeFDOB.frm"
        Kill "frmChangeFDOB.frx"


    Regards, Hans Vogelaar

    Friday, February 24, 2012 10:35 PM

All replies

  • You can use Import:

        SourceWB.VBProject.VBComponents("frmChangeFDOB").Export "frmChangeFDOB.frm"
        DestinationWB.VBProject.VBComponents.Import "frmChangeFDOB.frm"
        Kill "frmChangeFDOB.frm"
        Kill "frmChangeFDOB.frx"


    Regards, Hans Vogelaar

    Friday, February 24, 2012 10:35 PM
  • So simple, but hard to track down in the online help.

    Much appreciated,

    Shane


    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

    Friday, February 24, 2012 11:58 PM
  • Hi Hans. Sorry to barge in on this thread, but where do you put that code to copy the form to another WB? Thanks.
    Wednesday, September 19, 2012 12:35 AM
  • Hi Hans. Sorry to barge in on this thread, but where do you put that code to copy the form to another WB? Thanks.

    The code can be in the source workbook, the destination workbook or another workbook just containing the export and import code just so long as you set the variables to the correct source and destination workbooks.

    All workbooks should be open.

    The following is coded to  have the code in a standard module. ThisWorkbook can then be used to set SourceWB.

    Sub ExportImportForm()
       
        Dim SourceWb As Workbook
        Dim DestinationWB As Workbook
       
        Set SourceWb = ThisWorkbook       'Workbook with existing form
        Set DestinationWB = Workbooks("Workbook to receive form.xlsm")
       
        SourceWb.VBProject.VBComponents("frmChangeFDOB").Export "frmChangeFDOB.frm"
        DestinationWB.VBProject.VBComponents.Import "frmChangeFDOB.frm"
        Kill "frmChangeFDOB.frm"
        Kill "frmChangeFDOB.frx"
       
    End Sub


    Regards, OssieMac

    Wednesday, September 19, 2012 6:03 AM