Results 1 to 9 of 9

Thread: Unloading userform doesn't seem to work

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Unloading userform doesn't seem to work

    Hi there,
    I am using Excel 2013 and am getting some strange behaviour when unloading a userform using the "Unload Me" statement. From what I understand the Unload command should remove the instance of the userform from memory. However even after the unload statement has executed, I am still able to access the userform and it's properties.

    My module code:
    Code:
    Option Explicit
    
    Sub FormsTest()
    
    
    Dim myFrm As frmTestForm
    Set myFrm = New frmTestForm
    Dim strTextEntry As String
    Dim MsgBoxResult1 As VbMsgBoxResult
    
    
    'Show form for user to enter new employee details
    myFrm.Show
    
    
    'If user clicks OK then instance will still exist so get the data from form
    If Not (myFrm Is Nothing) Then
        strTextEntry = myFrm.tbTextEntry.Value
        MsgBoxResult1 = MsgBox("You have entered the following text: " & strTextEntry, vbOKOnly)
    'If user clicks cancel then form will be unloaded (in "cmdCancel_Click()" procedure) and so instance won't exist
    Else
        MsgBoxResult1 = MsgBox("You cancelled the data entry", vbOKOnly)
    End If
    
    
    End Sub
    And the userform code:

    Code:
    Option Explicit
    
    
    Private Sub cmdCancel_Click()
    Unload Me
    End Sub
    
    
    Private Sub cmdOK_Click()
    Me.Hide
    End Sub
    If I run the code and enter something in the userform textbox and then press the cancel button, the code correctly executes the unload command, but the "If Not (myFrm Is Nothing) Then" evaluates to true and the code is still able to set the strTextEntry value to the value of the userform text box, even though it should have been unloaded. Any thoughts?

    Thanks!

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

    Default Re: Unloading userform doesn't seem to work

    Unloading the form will not make your variable Nothing. What you should do is use a property in the form to determine if it has been cancelled and only ever hide it from the form's code.

  3. #3
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,349
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Unloading userform doesn't seem to work

    Quote Originally Posted by RoryA View Post
    Unloading the form will not make your variable Nothing. What you should do is use a property in the form to determine if it has been cancelled and only ever hide it from the form's code.
    Hi Rory,

    Could you elaborate on that ? Do you mean a custom Proprty or a built-in one ?

    Thanks.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  4. #4
    New Member
    Join Date
    Feb 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unloading userform doesn't seem to work

    Quote Originally Posted by RoryA View Post
    Unloading the form will not make your variable Nothing. What you should do is use a property in the form to determine if it has been cancelled and only ever hide it from the form's code.
    Hi Rory, OK, so I can get that it might not set the variable to Nothing, and I'm changing that implementation. But what I don't understand is how, once I've unloaded the form, I can still access all it's properties. If that's the case what is the unload command actually doing?!

    Thanks!

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

    Default Re: Unloading userform doesn't seem to work

    Quote Originally Posted by Jaafar Tribak View Post
    Hi Rory,

    Could you elaborate on that ? Do you mean a custom Proprty or a built-in one ?

    Thanks.
    I meant a custom one. Sorry, should have been clearer.

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

    Default Re: Unloading userform doesn't seem to work

    Quote Originally Posted by OliverP View Post
    But what I don't understand is how, once I've unloaded the form, I can still access all it's properties.
    You can't access all of them. Trying to access anything other than a control on the form should give you an automation error. As best I can figure, the form is in a slightly weird half-state at that point, where it's not quite destroyed, but also not intact. The unload command has started the process of tearing down the form but your variable is holding a reference to it which seems to prevent it from fully unloading.

  7. #7
    New Member
    Join Date
    Feb 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unloading userform doesn't seem to work

    Quote Originally Posted by RoryA View Post
    You can't access all of them. Trying to access anything other than a control on the form should give you an automation error. As best I can figure, the form is in a slightly weird half-state at that point, where it's not quite destroyed, but also not intact. The unload command has started the process of tearing down the form but your variable is holding a reference to it which seems to prevent it from fully unloading.
    Yeah, you're right, it only seems to be certain things I can access, and if I add myFrm to the watch list everything listed there is given as "automation error" and the text boxes aren't even shown. So obviously not in a valid state somehow. Change of approach definitely required!

    Thanks for your help!

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

    Default Re: Unloading userform doesn't seem to work

    I basically just add a Boolean Cancelled property to the form, and in the Cancel button's code I set the property to True and then hide the form. The calling code takes care of unloading the form at the appropriate time.

  9. #9
    New Member
    Join Date
    Feb 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unloading userform doesn't seem to work

    Quote Originally Posted by RoryA View Post
    I basically just add a Boolean Cancelled property to the form, and in the Cancel button's code I set the property to True and then hide the form. The calling code takes care of unloading the form at the appropriate time.
    I had just put a public boolean as a quick workaround but a property is definitely a better approach.

    Thanks again!

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
  •