Userform closes after "End Sub" without ever calling "Unload Me"

Question

I have a userform (baseUF) that has multiple pages and buttons that all do different things. I have this baseUF being modeless because I want the user to be able to play with the sheet without closing the userform and losing all of the data they input. However, I started having a problem that might be due to the modeless nature of the baseUF.

There are other userforms that can be called from the baseUF. One executes with no issue by double clicking a textbox. However, the other userform is loaded after a button click. Once that button click sub is finished, the baseUF closes after the Exit Sub OR End Sub line. I don't remember this happening in the past and it doesn't happen with any other button click subs.

Does anybody have an idea what the issue could be? I'm pretty lost because I don't have a command to close the baseUF anywhere in that sub. Below is some code to show what is happening:

This sub is connected to a button on the spreadsheet to open the baseUF (the code is in a module).

Sub Button1_Click()

' show the userform
baseUF.Show vbModeless

End Sub

And this is the sub in the baseUF that calls an additional userform (LoadBox) which seems to be the issue.

Private Sub LoadQuery_Click()

' I Dim a bunch of stuff here

' if there are no saved queries, alert the user
If saveSht.Range("B3").Value = "" Then
    MsgBox "No saved queries!"
    Exit Sub
' if there is only one saved query, add it to the array and pop up the userform that allows for the user to select which save to load
ElseIf saveSht.Range("B4").Value = "" Then
    save_names = saveSht.Range("B3").Value
    LoadBox.Show
' otherwise, add all of the save names to the array and pop up that userform
Else
    save_names = saveSht.Range(saveSht.Range("B3"),saveSht.Range("B3").End(xlDown)).Value
    LoadBox.Show
End If

' if the user didn't select a save to load, stop trying to make stuff happen
If load_name = "" Then
    ' the userform will also close here if this turns out to be true
    Exit Sub
End If

' do a bunch of stuff with the selected name here

' and after this line, the userform that contains this code closes
End Sub

EDIT: here is some code showing the two other userforms

This one is the userform with no issue that is called after a textbox is double clicked

Private Sub UserForm_Initialize()

' On start up of this form, populate the listbox with the relevant column names

' Set position
Me.StartUpPosition = 0
Me.Top = baseUF.Top + 0.5 * baseUF.Height - 0.5 * Me.Height
Me.Left = baseUF.Left + 0.5 * baseUF.Width - 0.5 * Me.Width

With FilterSelectionBox
    ' First grab all of the column names from the main selected table
    For i = 0 To baseUF.SelectionBox.ListCount - 1
        .AddItem baseUF.SelectionBox.List(i)
    Next i
    ' Then grab all of the column names from the additional tables to be joined
    If Not IsVariantEmpty(join_table_cols) Then
        For n = 0 To UBound(join_table_cols)
            If Not IsEmpty(join_table_cols(n)) Then
                For Each col_name In join_table_cols(n)
                    .AddItem col_name
                Next
            End If
        Next n
    End If
End With

End Sub

Private Sub OkButton_Click()

' Initialize the variables
Dim tb As MSForms.TextBox
Dim arr() As String
Dim str As String

' tb is the textbox object that the column names will be pasted in to
Set tb = baseUF.MultiPage1.Pages(baseUF.MultiPage1.Value).Controls(Me.Tag)

' sets the str according to some logic

' This is actually where it gets sent
tb.Value = str

' And close the form
Unload Me

End Sub

And this is the code in the userform with an issue

Private Sub UserForm_Initialize()

' On initialization, populate the combobox with all of the save names present in the spreadsheet

' Set position
Me.StartUpPosition = 0
Me.Top = baseUF.Top + 0.5 * baseUF.Height - 0.5 * Me.Height
Me.Left = baseUF.Left + 0.5 * baseUF.Width - 0.5 * Me.Width

With LoadComb
    ' If there is more than one save present, go through the array and add each one
    If IsArray(save_names) Then
        For Each saved_name In save_names
            .AddItem saved_name
        Next
    ' Otherwise just add the one
    Else
        .AddItem save_names
    End If
End With

End Sub

Private Sub LoadButton_Click()

' When the user hits the load button, first check if they actually selected anything
If LoadComb.Value = "" Then
    ' If they didn't, yell at them
    MsgBox "No saved query selected!"
Else
    ' Otherwise, save the name to a global variable
    load_name = LoadComb.Value
End If

' Close the form
Unload Me

End Sub

Vityata · Accepted Answer

Whenever something unexpected happens with forms, consider writing End in the immediate window and pressing enter. It will kill all the unkilled instances of a form and generally any variable, thus it would be like a cold restart to the VBA program.

After doing this, it is a good idea to consider a cleaner solution, concerning VBA & UserForms, using some OOP. (Disclaimer - the first article is mine):

Although it may seem that you are achieving the same results with more code, the benefits of using this approach are quite a lot in the long term.

This is a small example of the OOP model. Imagine you have a user form like this:

It has only the following controls:

btnRun
btnExit
lblInfo
frmMain (the class)

The code withing the form is the following:

Option Explicit

Public Event OnRunReport()
Public Event OnExit()

Public Property Get InformationText() As String    
    InformationText = lblInfo.Caption    
End Property

Public Property Let InformationText(ByVal value As String)    
    lblInfo.Caption = value    
End Property

Public Property Get InformationCaption() As String    
    InformationCaption = Caption    
End Property

Public Property Let InformationCaption(ByVal value As String)    
    Caption = value    
End Property

Private Sub btnRun_Click()    
    RaiseEvent OnRunReport    
End Sub

Private Sub btnExit_Click()    
    RaiseEvent OnExit    
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)    
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        Hide
    End If    
End Sub

The form is with two events, getting caught by the clsSummaryPresenter. The clsSummaryPresenter looks like this:

Option Explicit

Private WithEvents objSummaryForm As frmMain

Private Sub Class_Initialize()        
    Set objSummaryForm = New frmMain    
End Sub

Private Sub Class_Terminate()        
    Set objSummaryForm = Nothing        
End Sub

Public Sub Show()    
    If Not objSummaryForm.Visible Then
        objSummaryForm.Show vbModeless
        Call ChangeLabelAndCaption("Press Run to Start", "Starting")
    End If    
    With objSummaryForm
        .Top = CLng((Application.Height / 2 + Application.Top) - .Height / 2)
        .Left = CLng((Application.Width / 2 + Application.Left) - .Width / 2)
    End With    
End Sub

Private Sub Hide()    
    If objSummaryForm.Visible Then objSummaryForm.Hide    
End Sub

Public Sub ChangeLabelAndCaption(strLabelInfo As String, strCaption As String)    
    objSummaryForm.InformationText = strLabelInfo
    objSummaryForm.InformationCaption = strCaption
    objSummaryForm.Repaint    
End Sub

Private Sub objSummaryForm_OnRunReport()    
    MainGenerateReport
    Refresh    
End Sub

Private Sub objSummaryForm_OnExit()    
    Hide    
End Sub    

Public Sub Refresh()        
    With objSummaryForm
        .lblInfo = "Ready"
        .Caption = "Task performed"
    End With    
End Sub

Finally, we have the modMain, which is the so-called business logic of the form:

Option Explicit

Private objPresenter   As clsSummaryPresenter

Public Sub MainGenerateReport()    
    objPresenter.ChangeLabelAndCaption "Starting and running...", "Running..."
    GenerateNumbers    
End Sub

Public Sub GenerateNumbers()    
    Dim lngLong         As Long
    Dim lngLong2        As Long    
    tblMain.Cells.Clear    
    For lngLong = 1 To 10
        For lngLong2 = 1 To 10
            tblMain.Cells(lngLong, lngLong2) = lngLong * lngLong2
        Next lngLong2
    Next lngLong    
End Sub

Public Sub ShowMainForm()    
    If (objPresenter Is Nothing) Then
        Set objPresenter = New clsSummaryPresenter
    End If    
    objPresenter.Show    
End Sub

Userform closes after "End Sub" without ever calling "Unload Me"

Answers (1)

Related Questions

Userform closes after &quot;End Sub&quot; without ever calling &quot;Unload Me&quot;

Answers (1)

Related Questions

Userform closes after "End Sub" without ever calling "Unload Me"