用户表单（ Userforms）

优质

小牛编辑

129浏览

2023-12-01

User Form是一个自定义的对话框，使用户数据条目更易于控制，更易于用户使用。在本章中，您将学习如何设计一个简单的表单并将数据添加到excel中。

Step 1 - 按Alt + F11导航到VBA窗口，导航到“插入”菜单，然后选择“用户表单”。选择后，将显示用户表单，如以下屏幕截图所示。

Step 2 - 使用给定的控件设计表单。

Step 3 - 添加每个控件后，必须命名控件。标题对应于表单上显示的内容，名称对应于为该元素编写VBA代码时将出现的逻辑名称。

Step 4 - 以下是针对每个添加的控件的名称。

控制	逻辑名称	字幕
From	frmempform	员工表格
Employee ID Label Box	empid	员工ID
firstname Label Box	firstname	First Name
lastname Label Box	lastname	Last Name
dob Label Box	dob	出生日期
mailid Label Box	mailid	电子邮件ID
Passportholder Label Box	Passportholder	Passport Holder
Emp ID文本框	txtempid	不适用
First Name Text Box	txtfirstname	不适用
Last Name Text Box	txtlastname	不适用
电子邮件ID文本框	txtemailid	不适用
日期组合框	cmbdate	不适用
月组合框	cmbmonth	不适用
年组合框	cmbyear	不适用
Yes Radio Button	radioyes	Yes
No Radio Button	radiono	No
提交按钮	btnsubmit	Submit
取消按钮	btncancel	Cancel

Step 5 - 通过右键单击表单并选择“查看代码”，为表单加载事件添加代码。

Step 6 - 从对象下拉列表中选择“Userform”，然后选择“Initialize”方法，如以下屏幕截图所示。

Step 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

Step 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

Step 9 - 添加方法以在用户单击“取消”按钮时关闭表单。

Private Sub btncancel_Click()
   Unload Me
End Sub

Step 10 - 单击“运行”按钮执行表单。在表单中输入值，然后单击“提交”按钮。这些值将自动流入工作表，如以下屏幕截图所示。