How to Unload a Form

[eros]

Hi everyone,

I have a bunch of forms in memory as part of UserForms collocation. I find the particular form that I want to unload from memory through a "for each" loop, but I fail to do so successfully.

Could anyone suggest a correct syntax?

Many thanks in advance

Public myForm As UserForm 
 
 
Sub UnloadForm()
  Dim ctrlLabel As MSForms.Control
 
  For Each myForm In UserForms
    On Error Resume Next 
    Set ctrlLabel = myForm.myLabel 
    If Not ctrlLabel Is Nothing Then
      If myForm.myLabel.Caption = "X" Then
 
              Unload myForm  'DOES NOT SEEM TO BE WORKING...
 
      End If
    End If
  Next
End Sub

 

[eros]

Are you repeatedly calling ShowForm? Are you putting each instance of myUserForm in your own collection after it is created? That collection would be a good way to keep all the run-time UFs handy for referencing.

Yes I repeatedly call ShowForm for different tags, X, Y, Z and to assign different control values available on the form. I assume Excel puts all these newly created forms in Userforms collection. And I can loop through them using this collection managed by Excel. Therefore, I don't do anything further. Am I right?

Alternatly, to mark a userform as "made" you could put an "x" in the .Tag property of the userform. That avoids the error handling to check if someForm has a myLabel control.

That's a beautiful idea! I will certainly use it. It will help a lot, timewise for my VBA code.

On another level, why all the run-time userforms? An existing userform that has Pages added to its Multi-page control at run time seems a more stable control.

I would love to do it, but it is way beyond my current level of Excel knowledge. If you would be kind to show me how to set up that multi page forms, I would appreciate. Nevertheless, I would also love to know how to get around my problem I am currently suffering from. How can I unload my primitive forms from memory based on the setup I have used. I just spend too much time for this setup and I cannot ignore it completely at the moment.

Many many thanks indeed.

 

[eros]

Why not just:

Unlaod myForm

then, given that your variable points at a specific instance.

That just does not work. It is the first thign I have tried...

 

[eros]

That just does not work. It is the first thign I have tried...

Let me be more specific... It removes all forms already open on my screen. However I target only specific forms with a special tag value.

 

[eros]

Let me be more specific... It removes all forms already open on my screen. However I target only specific forms with a special tag value.

Gee!!! I checked my code once again, it works now. It is a simple if check error on my side for tag values. "Unload myForm" works.

Sorry for bothering. How I am ashamed, I cannot tell enough.

However, you have given me great suggestions. Thanks for all...

Nevertheless, I am looking forward to multipage implementation of forms.

Truly, many many thanks!!

 

[mikerickson]

I'm away from my Excel at the moment, so I can't be sure of the syntax of a
UserForm1.MultiPage1.Pages.Add statement.

One further step in simplification would ask Why have different instances of the same userform? If each userform does the same thing to the different objects that it is "pointed" at, it might be more stable to have one userform and with a variable "pointer", than many userforms with fixed "pointers". As you can tell, this approach would be very use dependent, so What are you doing with each of these userforms?

 

[eros]

I'm away from my Excel at the moment, so I can't be sure of the syntax of a
UserForm1.MultiPage1.Pages.Add statement.

One further step in simplification would ask Why have different instances of the same userform? If each userform does the same thing to the different objects that it is "pointed" at, it might be more stable to have one userform and with a variable "pointer", than many userforms with fixed "pointers". As you can tell, this approach would be very use dependent, so What are you doing with each of these userforms?

No worries Mike. I myself can look up multipage and relevant syntax. With this multipage approach is it possible to collect all different forms in one place and switch to each form as if it is a page of book? Then it might work for me.

As you say Mike, in my case I think this is what I need. I didn't quite understand your "pointed" approach; however, I can say that each form, being a different instance of a UserForm, holds different data, well a block of data by means of label controls on the form. So I need these forms seperately, and sequentially as if a database record. I check the label controls as my data fields and use them further in my VBA code. I hope this explains what I am up to.

Many many thanks again for your kind assistance.