VBA 隐藏用户表单但保留输入的数据

VBA Hide User form but retain data entered into it

提问by MattB

I'm back again with what I hope is a fairly easy question.

我又回来了，我希望这是一个相当简单的问题。

I'm attempting to create a user form in VBA. The user will enter certain bits of information into the form, and then close the form. I'd like the user form to retain the data entered after it is closed by the user. I'm treating it as a class module, since techinically they are, or at least that is how I understand it. Here is the code I'm using:

我正在尝试在 VBA 中创建一个用户表单。用户将在表单中输入某些信息，然后关闭表单。我希望用户表单在用户关闭后保留输入的数据。我把它当作一个类模块，因为从技术上讲它们是，或者至少我是这样理解的。这是我正在使用的代码：

In the main sub that displays the user form:

在显示用户表单的主子中：

Sub NonACATMemo()

Dim UserInput As MemoReasons
Set UserInput = New MemoReasons
UserInput.Show

... And then in the user form itself to close it...

...然后在用户表单本身中关闭它...

Private Sub UserForm_Terminate()
MemoReasons.Hide
End Sub

I also call this sub from a command button on the form. The issue I'm running into is that when I use this method, I get an error "Run-time error '402': Must close or hide topmost modal form first." If I use unload me, when I try to get data out of the form it is cleared and I get a "server not available" error or something to that effect.

我还从表单上的命令按钮调用此子程序。我遇到的问题是，当我使用此方法时，出现错误“运行时错误‘402’：必须先关闭或隐藏最顶层的模态窗体。” 如果我使用卸载我，当我尝试从表单中获取数据时，它会被清除，并且出现“服务器不可用”错误或类似的错误。

So, any ideas on hiding a user form but retaining the data inside?

那么，关于隐藏用户表单但保留其中的数据有什么想法吗？

Final couple of notes: This is the only user form in the project, and here is an example of how I'm trying to get data out of it using the Public Property Get method:

最后几个注意事项：这是项目中唯一的用户表单，以下是我如何尝试使用 Public Property Get 方法从中获取数据的示例：

Debug.Print UserInput.EmailFlag
Debug.Print UserInput.ContraFirm
Debug.Print UserInput.MemoReason

Well, I'm all ears if anyone has any suggestions.

好吧，如果有人有任何建议，我会全神贯注。

回答by David Zemens

I've not seen this approach before. Normally, I would just instantiate the form by:

我以前没有见过这种方法。通常，我只会通过以下方式实例化表单：

MemoReasons.Show

MemoReasons.Show

Indeed the _Terminate()event is wiping out the data held in the form. So the solution is to notcall the _Terminate()event from the button-click. Instead, simply hide the form, e.g.:

事实上，该_Terminate()事件正在清除表单中保存的数据。所以解决方案是不要_Terminate()从按钮单击中调用事件。相反，只需隐藏表单，例如：

Sub ShowMemoReasons()
'In a normal code module, this calls the form
' could be run from the macros menu or attached to
' a shape/button/etc on the worksheet.

MemoReasons.Show

End Sub

Put these in the MemoReasonscode module:

将这些放在MemoReasons代码模块中：

Private Sub CommandButton1_Click()  '<-- Rename to handle your button's click event

    MemoReasons.Hide  '## Hides the form but does not release it from memory

End Sub
Private Sub UserForm_Terminate()
'Any events pertaining to the termination of the form object
' otherwise, all form control data will be wiped out when
' this object releases from memory

End Sub

After you do these, if you use the button to HIDEthe form, you can call the ShowMemoReasons()and it should re-display the form, while preserving data that was previously entered in the form.

执行这些操作后，如果您使用按钮隐藏表单，您可以调用ShowMemoReasons()并且它应该重新显示表单，同时保留以前在表单中输入的数据。

If you use the red "X" button or some other event triggers the Terminateevent, you will lose the form data. There are ways to do validation and prevent this with the QueryCloseevent if necessary.

如果您使用红色的“X”按钮或某些其他事件触发了该Terminate事件，您将丢失表单数据。有一些方法可以进行验证并QueryClose在必要时通过事件防止这种情况发生。

Update

更新

I don't think you need to Diman instance of the user form (an exception would be if you will be potentially displaying multipleforms at the same time). Otherwise, declaring UserInputas a public variable is redundant and confusing.

我认为您不需要Dim用户表单的实例（如果您可能同时显示多个表单，则例外）。否则，声明UserInput为公共变量是多余且令人困惑的。

Inicidentally, this is why you're getting the error: Must close or hide topmost modal form first. If you must implement it this way, instead of doing MemoReasons.hideyou should use Me.Hide.

顺便说一句，这就是您收到错误的原因：Must close or hide topmost modal form first. 如果您必须以这种方式实现它，MemoReasons.hide那么您应该使用Me.Hide.

As long as you are only displaying one instance of the form, you can always refer to MemoReasons.propertybecause MemoReasonsis a public object, just like ThisWorkbookor ActiveWorksheet, etc.

只要你只显示表单的一个实例，你总是可以引用MemoReasons.property因为MemoReasons是一个公共对象，就像ThisWorkbook或ActiveWorksheet等。

Instead, you should be able to refer to this object (MemoReasonsis an object) in any subroutine, for example create another one that is not called from the previous subs. Run the sub to show the form, enter in some data, and then hide the form. With the form hidden, then run this subroutine, and you should see the resulting data from the form.

相反，您应该能够MemoReasons在任何子例程中引用此对象（是一个对象），例如创建另一个未被前一个子程序调用的对象。运行 sub 以显示表单，输入一些数据，然后隐藏表单。隐藏表单，然后运行此子例程，您应该会看到来自表单的结果数据。

Sub Test2()
    Debug.Print MemoReasons.EmailFlag
End Sub

回答by Mohamed Boulmers

It's an old topic... hopefully someone will need a help on this.

这是一个古老的话题......希望有人会在这方面需要帮助。

You can do the following :

您可以执行以下操作：

1-Place a Close/Cancel button (you can set Cancel property to True)

1-放置关闭/取消按钮（您可以将取消属性设置为 True）

2-Attach following code to Clickevent

2-将以下代码附加到 Click事件

Private Sub btnClose_Click()
    'Do some stuff if necessary
    Me.Hide
End Sub

3-Attach this code to QueryCloseevent

3-将此代码附加到 QueryClose事件

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then 'vbFormControlMenu = 0 ([X] button on top right), see MSDN
     Cancel = True 'Don't fire Terminate event...
     btnClose_Click '...instead, call my close event handler
   End If
End Sub

You can check the result by placing a debugger breakpoint in UserForm_Initializeevent and it should be fired only for the first time showing the UserForm and thus, granting UserForm state preservation.

您可以通过在UserForm_Initialize事件中放置一个调试器断点来检查结果，它应该只在第一次显示 UserForm 时触发，从而授予 UserForm 状态保留。