Close Button Works Unload Me does not

Question

I have a for loop in UserForm1 which opens UserForm2 a variable number of times to grab the appropriate amount of data from the user. See below the code running in UserForm1

Private Sub Start_Click()
    Constants
    InitBoard
    While Not ValidTest
        IncParts
    Wend
End Sub

Public Sub InitBoard()
    Dim row As Integer
    Dim col As Integer
    For Mirror = 1 To NumBlocks(2, 1)
        LockType = 1
        UserForm2.Show
    Next Mirror
    For Prism = 1 To NumBlocks(2, 2)
        LockType = 2
        UserForm2.Show
    Next Prism
    For Wormhole = 1 To NumBlocks(2, 3)
        LockType = 3
        UserForm2.Show
    Next Wormhole
    For Blocker = 1 To NumBlocks(2, 4)
        LockType = 4
        UserForm2.Show
    Next Blocker
    For Splitter = 1 To NumBlocks(2, 5)
        LockType = 5
        UserForm2.Show
    Next Splitter
End Sub

Now if I run the code, I can grab data in UserForm2 appropriately. When I close UserForm2 manually with the red 'X' button, everything works as expected. UserForm2 pops up again with showing the data was accepted from the previous run through the loop. After each loop has been run through the appropriate number of times, UserForm2 stops opening and the code continues in UserForm1 to the While loop of the Start_Click() sub. However, if I use Unload Me at the end of a sub inside UserForm2 to close it automatically after it receives the correct input, I get "Run-time error '91': Object variable or With block variable not set". When I press Debug, Line 5 of InitBoard() in the above code is highlighted (UserForm2.Show). Below is a checkbox click function that I was using inside UserForm2. commenting out line 3 fixes the issue, but I must close the form manually.

Private Sub Bstate00_Click()
    BoardState(0, 0) = LockType + 5
    Unload Me
End Sub

I've tried every combination I can think of to Load UserForm2 before showing, making sure not to end the loop before UserForm2 has closed each time, and even adding a delay with no avail. Me.Hide does fix the issue, but does not run the UserForm2_Initialize() sub needed to update the info entered in the previous loop.

Please see below as per the conversation about the 402 error in the comments the minimum code to recreate the error:

In UserForm1:

Private Sub Start_Click()
    For Mirror = 1 To 3
        LockType = 1
        With New UserForm2
            .Show
        End With
    Next Mirror
End Sub

In UserForm2:

Private Sub Bstate00_Click()
    BoardState(0, 0) = LockType + 5
    Me.Hide
End Sub

Private Sub UserForm_Initialize()
    If BoardState(0, 0) = -1 Then
        Me.Controls("BState" & 0 & 0).Value = False
        Me.Controls("BState" & 0 & 0).Enabled = False
    ElseIf BoardState(0, 0) = 0 Then
        Me.Controls("BState" & 0 & 0).Value = False
        Me.Controls("BState" & 0 & 0).Enabled = True
    Else
        Me.Controls("BState" & 0 & 0).Value = True
        Me.Controls("BState" & 0 & 0).Enabled = False
    End If
End Sub

In Module1:

Public BoardState(0 To 5, 0 To 5) As Integer
Public LockType As Integer

This is an inherent problem with using the default instance of a UserForm. I'd start by reading this answer, then reading the linked blog post. — Comintern
A form is an object, i.e. an instance of a class. UserForm classes have a default instance, which is what gets displayed when you do UserForm1.Show. The problems you're experiencing are inherent to coding against a stateful default instance, and self-destructing objects (e.g. Unload Me). Read the article @Comintern linked (I wrote it), it explains everything you need to fix in a tutorial-like post that covers everything there is to know about doing this right. — Mathieu Guindon
Same 402error, but I'm currently working on the code rewrite so that I still get error with the Minimal, Complete... thing — Andrew Werner
FYI part of the problem is an under-documented "feature" of MSForms where closing a form from anything other than the Click handler of a CommandButton (or menu item, but that's not applicable to VBA) can cause general protection faults and unexpected behavior (again credits go to @Comintern) - is the requirement to close the form upon checking a checkbox a hard requirement? — Mathieu Guindon

Comintern Comintern · Accepted Answer · 2019-02-12T01:10:44

tldr; Buttons matter. All is not as it seems in UserForm land.

FWIW, I can't reproduce the 402 run-time error in Excel 2013 x64 but I did discover some interesting behavior. Given the following code:

'UserForm1.cls
Private Sub UserForm_Initialize()
    Debug.Print "UserForm1_Initialize"
End Sub

Private Sub UserForm_Click()
    Dim i As Long
    For i = 1 To 3
        With New UserForm2
            .Show vbModal
        End With
    Next
    Debug.Print "Done"
End Sub

'UserForm2.cls
Private Sub UserForm_Initialize()
    Debug.Print "UserForm2_Initialize"
End Sub

Private Sub UserForm_Click()
    Me.Hide
End Sub

Private Sub UserForm_Terminate()
    Debug.Print "UserForm2_Terminate"
End Sub

Executing the above (showing UserForm1 as modal) gives the following output when clicked to dismiss:

UserForm1_Initialize
UserForm2_Initialize
UserForm2_Initialize
UserForm2_Initialize
Done

Note that none of the terminate events fire when the With block exits. Calling Unload Me in the click handler behaved normally. This was completely unexpected, so I dug through the scant documentation for the UserForm class and found this quote from Paul Lomax¹:

Microsoft recommends that forms should be unloaded only in the Click event of a CommandButton or menu control. Calling the Unload statement in other event handlers can have undesirable side effects and cause general protection faults (GPFs).

This made me curious what would happen if I moved the Me.Hide statement into a CommandButton handler:

Private Sub UserForm_Initialize()
    Debug.Print "UserForm2_Initialize"
End Sub

Private Sub CommandButton1_Click()
    Me.Hide
End Sub

Private Sub UserForm_Terminate()
    Debug.Print "UserForm2_Terminate"
End Sub

Executing this code gave the following output...

UserForm1_Initialize
UserForm2_Initialize
UserForm2_Terminate
UserForm2_Initialize
UserForm2_Terminate
UserForm2_Initialize
UserForm2_Terminate
Done

...exactly as expected. It seems that there is some undocumented weirdness surrounding VBA's handling of UserForms. But it gets even more bizarre. If I leave the CommandButton on the form and revert back to the original code (hiding the form from the form click handler without the UserForm_Click() handler in the code-behind at all, it still fires the Terminate event. The mere presence of the CommandButton on the form alters the unload behavior. That said, you might try putting a hidden CommandButton on the form and see if that resolves it. If not, you can always explicitly Unload the form from the calling site:

Private Sub UserForm_Click()
    Dim i As Long
    Dim subForm As UserForm2
    For i = 1 To 3
        Set subForm = New UserForm2
        subForm.Show vbModal
        Unload subForm
    Next
    Debug.Print "Done"
End Sub

¹_{Lomax, Paul. VB & VBA in a Nutshell: The Language. Sebastopol, CA: OReilly, 1999., p.567}

Close Button Works Unload Me does not

1 Answers