Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Userform to another workbook (2002)

    Good Evening,
    How is the easiet way to copy a userform from one workbook to another?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Copy Userform to another workbook (2002)

    Open both workbooks.
    Activate the Visual Basic Editor.
    Click on the userform in the Project Explorer on the left hand side and drag it to the other workbook.
    When you release the mouse pointer, the userform will be copied to the other workbook, including the code belonging to the userform.

  4. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Userform to another workbook (2002)

    Thanks, I was trying to import / export, not considering the obvious.

    What about if I want to pass a macro or useform to somebody else. At the moment, for example, I end up copying the code from the VBE and saving as a text file, sending by email and having them paste into their personal VBE. Is there an easier way?

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Copy Userform to another workbook (2002)

    You can right-click a userform in the Project Explorer and select Export File... from the popup menu (or select File | Export File...)
    You'll get prompted to save as a .frm file, but you'll actually create two separate files: a .frm file (a plain text file containing a description of the form and its controls and the code), and a .frx file (a binary file containing aspects of the form that can't be captured in the .frm file).
    You can send the .frm and .frx files to another user.
    The user can select File | Import File... in the Visual Basic Editor and select the .frm file. This will import the useform.

    Warning: one can't have two userforms with the same name in a workbook. So if the user already has an older version of the same userform, this must be removed before importing the new version.

  6. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Userform to another workbook (2002)

    Thanks, I presume the same can be done with a macro, but the file extention would be different?
    I know these files live within either the workbook or personal, so they are'nt saved externally???
    But then I could save all my individual components (macro's/userforms etc) separately on my private drive for both easy distribution and back-up? In theory?

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Copy Userform to another workbook (2002)

    A standard module can be exported to a .bas file, and a class module to a .cls file (both can imported into another workbook).

    But although you can export the ThisWorkbook module and the worksheet modules to .cls files too (they are a kind of class module), you cannot import ThisWorkbook or a worksheet module because these modules already exist. Attempting to import them results in separate class modules. So you need to copy/paste the code.

  8. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Userform to another workbook (2002)

    So if I wanted to save macro's separately, I would need them to be in individual modules and then re-name the modules before export?

  9. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Copy Userform to another workbook (2002)

    The idea of modules is organize related macros in a single module. If you want to save a single macro you might as well copy and paste it into a text file (.txt).

  10. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Userform to another workbook (2002)

    Ok Thanks.

  11. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,873
    Thanks
    0
    Thanked 79 Times in 75 Posts

    Re: Copy Userform to another workbook (2002)

    If you are looking to distribute a bunch of related code and userforms, you could copy them into a new workbook and save it as an add-in, which you can then distribute freely.
    Regards,
    Rory
    Microsoft MVP - Excel.

  12. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Userform to another workbook (2002)

    It might be easier to drag the VBA components you want to distribute to an empty workbook and send them that workbook?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Userform to another workbook (2002)

    Great minds...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,873
    Thanks
    0
    Thanked 79 Times in 75 Posts

    Re: Copy Userform to another workbook (2002)

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15> The MVP hive mind!
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •