Sleeper: Copying UserForm under VBA Control

[MWE]

I have a userform created using the standard methods in the VBA editor. Let's call this "ufA". During execution of the relevant application, several "copies" of ufA are required, each copy being a VBA-tweaked version of the original and presented with the "vbModeLess" method so it stays on the screen while the appl does other things.

Creating a number of copies of ufA in the editor with some naming convention, using each as is required, tracking which are "active", etc., works but requires extra code to track things and is ultimately limited by the number of original copies.

A much better solution is to make copies of ufA as necessary with appropriate new names. This eliminates all tracking requirements and (within reason) the limits on # of forms on the screen at one time. Unfortunately it appears that making copies of userforms or creating new userforms under VBA control is not possible.

Does anyone know how this might be done?

Thanks

[Andy Pope]

Hi MWE,

You can create copies of your userform.
Try this, create a userform and add a label. Hopefully the default names will be Userform1 and Label1.

In a standard code module add the following code.

Dim g_objU1 As UserForm1
Dim g_objU2 As UserForm1

Sub x()
Set g_objU1 = New UserForm1
Set g_objU2 = New UserForm1
Load g_objU1
g_objU1.Label1.Caption = "This is copy 1"
g_objU1.Show vbModeless
Load g_objU2
g_objU2.Label1.Caption = "This is copy 2"
g_objU2.Show vbModeless
End Sub

Sub z()
Unload g_objU1
Unload g_objU2
Set g_objU1 = Nothing
Set g_objU2 = Nothing
End Sub

Now run routine x. You should be able to move the 2nd copy of the userform to reveal the first version.
Routine z will release the objects from memory

[MWE]

Andy: thanks for your interesting approach. It is not quite as generic as I was hoping for, but is far better than what I was doing. I used an array instead of individual declaration statements, i.e.,

Public ufInstance(10) as ufTemplate
...
...
...
Set ufInstance(I) = New ufTemplate
...
...
...

to easily manage the # of instances and individual instances. Works fine.

This approach leads to two other questions about UserForms:

1. is there a way to execute code when the UserForm is closed (by clicking on the X in the upper right of the UserForm). There appears to be a "on exit" proc for some controls, but not for the UserForm. It also appears that when the on exit code for a control executes, the UserForm has already lost any VBA added or modified controls. I could use a command button to close the UserForm, but I would prefer to use the X
2. if I have to use a command button to close the UserForm with some control, is there a way to eliminate the X in the upper right corner?

Thanks

[Norie]

The UserForm has QueryClose and Terminate events.

Perhaps you could use them.

[MWE]

Norie: thanks for the suggestion. Unfortunately those events have the same problem as the "on exit" events. I suspect that the problem is actually a little different than "loosing" VBA mods. The code associated with the UserForm references the original userform, not one of the "copies". So any VBA added controls do not exist for the original and any VBA mods are unknown to the original.

I have cobbled up a method to keep track of which instances are "open" by indexing through all instances and checking for visibility. If not visible, it has been closed and appropriate tracking variables are updated. If the test has an error, then the instance was never generated (yet). It works but ...

What I need is someway to know which instance is "active"; perhaps something akin to ActiveSheet or ActiveChart (ActiveForm?).

MWE

[johnske]

1. is there a way to execute code when the UserForm is closed ....

Thanks

What about UserForm_Deactivate() ?

[Andy Pope]

You can use the Terminate and QueryClose events if you put code in the template userform.

Here is another variation on the code. Instead of an array it uses a collection.
The button on the userform creates another clone of itself.

Code for your standard code module

Public g_colMyForms As Collection
Public g_lngActiveMyForm As Long

Sub Demo()
' entry point
    AddUserform
    
End Sub
Sub AddUserform()
' add another userform
    Dim objMyForm As UserForm1
    
    If g_colMyForms Is Nothing Then
        Set g_colMyForms = New Collection
    End If
    
    Set objMyForm = New UserForm1
    
    g_colMyForms.Add objMyForm, CStr(g_colMyForms.Count + 1)
    
    Load objMyForm
    With objMyForm
        .Label1.Caption = "This is copy " & g_colMyForms.Count
        .Caption = "Copy " & g_colMyForms.Count
        .Tag = g_colMyForms.Count
        .Show vbModeless
    End With
    
End Sub
Sub DestroyUserforms()
' release userforms from memory
    Do While g_colMyForms.Count > 0
        Unload g_colMyForms.Item(g_colMyForms.Count)
        g_colMyForms.Remove g_colMyForms.Count
    Loop
    Set g_colMyForms = Nothing
    
End Sub

Code for userform, containing Label1, CommandButton1

Private Sub CommandButton1_Click()

    AddUserform
    
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    MsgBox "Query close " & Me.Name & " " & Me.Caption

End Sub

Private Sub UserForm_Terminate()

    MsgBox "Terminating " & Me.Name & " " & Me.Caption
    
End Sub
Private Sub UserForm_Deactivate()

    Me.Caption = "Inactive"
End Sub
Private Sub UserForm_Activate()

    g_lngActiveMyForm = CLng(Me.Tag)
    Me.Caption = "I'm Active"
    
End Sub

[MWE]

What about UserForm_Deactivate() ?

Thanks for your reply. Unfortunately, it has the same problem as with other methods at close, i.e., any reference to the UserForm reverts to the original UserForm, not the current instance. Per my earlier post, the code idea is:

Public ufInstance(10) as ufTemplate
...
Set ufInstance(I) = New ufTemplate
...
ufInstance(I).Show (vbModeless)

This allows up to 10 instances of the uf on the screen at any time. When a particular instance of the uf is closed, say, ufInstance(j), any code in the code module associated with ufTemplate refers to ufTemplate and not to ufInstance(j). That actually makes sense (even though I wish it worked the way I want) because the uf code does refer to the ufTemplate and not the ufInstance -- the ufInstance's "name" is unknown to the code.

Tests run so far:

1. if I had a sub that activated when the uf closes and displayed the name, e.g., MsgBox me.name
the name displayed is ufTemplate.
2. If I add a label to ufTemplate, set Visible=False, and use it to label the instance: ufInstance(I).lblName = "ufInstance"+Format(I),"##"); when I display lblName on close, the text displayed is whatever was in lblName for the Template
3. I tried adding code to that did refer explicitly to the (future) instance:

Private Sub UserForm_Terminate()
MsgBox Me.Name + " ... " + ufInstance(1).lblName.Caption
End Sub

and when ufInstance(1) is closed the sub correctly displays ufTemplate ... ufInstance01

But that is cheating because I knew which instance to reference. What I need is a method that somehow knows which instance is "active".

[Andy Pope]

How about adding and Instance property to the template userform.

Private m_lngInstance As Long
Public Property Let Instance(RHS As Long)
    m_lngInstance = RHS
End Property
Public Property Get Instance() As Long
    Instance = m_lngInstance
End Property
Private Sub CommandButton1_Click()
    AddUserform
End Sub
Private Sub UserForm_Terminate()

    MsgBox "Terminating " & Me.Name & " ... " & vbLf _
            & g_colMyForms.Item(Me.Instance).Label1.Caption & vbLf _
            & Me.Label1.Caption

End Sub
Private Sub UserForm_Activate()

    g_lngActiveMyForm = Me.Instance
    Me.Caption = "I'm Active"
    
End Sub

With this revision to the AddUserform routine

Sub AddUserform()
' add another userform
    Dim objMyForm As UserForm1
    
    If g_colMyForms Is Nothing Then
        Set g_colMyForms = New Collection
    End If
    
    Set objMyForm = New UserForm1
    
    g_colMyForms.Add objMyForm, CStr(g_colMyForms.Count + 1)
    
    Load objMyForm
    With objMyForm
        .Label1.Caption = "This is copy " & g_colMyForms.Count
        .Caption = "Copy " & g_colMyForms.Count
        .Tag = g_colMyForms.Count
        .Instance = g_colMyForms.Count
        .Show vbModeless
    End With
    
End Sub

[MWE]

Andy: I really appreciate all the help you have provided. I have learned a lot about UserForms. See comments below:

You can use the Terminate and QueryClose events if you put code in the template userform.

Here is another variation on the code. Instead of an array it uses a collection.
The button on the userform creates another clone of itself.

Code for your standard code module

Public g_colMyForms As Collection
Public g_lngActiveMyForm As Long

Sub Demo()
' entry point
    AddUserform
End Sub

Sub AddUserform()
' add another userform
    Dim objMyForm As UserForm1
    
    If g_colMyForms Is Nothing Then
        Set g_colMyForms = New Collection
    End If
    
    Set objMyForm = New UserForm1
    
    g_colMyForms.Add objMyForm, CStr(g_colMyForms.Count + 1)
    
    Load objMyForm
    With objMyForm
        .Label1.Caption = "This is copy " & g_colMyForms.Count
        .Caption = "Copy " & g_colMyForms.Count
        .Tag = g_colMyForms.Count
        .Show vbModeless
    End With
    
End Sub
Sub DestroyUserforms()
' release userforms from memory
    Do While g_colMyForms.Count > 0
        Unload g_colMyForms.Item(g_colMyForms.Count)
        g_colMyForms.Remove g_colMyForms.Count
    Loop
    Set g_colMyForms = Nothing
    
End Sub

Thanks, a nice method and it has advantages over the array approach I used (I admit to being very comfortable with arrays -- my FORTRAN background is showing). Manipulating instances or object elements is not difficult when you want to process all instances or know beforehand how to process selected instances. The trick is to operate on the active instance.

I added code before the creation of each new instance to check all 10 possible instances and unload/set=Nothing any previously created instance that was now not visible. That works fine and frees up memory

Code for userform, containing Label1, CommandButton1

Private Sub CommandButton1_Click()

    AddUserform
    
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    MsgBox "Query close " & Me.Name & " " & Me.Caption

End Sub

Private Sub UserForm_Terminate()

    MsgBox "Terminating " & Me.Name & " " & Me.Caption
    
End Sub
Private Sub UserForm_Deactivate()

    Me.Caption = "Inactive"
End Sub
Private Sub UserForm_Activate()

    g_lngActiveMyForm = CLng(Me.Tag)
    Me.Caption = "I'm Active"
    
End Sub

I have tried variations of these suggestions and constantly run into the same problem, i.e., that any code using the "me" object refers to the template and not to the instance. In your above examples, when you close a "copy" and display me.name and me.caption, what is displayed? I suspect whatever you called the original userform and whatever was stored in the caption control for the original userform.

I tried your code precisely (cut and paste). It runs and creates copies of the orignal userform. But when I closed any copy on the screen, I encountered just the problems I expected:

1. I execute the demo and using the command button made 5 additional copies
2. The label on each copy was correct
3. I then closed each at random:

• the UserForm_QueryClose sub triggered and displayed: "Query close UserForm1 I'm Active"
• the UserForm_Terminate sub triggered and displayed: "Terminating UserForm1 UserForm1"

The display results are exactly what I expected.

I also tried leaving the copies on the screen and executing the DestroyUSerForms sub. It indexed through the copies triggering the QueryClose and Terminate subs and displaying the same (bogus) information.

See comments to Johnske about code references to specific instances.

If you are able to generate several copies and when each is closed display the correct caption, e.g., "Copy1", then perhaps you are running a different version of MSForms than I am. My version is MSForms 2.0

Again, many thanks for all of your help (so far?)

[Andy Pope]

I change the Msgbox to a Debug.Print in teh Terminate event. This is the output after creating 5 userforms and then closing the current top one.

Terminating UserForm1 ... This is copy 5  This is copy 5
Terminating UserForm1 ... This is copy 4  This is copy 4
Terminating UserForm1 ... This is copy 3  This is copy 3
Terminating UserForm1 ... This is copy 2  This is copy 2
Terminating UserForm1 ... This is copy 1  This is copy 1

I have attached my small example.

I have all but shed my Fortran skin!

[Killian]

Just a thought here...
When creating multiple intances of anything, I tend to make a class object and instance one whenever needed. That way, they can easily be managed in a collection and you then have the class initialize and terminate events as well as any other additional properties (like index) and methods you care to write into the class