用户表单是一个自定义对话框,使用户数据输入更易于控制,更易于用户使用.在本章中,您将学习如何设计一个简单的表单并将数据添加到Excel中.
步骤1 : 按Alt + F11导航到VBA窗口,导航到"插入"菜单,然后选择"用户表单".选择后,将显示用户表单,如以下屏幕截图所示.
第2步 : 使用给定的控件设计表单.
第3步 : 添加每个控件后,必须命名控件.标题对应于表单上显示的内容,名称对应于为该元素编写VBA代码时将出现的逻辑名称.
步骤4 : 以下是针对每个添加的控件的名称.
Control | 逻辑名称 | 标题 |
---|---|---|
From | frmempform | 员工表格 |
员工ID标签栏 | empid | 员工ID |
firstname标签 | firstname | 名字 |
姓氏标签 | 姓氏 | 姓氏 |
dob标签 | dob | 出生日期 |
mailid标签 | mailid | 电子邮件ID |
Passportholder标签盒 | Passportholder | Passport Holder |
Emp ID文本框 | txtempid | 不适用 |
名字T ext box | txtfirstname | NOT适用 |
姓氏文本框 | txtlastname | NOT适用 |
电子邮件ID文本框 | txtemailid | 不适用 |
日期组合框 | cmbdate | 不适用 |
月份组合框 | cmbmonth | 不适用 |
年组合框 | cmbyear | 不适用 |
是单选按钮 | radioyes | 是 |
没有单选按钮 | radiono | 否 |
提交按钮 | btnsubmit | 提交 |
取消按钮 | btncancel | 取消 |
Ste p 5 : 通过右键单击表单并选择"查看代码"来添加表单加载事件的代码.
步骤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
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
第9步 : 当用户单击"取消"按钮时,添加一个关闭表单的方法.
Private Sub btncancel_Click() Unload Me End Sub
步骤10 : 单击"运行"按钮执行表单.在表单中输入值,然后单击"提交"按钮.这些值将自动流入工作表,如以下屏幕截图所示.