×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Determine when a userform is closes without the Terminate event

Determine when a userform is closes without the Terminate event

Determine when a userform is closes without the Terminate event

(OP)
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.

RE: Determine when a userform is closes without the Terminate event

Quote (JTBorton)

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:

CODE -->

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

combo

RE: Determine when a userform is closes without the Terminate event

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.

RE: Determine when a userform is closes without the Terminate event

(OP)
@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)

CODE

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.

RE: Determine when a userform is closes without the Terminate event

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

RE: Determine when a userform is closes without the Terminate event

(OP)

Quote:


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!!!

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close