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

 

[mikerickson]

1) Declare myForm as Object

2) force ctrLabel to the default null

Public myForm As Object
 
Sub UnloadForm()
  Dim ctrlLabel As MSForms.Control
 
  For Each myForm In UserForms
    
    Set strlLabel = Nothing
    On Error Resume Next
    Set ctrlLabel = myForm.myLabel
    On Error Goto 0

    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]

Thank you Mike for your prompt return. Highly appreciated...

May I ask the logic behind your suggestion. At this point, I am just trying to completely figure out how Excel UserForms work.

I presume strlLabel must be ctrlLabel and somewhere there must be a label 0. Am I right?

Many thanks again.

 

[mikerickson]

"I presume strlLabel must be ctrlLabel" yes that is a typo on my part.

"and somewhere there must be a label 0. Am I right?" no, there is no need for a label0

What are you doing?
If its not just a theoretical exercise, there should be a better way to mark a userform for unloading than putting X in a label and looping through the Userforms collection.

 

[RoryA]

Your code is syntactically correct and works for me, BTW.

 

[eros]

Here is what I am trying to achieve:

My VBA code generates on-the-fly a seperate form with a form tag attached to it in order to later refer to the very exact from to unload from memory. Therefore I declare the form as Public to be reached from everywhere. So imagine I have 10 forms already in memory with tags X, Y, and Z. Then, I want to remove forms with tagged as X.

My code seem to pinpoint the form correctly; however, I cannot remove it from memory.

What rorya says is correct, I know because my code works, it just does NOT remove the form.

I trust I could clear the air now a bit more.

 

[RoryA]

What do you mean by "declare the form as Public"? If you have a reference to the form in a variable, then just use Unload with that variable.

 

[eros]

What do you mean by "declare the form as Public"? If you have a reference to the form in a variable, then just use Unload with that variable.

It means that my forms are accessed from different functions in my VBA. Some changes are made to other controls on the form during run-time.

What I mean is, the forms are loaded properly in memory, accessed, modified correctly; but when it comes to unloading them, it seems I cannot properly pinpoint the form, the exact location address in memory to refer to and remove.

Here is how I create a new form and load in memory: Please note how I allocate memory for each form :

Public myForm As myUserForm
 
Function ShowForm()
   Set myForm = New myUserForm
 
   Load myForm
 
   myForm.myLabel.Caption = "X"
   ... 'other control assignments
 
   myForm.Show vbModeless
end Function

How can I remove all forms tagged as X from memory after looping through collections, if the forms are created and loaded as above?

 

[mikerickson]

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.

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.

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.

 

[RoryA]

Why not just:

Unlaod myForm

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