Determine when a userform is closes without the Terminate event 1

[JTBorton]

Hello, I’m looking for some ideas (no source code yet)

I have a user form which I have thoroughly tested, it functions well, and I use it often. Now I want to create multiple instances of the same user form to analyze different sets of data side by side, simultaneously. The form is very complex, having grown and evolved over more than 10 years, and simply adding redundant sections to would be very messy. I also don’t want to simply duplicate the form because I want to have a variable number of instances available for use, not just 3 hard copies. Plus, simply duplicating the form would require me to change each copy of the form anytime I want to adjust something. So I have decided to create a class module that will replicate the userform code and functionality, and will generate a dynamic userform and its corresponding controls each time I call a new instance of the class into action. This way I can have limitless array of userforms, each managed by an instance of my class module. I have used class modules to generate and manage clusters of dynamic controls within a static userform many times, so that part no problem for me.

The trouble I am finding is determining when the dynamic userform is closed. The events of the MSForms.Userform object library do not include Initialize and Terminate when it is declared as a variable. I can move the normal userform initialization and termination logic into the class intilialize and terminate events, but detecting when my form has closed becomes the problem. I can use Windows API functions to remove the windows ‘close’ button on the form window and replace it with a command button that will trigger my event, but if the form is closed by some method other than the user clicking the button, then I have no way to detect it. I do have a 'Form Manager' class that I created which logs the system window handle (Hwnd) of each form that I open (among other activities it provides). The only solution I can think of is to schedule a task that iterates through the list of window handles (Hwnd) and check if they are still open in the system, but I don't want to bog the execution down with this loop and I don't want to wait 5 min to clean up code after a form mysteriously closes. Do any of you amazing VBA coders have any thoughts on how to detect when a dynamic userform has been closed without the Terminate event?

NOTE: I do not want to enable access to the VBA project model in the Excel Trust Center. This would surely make the problem much easier to solve, but I want the code in this workbook to function perfectly without conflicting with any corporate security settings in microsoft office - which often prohibit this access.


-Joshua
If it's not broken, it doesn't have enough parts yet.

 

[Andrzejek]

Would this be of any help to you?
thread702-1818595

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

So I have decided to create a class module that will replicate the userform code and functionality, and will generate a dynamic userform and its corresponding controls each time I call a new instance of the class into action.

Actually an userform is a class and can be instantiated in more than one instance at the same time:

dim myForm1 as myUserForm, myForm2 as myUserForm
Set myForm1 = New myUserForm
Set myForm2 = New myUserForm
myForm1.Show vbModeless
myForm2.Show vbModeless

combo

 

[strongm]

T%echnically this is the best practice way of using forms. Sadly Microsoft muddies the water on this by making Form1 the default startup object in VB, which led to vast swathes of VB(A) programmers not realising that a form was a self-instantiating class, with all of the side-effects that that results in.

 

[JTBorton]

@Andrzejek, @Combo

Sadly these solutions are not applicable, because I still have the problem that a userform dynamically generated does not include the QuesryClose or Terminate events. This is why I need an alternative way to for the class shell to determine when the form is closed.

However it is becoming apparent that my plan is not possible without enabling access to the VBA project model in the Trust Center. Neither your examples, nor mine, will successfully generate a new dynamic userform in run time.

In addition to the code example below, See the attached screen shot (file download)

Option Explicit
Option Base 1

Private WithEvents pfrmParentForm As MSForms.UserForm

Public Property Get ParentForm() As MSForms.UserForm
    Set ParentForm = pfrmParentForm
End Property
Public Property Set ParentForm(TargetForm As MSForms.UserForm)
    Set pfrmParentForm = TargetForm
End Property

Private Sub Class_Initialize()
    Set ParentForm = VBA.UserForms.Add("frmTestForm").Show
    
End Sub

Private Sub Class_Terminate()
    On Error Resume Next
        Unload ParentForm.Name
    On Error GoTo 0
End Sub

Private Sub pfrmParentForm_Click()

End Sub

-Joshua
If it's not broken, it doesn't have enough parts yet.

 

[combo]

As long as you do not build the userform by code and the only need is: "Now I want to create multiple instances of the same user form to analyze different sets of data side by side, simultaneously.", generating multiple instances of the same userform is the answer:
- a code in the QueryClose event can handle the event and submit additional data,
- you can have public variable in the form's code and use it as the tag when creating multiple instances,
- between [tt]Set myForm1 = New myUserForm[/tt] and [tt]myForm1.Show vbModeless[/tt] it is possible to customize instances, including setting public variables,
- [tt]vbModeless[/tt] allows multiple threads.

The [tt]Set ParentForm = VBA.UserForms.Add("frmTestForm").Show[/tt] does not work. UserForm, has no Show method too.

combo

 

[JTBorton]

As long as you do not build the userform by code and the only need is: "Now I want to create multiple instances of the same user form to analyze different sets of data side by side, simultaneously.", generating multiple instances of the same userform is the answer:
- a code in the QueryClose event can handle the event and submit additional data,
- you can have public variable in the form's code and use it as the tag when creating multiple instances,
- between Set myForm1 = New myUserForm and myForm1.Show vbModeless it is possible to customize instances, including setting public variables,
- vbModeless allows multiple threads.

The Set ParentForm = VBA.UserForms.Add("frmTestForm").Show does not work. UserForm, has no Show method too.
combo

Aha!! NOW I understand what you were saying in your first post, and how to use your example, and it works.

MIND BLOWN!!!

Sub testmultipleforms()
    Dim frmJob1 As frmJobs
    Dim frmJob2 As frmJobs
    
    Set frmJob1 = New frmJobs
    Set frmJob2 = New frmJobs
    
    frmJob1.Show False
    frmJob2.Show False
    
    Stop
    
    Unload frmJob1
    Unload frmJob2
    Set frmJob1 = Nothing
    Set frmJob2 = Nothing
End Sub

-Joshua
If it's not broken, it doesn't have enough parts yet.