用户表单是一个定制的对话框，它使用户数据输入更可控，更易于用户使用。在本章中，您将学习设计一个简单的表单并将数据添加到 Excel 中。
步骤 1 - 按 Alt+F11 导航到 VBA 窗口并导航到“插入”菜单并选择“用户表单”。选择后，将显示用户表单，如以下屏幕截图所示。
Step 2 - 使用给定的控件设计表单。
Step 3 - 添加每个控件后，必须命名控件。标题对应于表单上显示的内容，名称对应于您为该元素编写 VBA 代码时将出现的逻辑名称。
Step 4 - 以下是每个添加控件的名称。
| Control | Logical Name | Caption |
|---|---|---|
| From | frmempform | Employee Form |
| Employee ID Label Box | empid | Employee ID |
| firstname Label Box | firstname | First Name |
| lastname Label Box | lastname | Last Name |
| dob Label Box | dob | Date of Birth |
| mailid Label Box | mailid | Email ID |
| Passportholder Label Box | Passportholder | Passport Holder |
| Emp ID Text Box | txtempid | NOT Applicable |
| First Name Text Box | txtfirstname | NOT Applicable |
| Last Name Text Box | txtlastname | NOT Applicable |
| Email ID Text Box | txtemailid | NOT Applicable |
| Date Combo Box | cmbdate | NOT Applicable |
| Month Combo Box | cmbmonth | NOT Applicable |
| Year Combo Box | cmbyear | NOT Applicable |
| Yes Radio Button | radioyes | Yes |
| No Radio Button | radiono | No |
| Submit Button | btnsubmit | Submit |
| Cancel Button | btncancel | Cancel |
第 5 步- 通过右键单击表单并选择“查看代码”来添加表单加载事件的代码。
Step 6 - 从对象下拉列表中选择“Userform”，然后选择“Initialize”方法，如以下屏幕截图所示。
步骤 7 - 加载表单后，确保清除文本框、填充下拉框并重置单选按钮。
Private Sub UserForm_Initialize()
'Empty Emp ID Text box and Set the Cursor
txtempid.Value = ""
txtempid.SetFocus
'Empty all other text box fields
txtfirstname.Value = ""
txtlastname.Value = ""
txtemailid.Value = ""
'Clear All Date of Birth Related Fields
cmbdate.Clear
cmbmonth.Clear
cmbyear.Clear
'Fill Date Drop Down box - Takes 1 to 31
With cmbdate
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"
End With
'Fill Month Drop Down box - Takes Jan to Dec
With cmbmonth
.AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
.AddItem "OCT"
.AddItem "NOV"
.AddItem "DEC"
End With
'Fill Year Drop Down box - Takes 1980 to 2014
With cmbyear
.AddItem "1980"
.AddItem "1981"
.AddItem "1982"
.AddItem "1983"
.AddItem "1984"
.AddItem "1985"
.AddItem "1986"
.AddItem "1987"
.AddItem "1988"
.AddItem "1989"
.AddItem "1990"
.AddItem "1991"
.AddItem "1992"
.AddItem "1993"
.AddItem "1994"
.AddItem "1995"
.AddItem "1996"
.AddItem "1997"
.AddItem "1998"
.AddItem "1999"
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
.AddItem "2005"
.AddItem "2006"
.AddItem "2007"
.AddItem "2008"
.AddItem "2009"
.AddItem "2010"
.AddItem "2011"
.AddItem "2012"
.AddItem "2013"
.AddItem "2014"
End With
'Reset Radio Button. Set it to False when form loads.
radioyes.Value = False
radiono.Value = False
End Sub
第 8 步- 现在将代码添加到提交按钮。单击提交按钮后，用户应该能够将值添加到工作表中。
Private Sub btnsubmit_Click()
Dim emptyRow As Long
'Make Sheet1 active
Sheet1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = txtempid.Value
Cells(emptyRow, 2).Value = txtfirstname.Value
Cells(emptyRow, 3).Value = txtlastname.Value
Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
Cells(emptyRow, 5).Value = txtemailid.Value
If radioyes.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If
End Sub
第 9 步- 添加一个方法以在用户单击“取消”按钮时关闭表单。
Private Sub btncancel_Click()
Unload Me
End Sub
Step 10 - 单击“Run”按钮执行表单。在表格中输入值，然后单击“提交”按钮。值将自动流入工作表，如以下屏幕截图所示。
原创文章，转载请注明出处：http://b.nwumba.cn/article/37/