VBA - How to Save Active UserForm with Dynamically Created Controls

Export it to some location that you'll remember and import it when you need it.

Hi Martin,

My problem is I don't seem to be able to get the handle or syntax right to save the ActiveForm.  What I end up exporting it the original Userform before the dynamic controls are added.  I want to export the Userform AFTER the I've dynamically added controls controls to it.

Currently, within the form code (e.g. within the command button's click code) I can correctly reference the active form, after it has received the dynamically added controls as follows:

Me.Name '(gives me the correct name for the form with the dynamically added contents)
Me.Controls.Count '(gives me the correct number of dynamically added controls

But:

ThisWorkbook.VBProject.VBComponents(Me.Name).Export exportFormName ' exports the original form without the dynamically added controls

Illustrated:
1.  I start with this Userform in PERSONAL.XLSB:

2.  I run code attached to the command button's Click event to dynamically create new form controls to the above userform, as so:

3.  Within the same command button's Click event code, I verify that:
a) the form's name I'm about to export is correct (see ?Me.Name in Immediate Window below)
b) the form's I'm about to export contains the new dynamically added form controls (see ?Me.Controls.Count = 17 in Immediate Window below; the original userform only had 1 control, so the form I'm exporting: Me.Name should have 17 controls in it
c) I export the form to my Desktop (see the "ThisWorkbook. ... line highlighed in red in the image below)
d) I create a new workbook, in this case:  Book4.xlsx into which I will import the exported Userform

4.  I then import Userform:  Me.Name which had Me.Controls.Count = 17 controls in it, but the form which gets imported into Book4.xlsx ends up being the original Userform I started with, devoid of any of the dynamically created controls seen in Step 2 above:

So it seems that what I'm exporting in Step 3 is the Userform object which was originally in the Userforms collection, and not the ActiveForm which contains the dynamically created form controls.

It's that ActiveForm I'm trying to figure out how to save/export, complete with the dynamically added controls. 

Can you supply a project I can test with?

What you want to achieve is called serialization.

Unfortunately, VBA do not offer any native way to do that.

I don't know the extent to which you want to add controls, but if you know the complete set that you might ever want to add you could add the complete set of controls to the userform and hide those that you don't want to see initially. Then instead of creating controls dynamically you could just un-hide them.

This is doable, with a serialize interface, but it will also require to write class wrappers implementing the serialize interface, faking forms / controls inheritance, forwarding every methods and properties.

In other words:
Not worth the troubles.

Thx Martin, yes pre-populating with controls and then hiding those I wouldn't want is a possible strategy but it requires quite a bit of coding to then reformat the remaining controls so they are properly arranged on the userform and the userform itself.

I'm part-way towards solving this issue myself.  I'll come back here with the final result/conclusions.

At the moment I can take an existing userform with or without controls (or create one dynamically) and dynamically add new controls and/or modify the existing form's controls.  I can then export the resulting modified userform, leaving the original form as it was before modifications (I could later delete it and replace it with the updated one if wanted).  I then import the modified userform back in under a different name, and this userform has all of the dynamically added/modified controls.

It's a little bit kludgy but it's working.

One thing I can't seem to do at the moment is to programmatically change the userform's caption and save it (it would seem obvious since I can effectively change the userform's name, e.g. .name = newformname, but oddly .caption = newcaption errors out.  I can find a way to do it in the .Designer version of the form but instead of it changing the caption value in the userform's properties, it seems to inject a new caption in a 'weird place' just under the old caption, in the form itself but doesn't change the value in the form properties).

To illustrate this part of the problem re userform's caption, with the relevant portion of code:

Set frm = ActiveWorkbook.VBProject.VBComponents(UserFormname)
newFormName = "MyNewForm"
   
 With frm
        .name = newFormName ' this works
        .Caption = "MyNewForm" ' oddly this errors out, can change name but not caption
 End With

If I try to change the caption using the .Designer object:

Set frm = ActiveWorkbook.VBProject.VBComponents(frm.name).Designer

 With frm
        .Caption = newFormName ' oddly, this is adding the caption within the Userform right under the caption almost like a label
 End With

What I'm presently able to accomplish and the above issue, my inability to programatically change the form's caption and save it, is illustrated in the images below:

After exporting the dynamically modified form as MyNewForm and importing it as MyNewForm, I'm left with this (the imported form is essentially what I want, except for the inability to properly alter the form's caption):

Hi Fabrice,
As shown in my response back to Martin above, I am part-way through resolving the problem without having to resort to a serialization approach.  I still have to figure out how to programatically change (and save in the form) the form's caption value.  This problem is odd since I have no trouble changing the form's name programatically or adding/deleting controls programatically ... it's odd that I can programatically change some of the form's properties (e.g. name) but not others (i.e. caption) ... unless the syntax isn't .caption = "new caption" (the syntax .name = "new name" works fine).
Thx,
Mike

If you add the complete set of controls it seems to me that all you need to do is unhide the "new" ones and possibly move them to where you want them. That can't be more work then adding them. BTW every type of control has its own set of properties with some overlap. So you may need to do something like

Dim ctl As Control

For Each ctl In Me.Controls
    Select Case TypeName(ctl)
        Case "Label"
            ' Set label properties
        Case "TextBox"
            ' Set textbox properties
        Case "CommandButton"
            ' Set command button properties
        'etc.
    End Select
Next

Select allOpen in new window

Why don't you simply save the usefull properties somewhere (in a text file or whatever), and reload them whenever you need them ?
It is far less troublesome, and easyer to maintain.

Thx Martin.  I already have a module working which add every or any control type to a target form.  My challenge was initially how to save the resulting modified form ... which I've since largely figured out, except for programmatically changing the modified form's caption (still working on that).

Thx Fabrice.  Saving the useful properties somewhere doesn't help me much in this particular quest since my intent is to be able to automatically create userforms (and subsequently be able to programmatically modify any target form and resave it) from a high level interface, e.g. one simply selects the form controls one wants in a form from a user-friendly interface and this form drives the automatic production of the desired Userform with all controls properly spaced and distributed, e.g. FORM_CreateWith("label", "textbox","radiobutton1", "radiobutton2","radiobutton3","combobox","textbox","commandbutton1","commandbutton2","commandbutton3") would automatically generate something like this:

 
This is just an example off the top of my head.  The idea is that one could get a very useable UserForm in seconds which would otherwise take a lot longer to design by hand, let alone have to redesign from scratch if variations were needed/wanted, especially where there is a lot of code attached to the controls.

In this example above I didn't go into details but the high-level interface which would drive the form creation could just as easily capture the control captions, sizes, general location, colours, etc and pass those through to the module responsible for creating the form.

I've already written a lot of the code to automate form creation and formatting, the part I was struggling with was how to save the dynamically created/modified form as a new userform.

I've figured out a reasonable workaround for now which automates this but I was hoping someone might know how to do it more elegantly by working from the dynamically modified form object instead of having to export and import the form.

Thx for the input just the same.

A high level interface? Kind if like an IDE?

Have you resolved this issue? If so please close the question by selecting one or more comments as your answer..