用户表单在"End Sub"结束后关闭.无需致电“卸载我"

问题描述

我有一个用户窗体(baseUF)，其中有多个页面和按钮，它们都可以做不同的事情.我有这个baseUF是无模式的，因为我希望用户能够在不关闭用户窗体并丢失他们输入的所有数据的情况下玩纸.但是，我开始遇到一个问题，这可能是由于baseUF的无模式特性造成的.

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.

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

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.

有人知道这个问题可能是什么吗?我很迷路，因为我没有命令可以关闭该子目录中任何位置的baseUF.下面是一些代码来显示正在发生的事情:

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:

此子连接到电子表格上的一个按钮，以打开baseUF(代码在模块中).

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

这是baseUF中的子程序，它调用了一个附加的用户窗体(LoadBox)，这似乎是问题所在.

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

这是一些代码，显示了另外两种用户形式

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

推荐答案

每当表单发生意外情况时，请考虑在立即窗口中编写End并按Enter.它将杀死表单的所有非终止实例以及通常的任何变量，因此就象冷重启到VBA程序一样.

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.

执行完此操作后，考虑使用更干净的解决方案(VBA& UserForms，使用一些OOP. (免责声明-第一篇是我的):

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):

• http://www.vitoshacademy.com/vba-在vba中的完美用户表单/
• https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/
• https://codereview.stackexchange.com/questions /154401/handling-dialog-closure-in-a-vba-user-form

• 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

尽管看起来您可以通过更多的代码来获得相同的结果，但是从长远来看，使用此方法的好处是很多的.

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.

这是OOP模型的一个小例子.假设您有一个这样的用户表单:

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

它只有以下控件:

• btnRun
• btnExit
• lblInfo
• 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看起来像这样:

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

最后，我们有了modMain，它是以下形式的所谓业务逻辑:

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

这篇关于用户表单在"End Sub"结束后关闭.无需致电“卸载我"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！