用户窗体在“结束子"之后关闭从来没有叫过“卸载我"； [英] Userform closes after "End Sub" without ever calling "Unload Me"

问题描述

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

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

有人知道问题可能是什么吗?我很迷茫，因为我没有命令在该潜艇的任何地方关闭 baseUF.下面是一些代码来显示正在发生的事情:

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

Sub Button1_Click()' 显示用户窗体baseUF.Show vbModeless结束子

这是 baseUF 中的子程序，它调用了一个额外的用户表单(LoadBox)，这似乎是问题所在.

私有子 LoadQuery_Click()' 我在这里调暗了一堆东西' 如果没有保存的查询，提醒用户如果 saveSht.Range("B3").Value = "" 那么MsgBox "没有保存的查询！"退出子' 如果只有一个已保存的查询，则将其添加到数组中并弹出允许用户选择要加载的保存的用户表单ElseIf saveSht.Range("B4").Value = "" Thensave_names = saveSht.Range("B3").Value加载框显示' 否则，将所有保存名称添加到数组并弹出该用户表单别的save_names = saveSht.Range(saveSht.Range("B3"),saveSht.Range("B3").End(xlDown)).Value加载框显示万一' 如果用户没有选择要加载的保存，请停止尝试使事情发生如果 load_name = "" 那么' 如果结果为真，用户表单也会在此处关闭退出子万一' 在这里用选定的名字做一堆东西' 并且在此行之后，包含此代码的用户窗体将关闭结束子

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

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

Private Sub UserForm_Initialize()' 在这个表单启动时，用相关的列名填充列表框' 设置位置我.StartUpPosition = 0Me.Top = baseUF.Top + 0.5 * baseUF.Height - 0.5 * Me.HeightMe.Left = baseUF.Left + 0.5 * baseUF.Width - 0.5 * Me.Width使用过滤器选择框' 首先从选定的主表中获取所有列名对于 i = 0 到 baseUF.SelectionBox.ListCount - 1.AddItem baseUF.SelectionBox.List(i)接下来我' 然后从要连接的附加表中获取所有列名If Not IsVariantEmpty(join_table_cols) Then对于 n = 0 到 UBound(join_table_cols)If Not IsEmpty(join_table_cols(n)) Then对于 join_table_cols(n) 中的每个 col_name.AddItem col_name下一个万一下一个万一结束于结束子私有子 OkButton_Click()' 初始化变量将 tb 调暗为 MSForms.TextBox暗淡 arr() 作为字符串暗淡 str 作为字符串' tb 是将列名粘贴到的文本框对象设置 tb = baseUF.MultiPage1.Pages(baseUF.MultiPage1.Value).Controls(Me.Tag)' 根据某种逻辑设置 str' 这实际上是它被发送的地方tb.Value = str' 并关闭表格卸载我结束子

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

Private Sub UserForm_Initialize()' 在初始化时，使用电子表格中存在的所有保存名称填充组合框' 设置位置我.StartUpPosition = 0Me.Top = baseUF.Top + 0.5 * baseUF.Height - 0.5 * Me.HeightMe.Left = baseUF.Left + 0.5 * baseUF.Width - 0.5 * Me.Width带负载梳' 如果存在多个存档，遍历数组并添加每个If IsArray(save_names) Then对于 save_names 中的每个 saved_name.AddItem saved_name下一个' 否则只需添加一个别的.AddItem 保存名称万一结束于结束子私有子 LoadButton_Click()' 当用户点击加载按钮时，首先检查他们是否真的选择了任何东西如果 LoadComb.Value = "" 那么'如果他们没有，就对他们大喊大叫MsgBox "没有选择保存的查询！"别的' 否则，将名称保存到全局变量load_name = LoadComb.Value万一' 关闭表格卸载我结束子

解决方案

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

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

• 它只有以下控件:

  • btnRun
  • btn退出
  • lblInfo
  • frmMain(类)

  表单代码如下:

  选项显式公共事件 OnRunReport()公共事件 OnExit()公共属性获取 InformationText() 作为字符串信息文本 = lblInfo.Caption结束属性公共属性 Let InformationText(ByVal value As String)lblInfo.Caption = 值结束属性公共属性获取 InformationCaption() 作为字符串信息标题 = 标题结束属性公共属性 Let InformationCaption(ByVal value As String)标题 = 价值结束属性私有子 btnRun_Click()RaiseEvent OnRunReport结束子私有子 btnExit_Click()RaiseEvent OnExit结束子Private Sub UserForm_QueryClose(取消为整数，CloseMode 为整数)如果 CloseMode = vbFormControlMenu 则取消 = 真隐藏万一结束子

  表单包含两个事件，被 clsSummaryPresenter 捕获.clsSummaryPresenter 看起来像这样:

  选项显式Private WithEvents objSummaryForm As frmMain私有子类_Initialize()设置 objSummaryForm = New frmMain结束子私有子类_Terminate()设置 objSummaryForm = 无结束子公共子节目()If Not objSummaryForm.Visible ThenobjSummaryForm.Show vbModeless调用 ChangeLabelAndCaption("Press Run to Start", "Starting")万一使用 objSummaryForm.Top = CLng((Application.Height/2 + Application.Top) - .Height/2).Left = CLng((Application.Width/2 + Application.Left) - .Width/2)结束于结束子私人子隐藏()If objSummaryForm.Visible Then objSummaryForm.Hide结束子公共子 ChangeLabelAndCaption(strLabelInfo 作为字符串，strCaption 作为字符串)objSummaryForm.InformationText = strLabelInfoobjSummaryForm.InformationCaption = strCaptionobjSummaryForm.Repaint结束子私有子 objSummaryForm_OnRunReport()主生成报告刷新结束子私有子 objSummaryForm_OnExit()隐藏结束子公共子刷新()使用 objSummaryForm.lblInfo = "准备好".Caption = "已执行的任务"结束于结束子

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

  选项显式私有 objPresenter 作为 clsSummaryPresenter公共子 MainGenerateReport()objPresenter.ChangeLabelAndCaption "正在启动并运行...", "正在运行..."生成数字结束子公共子 GenerateNumbers()调暗只要长Dim lngLong2 只要tblMain.Cells.Clear对于 lngLong = 1 到 10对于 lngLong2 = 1 到 10tblMain.Cells(lngLong, lngLong2) = lngLong * lngLong2下一个 lngLong2下一个 lngLong结束子公共子 ShowMainForm()如果(objPresenter 什么都没有)那么设置 objPresenter = 新的 clsSummaryPresenter万一objPresenter.Show结束子

  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
  

  解决方案

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

  • 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.

  ---

  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
  

  这篇关于用户窗体在“结束子"之后关闭从来没有叫过“卸载我"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！