Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: How to Unload a Form
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to Unload a Form

    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

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,581
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: How to Unload a Form

    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

  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Unload a Form

    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.

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,581
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: How to Unload a Form

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

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,804
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How to Unload a Form

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

  6. #6
    Board Regular
    Join Date
    May 2011
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Unload a Form

    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.

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,804
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How to Unload a Form

    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.

  8. #8
    Board Regular
    Join Date
    May 2011
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Unload a Form

    Quote Originally Posted by rorya View Post
    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?

  9. #9
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,581
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: How to Unload a Form

    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.

  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,804
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How to Unload a Form

    Why not just:
    Code:
    Unlaod myForm
    then, given that your variable points at a specific instance.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •