vba - 用户窗体在 "End Sub"之后关闭，而无需调用 "Unload Me"

Question

我有一个用户表单(baseUF)，它有多个页面和按钮，它们都执行不同的操作。我有这个 baseUF 是无模式的，因为我希望用户能够在不关闭用户窗体并丢失他们输入的所有数据的情况下使用该工作表。然而，我开始遇到一个问题，这可能是由于 baseUF 的无模式性质造成的。

还有其他用户窗体可以从 baseUF 调用。通过双击文本框可以毫无问题地执行。但是，单击按钮后会加载另一个用户窗体。一旦该按钮单击 sub 完成，baseUF 将在 Exit Sub 或 End Sub 行后关闭。我不记得过去发生过这种情况，并且任何其他按钮点击子也不会发生这种情况。

有人知道问题出在哪里吗？我很迷茫，因为我没有命令来关闭该子系统中任何地方的 baseUF。下面是一些代码来显示正在发生的事情:

此子连接到电子表格上的按钮以打开 baseUF(代码位于模块中)。

Sub Button1_Click()

' show the userform
baseUF.Show vbModeless

End Sub

这是 baseUF 中的子程序，它调用一个附加的用户表单(LoadBox)，这似乎是问题所在。

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

编辑:这里是一些显示其他两个用户表单的代码

这是双击文本框后调用的没有问题的用户窗体

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

这是用户表单中存在问题的代码

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

Answer 1

每当表单发生意外情况时，请考虑在立即窗口中写入 End 并按 Enter 键。它将杀死表单的所有未杀死实例以及通常的任何变量，因此它就像 VBA 程序的冷重启。

完成此操作后，最好考虑使用一些 OOP 的更简洁的解决方案，涉及 VBA 和用户窗体。 (免责声明 - 第一篇文章是我的):

• http://www.vitoshacademy.com/vba-the-perfect-userform-in-vba/
• https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/
• https://codereview.stackexchange.com/questions/154401/handling-dialog-closure-in-a-vba-user-form

虽然看起来您可以使用更多代码获得相同的结果，但从长远来看，使用这种方法的好处是相当多的。

<小时/>

这是 OOP 模型的一个小示例。想象一下您有一个像这样的用户表单:

它只有以下控件:

• btn运行
• btn退出
• lbl信息
• frmMain(类)

表单中的代码如下:

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

该表单有两个事件，被 clsSummaryPresenter 捕获。 clsSummaryPresenter 如下所示:

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

最后，我们有 modMain，也就是所谓的表单业务逻辑:

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