在 VBA 中重置表单
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12822301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Resetting form in VBA
提问by methuselah
I have a VBA form with a variety of selection options including drop downs, text fields, checkboxes and radios.
我有一个 VBA 表单，其中包含多种选择选项，包括下拉菜单、文本字段、复选框和收音机。
I just wanted to know about the best way to clear all these fields with a button press. A friend of mine has tried to help by emailing me the code below but unfortunately it doesn't work, I have checked the variable names.
我只是想知道通过按下按钮清除所有这些字段的最佳方法。我的一个朋友试图通过将下面的代码通过电子邮件发送给我来提供帮助，但不幸的是它不起作用，我检查了变量名称。
Any advice on how I can improve it?
关于我如何改进它的任何建议？
Thanks in advance.
提前致谢。
Private Sub btnReset_Click()
Unload Me
UserForm.Show
End Sub
Here is the other code for the userform.
这是用户表单的其他代码。
Dim DeptCode 'Holds department code
Private Sub UserForm_Initialize()
Dim c_deptCode As Range
Dim c_deptName As Range
Dim deptCodes As Variant
Dim deptNames As Variant
Dim ws_dept As Worksheet
Set ws_dept = Worksheets("lookupDept")
' Assign each range to an array containing the values
deptCodes = Choose(1, ws_dept.Range("deptCode"))
deptNames = Choose(1, ws_dept.Range("deptName"))
For i = 1 To ws_dept.Range("deptCode").Rows.Count
' Create the combined name (code + space + name)
CombinedName = deptCodes(i, 1) & " - " & deptNames(i, 1)
cbo_deptCode.AddItem CombinedName
Next i
End Sub
回答by Dick Kusleika
I think when it hits the Unload Me line, code execution stops and that's why it's not working for you. Here's a generic event procedure to reset all (most) of the controls on the form.
我认为当它到达 Unload Me 行时，代码执行停止，这就是它对您不起作用的原因。这是重置窗体上所有（大多数）控件的通用事件过程。
Private Sub cmdReset_Click()
Dim ctl As MSForms.Control
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.Value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl
End Sub
It doesn't repopulate the ComboBoxes and ListBoxes, just clears the selection, which is what I assume you want.
它不会重新填充 ComboBoxes 和 ListBoxes，只是清除选择，这就是我假设您想要的。
回答by Johan Godfried
I know this question is almost 2 years old BUT I was looking for an answer like this. However, I am using Access 2010 and discovered the function did not work entirely as expected:
我知道这个问题已经有将近 2 年的历史了，但我一直在寻找这样的答案。但是，我正在使用 Access 2010 并发现该功能没有完全按预期工作：
- ctl can be Dim-ed simply as Control
- For a textbox, the ctl.Text property can only be assigned to if the control has focus (use ctl.Value instead)
- If an OptionButton is part of an OptionGroup it can not be assigned a value
- ctl 可以简单地作为 Control 变暗
- 对于文本框，只有在控件具有焦点时才能分配 ctl.Text 属性（改用 ctl.Value）
- 如果 OptionButton 是 OptionGroup 的一部分，则不能为其分配值
So with these issues in mind, here is my rewritten function:
因此，考虑到这些问题，这是我重写的函数：
Private Sub resetForm()
Dim ctl As Control ' Removed MSForms.
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.value = ""
Case "CheckBox", "ToggleButton" ' Removed OptionButton
ctl.value = False
Case "OptionGroup" ' Add OptionGroup
ctl = Null
Case "OptionButton" ' Add OptionButton
' Do not reset an optionbutton if it is part of an OptionGroup
If TypeName(ctl.Parent) <> "OptionGroup" Then ctl.value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl
End Sub
回答by dRay
Microsoft has this documented fairly well now for latest version of Access. It looks like some of the answers above refer to older versions. The code below is working in my app perfectly. I only included the control types I need but you can find documentation for any others in the Microsoft links below.
对于最新版本的 Access，微软现在已经很好地记录了这一点。上面的一些答案似乎是指旧版本。下面的代码在我的应用程序中完美运行。我只包含了我需要的控件类型，但您可以在下面的 Microsoft 链接中找到任何其他控件类型的文档。
参考：https: //msdn.microsoft.com/en-us/vba/access-vba/articles/textbox-controltype-property-access?f =255 &MSPPError =-2147217396
Dim ctl As Control
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox
.Value = ""
Case acCheckBox
.Value = False
Case acComboBox
.SetFocus
.SelText = ""
Case acListBox
.Value = Null
End Select
End With
Next ctl
Note that for the combo box you have to set the focus there before setting the value. Ref: https://msdn.microsoft.com/en-us/vba/access-vba/articles/combobox-seltext-property-access
请注意，对于组合框，您必须在设置值之前在那里设置焦点。参考：https: //msdn.microsoft.com/en-us/vba/access-vba/articles/combobox-seltext-property-access
回答by Erik I
Adding to the most recent answer - if some of your controls have defaults, you can use
添加到最新的答案 - 如果您的某些控件具有默认值，则可以使用
ctl.Value = ctl.DefaultValue
ctl.Value = ctl.DefaultValue
Which works for me, at least for checkboxes and combo boxes.
这对我有用，至少适用于复选框和组合框。
回答by S. Stoyanov
You may try this:
你可以试试这个：
Private Sub btnReset_Click()
Call UserForm_Initialize
End Sub
