Saving a UserForm that is dynamically created?

[djr8yk]

I asked this question yesterday, but I don't think I was very clear about exactly what I'm doing. I have a UserForm that, upon initialization, will prompt the user with a series of questions. It will then update itself with controls accordingly. An example would be:

UserForm, before questions (note it is blank):

Questions:
How many frames do you want? (1)
What do you want to name frame 1? ("First")
How many checkboxes do you want in "First"? (2)
etc.

Updated UserForm:

My goal is to save the updated UserForm in a separate file, so that I can access it later, without changing the original one with the prompts. So, I want to use this prompt to make a lot of different forms, but once I answer the questions and create the forms, I want to be able to access them again without having to answer the questions.

Any help is appreciated! Let me know if I'm unclear about anything.
Thanks!

 

[VBA Geek]

if you want to add permanent controls to your userform then you need to do it differently than the way you are doing it.

you need to a reference to Microsoft Visual Basic for Applications Extensibility

and add controls like this:

Sub AddControlsToForm()
Const UserFormName As String = "UserForm1"
Const ExportPath As String = "C:\Documents\myForm.frm"

Dim UForm As MSForms.UserForm

' point to your userform
Set UForm = ThisWorkbook.VBProject.VBComponents(UserFormName).Designer

' add bunch of controls
With UForm
    .Controls.Add "Forms.CommandButton.1", "MyCommandButton", True
End With

' save the userform in your path
ThisWorkbook.VBProject.VBComponents(UserFormName).Export Filename:=ExportPath

End Sub

 

[djr8yk]

Thank you so much. I *think* I can make this work with the way I was doing it, just a matter of moving some lines around. This is a pretty dumb question, but I'm brand new to VBA. Where do I place the code like that? Do I put it in the UserForm code itself?

 

[djr8yk]

Hmm actually, what I'm doing is clicking a commandbutton from the spreadsheet and then prompting the user with questions. Should I just call this function in the Click method for that command button? That would avoid having to make another UserForm, right?

 

[VBA Geek]

you can place it in a standard module

the userform on which you will add the controls must not be loaded. After clicking the command button you have to store this data somewhere in my code, unload the userform and add the controls and export

 

[RoryA]

Why don't you just store the data in the other workbook and recreate the form as necessary based on the data (much as you do now)?

 

[djr8yk]

So I checked off the extensibility reference, but when I try to run the code I get the following error:

Run-time error '1004':
Programmatic access to Visual Basic Project is not trusted

and it highlights the line "Set UForm = ThisWorkbook.VBProject.VBComponents(UserFormname).Designer

Any idea how to alleviate this?
Thanks again

 

[VBA Geek]

http://www.mrexcel.com/forum/excel-questions/663848-access-vbulletin-project-not-trusted.html

 

[djr8yk]

RoryA
I had somebody else tell me to approach it this way as well. Do you really think it would be much easier? I suppose I may have to if I can't figure out how to save the form properly, but if I can save the form, as VBA Geek is helping me with, are there any disadvantages to that? I guess I haven't been using this language long enough to know the best approach to certain problems.

 

[djr8yk]

Thanks, sorry I should have looked that error up first. I'm now allowed access, but it's telling me I have a subscript out of range... it highlights the same line. I commented out the two lines pertaining to export (I don't know what my path is, I have to figure that out still).

Sub AddControlsToForm()
    
    Const UserFormname As String = "UserForm1"
    'Const ExportPath As String = "C:\Documents\myForm.frm"
    
    Dim UForm As MSForms.UserForm
    
    Set UForm = ThisWorkbook.VBProject.VBComponents(UserFormname).Designer
    
    With UForm
        .Controls.Add "Forms.CommandButton.1", "MyCommandButton", True
    End With
    
    'ThisWorkbook.VBProject.VBComponents(UserFormname).Export Filename:=ExportPath

End Sub