Unloading userform doesn't seem to work

[OliverP]

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:

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:

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!

 

[RoryA]

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.

 

[Jaafar Tribak]

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.

 

[OliverP]

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!

 

[RoryA]

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.

 

[RoryA]

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.

 

[OliverP]

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!

 

[RoryA]

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.

 

[OliverP]

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!