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