Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts

    Create a form from another worksheet

    This question is for Maudibe related to the thread:

    http://windowssecrets.com/forums/sho...other-workbook

    Is it possible to use a macro to create a form in a workbook from another? I have either one of two different types of workboorks automatically created depending on a value from my main workbook. Each workbook has a different form that it uses. This requires me to manually upload a useform file. Is there a way to programmatically build the form as needed?

    Thanks for any help you can give me.
    Alexandra

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Alexandra,

    There are 2 ways you can do this:

    1. Create the form using VBA (not preferable).
    Like the post you referred to, you will need to reference the Microsoft Visual Basic for Applications Extensibility 5.3 library. The following lines of code will create a blank User form which you will then need to manually build all the objects and code which would defeat the purpose or continue to build its elements with code. That is doable but very time consuming and you said you have more than one. Using code, every object would need to be added plus every line of code as well.

    Code:
    Public Sub SendForm()
    Dim module As VBComponent
    Set module = Workbooks("Target1.xlsx").VBProject.VBComponents.Add(vbext_ct_MSForm)
    module.Name = "TestForm"
    End Sub
    Userform1.png Userform2.png

    2. Using VBA, upload a previously exported forms (preferable).
    Even though you would still need to reference the Microsoft Visual Basic for Applications Extensibility 5.3 library, the forms, objects, and code are already built. You have stated that you are already doing this manually so you already have what you need. Here is the code to do that:

    Code:
    Public Sub ImportForm()
    Workbooks("Target1.xlsx").VBProject.VBComponents.Import ("C:\Users\Maudibe\Documents\Userform1.frm")
    End Sub
    Userforem3.png

    Create a conditional statement based on the criteria in your main workbook the loads one user form or another.

    Question: Is there a reason why you do not have both forms loaded on a second workbook or template?

    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2013-12-16)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You may want to look into creating an ADD-IN (MS MVP Jan Karel Pieterse has an article about this at http://www.jkp-ads.com/Articles/DistributeMacro00.asp.

    This file can be distributed and updated as needed.

    Steve

  5. #4
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Maud,

    I just want to say "Brilliant"!!! I imported the correct userform to the created workbook using a condition statement as you suggested. There is was, ready to open, and it functioned as it would if I manually uploaded it. I cannot thank you enough.

    A

  6. #5
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Maudibe,

    Sorry I missed your question, but the new workbook is created by a macro in my main work. It is not an existing workbook that I can add the userforms to before hand. The code has been in used during the day today and it worked perfectly. This saves me alone at least a half hour. I recorded and modified a macro that I added at the end of my macro, which includes your code, to automatically save it as an XLSM file. The process is now totally automated.

    I greatly appreciate all the help you have given me especially with the code for the upload to create my XML files a few months back. BTW, still working well.

    Code:
        ActiveWorkbook.SaveAs Filename:="C:\Users\Alexandra\Documents\XML\" & Filename & ".xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Steve,

    I meant to say thank you as well for the link. I am like a sponge when I visit this forum.

    Best to all,
    A.

Posting Permissions

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