vba 如何加载和卸载用户表单

How to load and unload a Userform

提问by SAFD

Where should I put Load and Unload frm1 (Userform name is frm1) and where should I put Me.Show and Me.Hide?

我应该把加载和卸载 frm1（用户表单名称是 frm1）放在哪里，我应该把 Me.Show 和 Me.Hide 放在哪里？

The (x) button within the UserForm doesn't work.

用户窗体中的 (x) 按钮不起作用。

My Load and Unload is within the Active-X command button code on Sheet1:

我的加载和卸载位于 Sheet1 上的 Active-X 命令按钮代码中：

 Private Sub cmdb1_Click()
     Load frm1
     Unload frm1
 End Sub

This way my UserForm is initialized and I can run the code

这样我的用户窗体就被初始化了，我可以运行代码

Private Sub Userform_Initialize()
    'Some other code that Works...  
    frm1.Show
End Sub

that shows my Userform. Now I have a command button in my Userform that has the code

显示我的用户表单。现在我的用户窗体中有一个带有代码的命令按钮

Private Sub cmdbClose_Click()
    Me.Hide
End Sub

which I use to hide the sub, upon which the last line within cmdb1_Click() is executed and UserForm is unloaded. This Works.

我用它来隐藏 sub，在它上面执行 cmdb1_Click() 中的最后一行并卸载 UserForm。这有效。

However when I press the (x) button in my UserForm, the following error appears

但是，当我按下用户窗体中的 (x) 按钮时，会出现以下错误

Debugger says error lies within cmdb1_Click(). I've tried adding a sub called UserForm_QueryClose(), but the error persists. If I'd have to guess, I'd say the error is caused by the way I handle Load and Unload, thus by cmdb1_Click().

调试器说错误在 cmdb1_Click() 中。我尝试添加一个名为 UserForm_QueryClose() 的子程序，但错误仍然存​​在。如果我不得不猜测，我会说错误是由我处理加载和卸载的方式引起的，因此是由 cmdb1_Click() 引起的。

EDIT:

编辑：

My problem is solved. ShowUserform and cmdbClose_Click contain the code CallumDA suggests. My command button now has:

我的问题解决了。ShowUserform 和 cmdbClos​​e_Click 包含 CallumDA 建议的代码。我的命令按钮现在有：

Private Sub cmdb1_Click()
    Load frm1
    Call ShowUserform
End Sub

采纳答案by CallumDA

Put this in a standard module and link it up to the button you use to show the userform

把它放在一个标准模块中，并将它链接到你用来显示用户表单的按钮

Sub ShowUserform
    frm1.Show
End Sub

And then in the userform

然后在用户表单中

Private Sub cmdbClose_Click()
    Unload Me
End Sub 

回答by Excel Developers

I recommend that you create an instance of your userform:

我建议您创建一个用户表单实例：

Dim MyDialog As frm1

Set MyDialog = New frm1    'This fires Userform_Initialize

You can then easily check whether the form is loaded before attempting to unload it:

然后，您可以在尝试卸载之前轻松检查表单是否已加载：

If Not MyDialog Is Nothing Then
    Unload MyDialog
End If

I also recommend that you don't call the Show method from the form's Initialize event. Think of your userform as just another object in Excel and manage it from your main code body.

我还建议您不要从窗体的 Initialize 事件中调用 Show 方法。将您的用户表单视为 Excel 中的另一个对象，并从您的主要代码体中管理它。

Also, I wouldn't unload it in the cmdbClose_Click event as suggested by CallumDA (though that works fine to solve the current issue). Often you will need to be able to refer to values on your form from your main code body, and if you unload it they won't be available. Instead, keep it like you had it in the first place:

此外，我不会按照 CallumDA 的建议在 cmdbClos​​e_Click 事件中卸载它（尽管这可以很好地解决当前问题）。通常，您需要能够从主代码体中引用表单上的值，如果您卸载它，它们将不可用。相反，请保持原样：

Private Sub cmdbClose_Click()
    Me.Hide
End Sub

So your main code body (in your activeX button) will look something like this:

所以你的主要代码体（在你的 activeX 按钮中）看起来像这样：

Dim MyDialog as frm1

Set MyDialog = New frm1      'This fires Userform_Initialize
'Place any code you want to execute between the Initialize and Activate events of the form here
MyDialog.Show           'This fires Userform_Activate
'When the close button is clicked, execution will resume on the next line:
SomeVariable = MyDialog.SomeControl.Value
'etc.

If Not MyDialog Is Nothing Then
    Unload MyDialog
End If

You can also catch the event that fires when a user clicks the "X" on the form, and prevent the form from being unloaded:

您还可以捕获当用户单击表单上的“X”时触发的事件，并防止表单被卸载：

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

Lastly, often you need a Cancel button on the form. The way I handle this is to create a "Cancelled" property in the form's code-behind:

最后，通常您需要表单上的“取消”按钮。我处理这个问题的方法是在表单的代码隐藏中创建一个“取消”属性：

Public Cancelled as Boolean
'(Note You can create additional properties to store other values from the form.)

In the Cancel button's click event:

在取消按钮的点击事件中：

Private Sub cmdbCancel_Click()
    Me.Cancelled = True
    Me.Hide
End Sub

And in the main code body:

在主代码体中：

Dim MyDialog as frm1

Set MyDialog = New frm1
MyDialog.Show

If Not MyDialog.Cancelled Then
    SomeVariable = MyDialog.SomeControl.Value
    SomeOtherVariable = MyDialog.SomeOtherProperty
    'etc.
End If

If Not MyDialog Is Nothing Then
    Unload MyDialog
End If

(I know the above is not strictly the correct way to declare a property, but this will work fine. You can make it read-only in the usual way if you wish.)

（我知道上面的方法严格来说并不是声明属性的正确方法，但这会正常工作。如果您愿意，可以按照通常的方式将其设置为只读。）

回答by MikeK

I struggled for years with forms in Excel. I could never understand how an object (a form) could be destroyed but still have it's code running.

我多年来一直在 Excel 中使用表单。我永远无法理解一个对象（一个表单）是如何被销毁的，但它的代码仍然在运行。

I now use this code on every form that has [OK] and [Cancel] buttons, as it allows you to control the [OK], [Cancel] and [X] being clicked by the user, in the main code:

我现在在每个具有 [确定] 和 [取消] 按钮的表单上使用此代码，因为它允许您在主代码中控制用户单击的 [确定]、[取消] 和 [X]：

Option Explicit
Private cancelled As Boolean

Public Property Get IsCancelled() As Boolean
    IsCancelled = cancelled
End Property

Private Sub OkButton_Click()
    Hide
End Sub

Private Sub CancelButton_Click()
    OnCancel
End Sub

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

Private Sub OnCancel()
    cancelled = True
    Hide
End Sub

You can then use the form as an instance as follows:

然后，您可以将表单用作实例，如下所示：

With New frm1    
    .Show
    If Not .IsCancelled Then
        ' do your stuff ...
    End If
End With

or you can use it as an object (variable?) as noted above such as:

或者您可以将其用作对象（变量？），如上所述，例如：

Dim MyDialog As frm1

Set MyDialog = New frm1    'This fires Userform_Initialize

Then similar instance noted above.

然后是上面提到的类似实例。

This is all based on an excellent article by RubberDuckwhich goes into more detail and explains the code given above.

这一切都基于RubberDuck 的一篇优秀文章，该文章更详细地解释了上面给出的代码。