none
Copy a User Form RRS feed

  • Question

  • I have designed a user form for use in a procedure.  I now want a second user form that will be almost identical, with just some textual changes.  Is there a way to copy the user form and insert it into the same procedure?  I have tried exporting it and re-importing it, but it won't let me do that because the form already exists.  I tried sneakily renaming the .frm file that I had exported and then trying to import it, but again it refused.  I can copy the individual elements of the form, but the positional information still has to be done manually.

    Clearly I could simply open a new form and copy all the positional and size etc data from the original, and for all the command buttons and labels, but that is very laborious.  Surely there must be a quicker way!

    Any advice gratefully received.

    Saturday, March 2, 2013 6:18 PM

Answers

  • The fastest way:

    • Open new Excel file
    • Drug your form to new project tree (to make copy)
    • Dbl click and change in form properties (name) [Ctrl+R]
    • Drug form to previous file (to have both forms)
    • Kill temporary/2nd file


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by AndyColRomsey Sunday, March 3, 2013 12:23 PM
    Sunday, March 3, 2013 11:12 AM
    Answerer
  • You don't need to duplicate the form in the same project. Just call it again with a different set of values. e.g. Imagine (at its simplest) a userform with a textbox, a label and a command button. The command button has the code

    me.hide

    The following will cause the userform to be displayed twice with a different set of values

    Sub UseFormTwice()
    Dim strValue1 As String
    Dim strValue2 As String
    With UserForm1
        .Label1.Caption = "This is the first label"
        .Caption = "This is the title of form"
        .CommandButton1.Caption = "Apply First Value"
        .Show
        strValue1 = .TextBox1.Text
    End With
    Unload UserForm1
    With UserForm1
        .Label1.Caption = "This is the second label"
        .Caption = "This is the title of new form"
        .CommandButton1.Caption = "Apply Next Value"
        .Show
        strValue2 = .TextBox1.Text
    End With
    Unload UserForm1
    MsgBox strValue1 & vbCr & strValue2
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by AndyColRomsey Saturday, March 16, 2013 9:16 AM
    Sunday, March 3, 2013 3:42 PM
  • Andy,

    I did this sometime back.

    Use a text file editor link notepad++ and open the frm file and I think change name after the 'Attribute VB_Name =' and save the file.  You should be able to import the file.

    HTH

    Harry

    • Marked as answer by AndyColRomsey Sunday, March 3, 2013 11:06 AM
    Saturday, March 2, 2013 8:44 PM

All replies

  • Andy,

    I did this sometime back.

    Use a text file editor link notepad++ and open the frm file and I think change name after the 'Attribute VB_Name =' and save the file.  You should be able to import the file.

    HTH

    Harry

    • Marked as answer by AndyColRomsey Sunday, March 3, 2013 11:06 AM
    Saturday, March 2, 2013 8:44 PM
  • Why not simply use the same form and change the text in code according to what it is you wish to achieve?

    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by AndyColRomsey Sunday, March 3, 2013 11:06 AM
    • Unmarked as answer by AndyColRomsey Sunday, March 3, 2013 11:06 AM
    Sunday, March 3, 2013 10:45 AM
  • Harry

    Thanks for that idea.  I had also to change the reference to the original user form in line 1 (Begin {...} ), but it worked.  Still a bit of a faff, but easier than duplicating all the parameters in a new form.

    VMT

    Andy

    Sunday, March 3, 2013 11:06 AM
  • Graham

    Thanks for looking at this.  That is effectively what I am trying to do.  But how do I duplicate the original form (as I want to keep it too)?  There is no facility to copy the user form, and I can't find a "Save As" option for the form alone.

    Andy

    Sunday, March 3, 2013 11:11 AM
  • The fastest way:

    • Open new Excel file
    • Drug your form to new project tree (to make copy)
    • Dbl click and change in form properties (name) [Ctrl+R]
    • Drug form to previous file (to have both forms)
    • Kill temporary/2nd file


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by AndyColRomsey Sunday, March 3, 2013 12:23 PM
    Sunday, March 3, 2013 11:12 AM
    Answerer
  • Oskar

    Very neat!  That should solve the problem nicely.

    Perhaps something for MS to address in Office 2015?

    Many thanks

    Andy

    Sunday, March 3, 2013 12:23 PM
  • You don't need to duplicate the form in the same project. Just call it again with a different set of values. e.g. Imagine (at its simplest) a userform with a textbox, a label and a command button. The command button has the code

    me.hide

    The following will cause the userform to be displayed twice with a different set of values

    Sub UseFormTwice()
    Dim strValue1 As String
    Dim strValue2 As String
    With UserForm1
        .Label1.Caption = "This is the first label"
        .Caption = "This is the title of form"
        .CommandButton1.Caption = "Apply First Value"
        .Show
        strValue1 = .TextBox1.Text
    End With
    Unload UserForm1
    With UserForm1
        .Label1.Caption = "This is the second label"
        .Caption = "This is the title of new form"
        .CommandButton1.Caption = "Apply Next Value"
        .Show
        strValue2 = .TextBox1.Text
    End With
    Unload UserForm1
    MsgBox strValue1 & vbCr & strValue2
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by AndyColRomsey Saturday, March 16, 2013 9:16 AM
    Sunday, March 3, 2013 3:42 PM
  • Graham

    Sorry, I have been a long time getting back to you on this reply.  I had not thought of applying the answer in this way, but it works just fine.  Two operations for the price of one (and a bit)!

    Thanks

    Andy

    Saturday, March 16, 2013 9:16 AM