How to Unload a Form

eros

Board Regular
Joined
May 6, 2011
Messages
90
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

Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
1) Declare myForm as Object

2) force ctrLabel to the default null

Code:
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
 
Upvote 0
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.
 
Upvote 0
"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.
 
Upvote 0
Your code is syntactically correct and works for me, BTW.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 :

Code:
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?
 
Upvote 0
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.
 
Upvote 0
Why not just:
Code:
Unlaod myForm
then, given that your variable points at a specific instance.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top