Userform - Does Not Completely Close

[vthokienj]

I close a userform when the user clicks the x:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Unload userformNav
    'Unload me ' also tried this 
End Sub

The problem is that the form still seems to be active somewhere. When the user clicks a button it first checks to see if the userform is active before loading. The form is not equal to nothing, as it is prior to initially loading.

If (userformNav Is Nothing = False) Then
    MsgBox "program already running"
    Exit Sub
End If

Why, after closing the form with the first block of code, would the if test above return true?

Thanks

 

[Hermanito]

when checking for Is Nothing to be False, you need to use a different syntax, like this:

If Not userformNav Is Nothing Then
    MsgBox "program already running"
    Exit Sub
End If

Not sure if that caused the issue, but I would bet some money on it

 

[vthokienj]

Thanks for the reply, but that is not the issue.

I should have mentioned that the userform is set to nothing only after the reset button is pressed from the vba menu.
Is there a way for vba to call the reset functionality?
I have tried a few things such as this with no luck:

DoCmd.RunCommand acCmdReset

Thanks

 

[Hermanito]

How and where do you Load or Show the userform at first?

Unless you want special exitbehaviour, you shouldn't need to put Unload Me code in the QueryClose eventhandler. Have you tried without it? Is your form modal or modeless?

 

[mikerickson]

If you run this bit of code at any time, it will return False.

Dim aForm As UserForm1

Set aForm = New UserForm1
MsgBox aForm Is Nothing

Mearly by refering to the userform, VBA creates an instance of the form.
You could test

If UserForms.Count = 0 Then MsgBox "No loaded Userforms"

or to test for a particular uf.

Dim someForm As Object
Dim ufFlag As Boolean

For Each someForm In UserForms
    If someForm.Name = "UserForm1" Then ufFlag = True
Next someForm

If ufFlag Then
    MsgBox "UserForm1 is loaded"
Else
    MsgBox "UserForm1 is not loaded"
End If

Excel has a few cases where a not nothing argument is around

Dim aForm As UserForm1

Set aForm = New UserForm1
aForm.Show

MsgBox (aForm Is Nothing) & vbCr & UserForms.Count: rem returns False,0
    
' aForm.Show: Rem trying to run this line will crash

similar to

Dim myRange As Range
Set myRange = Range("A1")

myRange.Delete

MsgBox myRange Is Nothing: Rem returns False
MsgBox myRange.Cells.Count: Rem code errors "Object variable not set"

 

[vthokienj]

Thanks to both. I should be able to modify my code to iterate through the UserForms with the for each statement to have it work as expected.

I still do not understand the behavior though. As shown by the following sequence, the form is not nothing at #5, yet at #6 there is no userform in the group.

Message Box output at each point:
1) false
2) true
3) formNav
4) true
5) true
6) -does not access this msgbox-

Private Function DoesFormExist(theform As UserForm) As Boolean
    If theform Is Nothing Then
        DoesFormExist = False
    Else
        DoesFormExist = True
    End If
End Function

Dim aform As formNavigator
MsgBox "1. after variable definition: " & DoesFormExist(aform)

Set aform = New formNavigator
MsgBox "2. after set statement: " & DoesFormExist(aform)

For Each auserform In UserForms
MsgBox "3. after set - form present with name " & auserform.name
Next auserform

aform.Show vbModeless
MsgBox "4. after show: " & DoesFormExist(aform)

Unload aform
Dim exists As Boolean
If aform Is Nothing Then ' crashes if aform sent to function
exists = False
Else
exists = True
End If
MsgBox "5. after unload: " & exists

For Each auserform In UserForms
MsgBox "6. after unload - form present with name " & auserform.name
Next auserform

 

[mikerickson]

Dim aForm as UserForm1

aForm = New UserForm1
aForm.Show
unLoad aForm

Rem Right Here

You could think of it that at RightHere, aForm is a variable that points to no object and neither does it point to the constant Nothing.

 

[vthokienj]

Oh, that makes sense. Thanks again.