VBA - 用户窗体

用户窗体是一个自定义构建的对话框，它使用户数据输入更易于控制，并且更易于用户使用。 在本章中，您将学习设计一个简单的窗体并将数据添加到 Excel 中。

步骤 1 - 通过按 Alt+F11 导航到 VBA 窗口，然后导航到“插入”菜单并选择“用户窗体”。 选择后，用户窗体将显示如下面的屏幕截图所示。

步骤 2 - 使用给定的控件设计窗体。

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

步骤 4 - 以下是每个添加控件的名称。

步骤 5 - 通过右键单击窗体并选择“查看代码”来添加窗体加载事件的代码。

步骤 6 - 从对象下拉列表中选择“用户窗体”，然后选择“初始化”方法，如下面的屏幕截图所示。

步骤 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

步骤 10 - 通过单击“运行”按钮执行窗体。 将值输入窗体，然后单击“提交”按钮。 值将自动流入工作表，如下面的屏幕截图所示。