它似乎不可能添加数据验证下拉列表使用内置的数据输入表单在excel中，所以我选择了VBA自定义表单构建选项，这也允许我发送数据到两个工作表。
为了创建vba用户表单的基础，我遵循了以下教程：VBA User Form with Dropdown lists
表单I名为NewEmployeeForm，我在Staff Details工作表上创建了一个按钮，当按下该按钮时，将触发以下宏将其打开：

Sub OpenAddEmployeeForm_Clicked()
    NewEmployeeForm.Show
End Sub

表格如下：

该表单具有一个初始化功能，该功能从Role和Employment Status的命名范围填充两个组合框，设置Employment Start Date文本框的日期格式，并将光标设置到Employee Name输入字段。

Private Sub UserForm_Initialize()
    
    ComboBoxRole.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("RoleList").RefersToRange)
    ComboBoxStatus.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("EmploymentStatusList").RefersToRange)
    
    Me.TxtStartDate.Value = Format(Date, "dd/mm/yyyy")
    Me.TxtName.SetFocus

End Sub

单击“添加雇员”时将运行以下代码：

Private Sub CmdAdd_Click()

    Dim Wb As Workbook: Set Wb = ThisWorkbook ' workbook containing this code
    Dim bClearForm As Boolean
    bClearForm = False
        
    'check for a valid employee name
    If Trim(Me.TxtName.Value) = "" Then
      Me.TxtName.SetFocus
      MsgBox "Please enter an Employee Name"
      Exit Sub
    End If
    
    'check for a valid Role
    If Trim(Me.ComboBoxRole.Value) = "" Then
      Me.ComboBoxRole.SetFocus
      MsgBox "Please enter a valid role"
      Exit Sub
    End If
    
    'check for a valid Date
    If Trim(Me.TxtStartDate.Value) = "" Then
      Me.TxtStartDate.SetFocus
      MsgBox "Please enter a valid date"
      Exit Sub
    End If
        
    'check for a valid Salary
    If Trim(Me.TxtSalary.Value) = "" Then
      Me.TxtSalary.SetFocus
      MsgBox "Please enter a valid salary"
      Exit Sub
    End If
        
    'check for a valid Salary
    If Trim(Me.ComboBoxStatus.Value) = "" Then
      Me.ComboBoxStatus.SetFocus
      MsgBox "Please enter a valid status"
      Exit Sub
    End If
        
    If Wb.WorkSheetExists("Staff Details", Wb) And Wb.WorkSheetExists("Staff Salaries", Wb) Then
        
        'Add row to Staff Details Table
        Dim SheetStaffDetails As Worksheet
        Set SheetStaffDetails = Wb.Worksheets("Staff Details")
        
        Dim tblStaffDetails As ListObject
        Set tblStaffDetails = SheetStaffDetails.ListObjects("StaffDetailsTbl")
        
        'Add Values to table
        Dim NewStaffDetailsRow As ListRow
        Set NewStaffDetailsRow = tblStaffDetails.ListRows.Add
        With NewStaffDetailsRow
            .Range(tblStaffDetails.ListColumns("Employee").Index) = Me.TxtName.Value
            .Range(tblStaffDetails.ListColumns("Role").Index) = Me.ComboBoxRole.Value
            .Range(tblStaffDetails.ListColumns("Employment Start Date").Index) = CDate(Me.TxtStartDate.Value)
            .Range(tblStaffDetails.ListColumns("Employment Status").Index) = Me.ComboBoxStatus.Value
        End With
             
        'Add row to Staff Salaries Table
        Dim SheetStaffSalaries As Worksheet
        Set SheetStaffSalaries = Wb.Worksheets("Staff Salaries")
        
        Dim tblStaffSalaries As ListObject
        Set tblStaffSalaries = SheetStaffSalaries.ListObjects("EmployeeSalaryTbl")
        
        'Add Values to table
        Dim NewStaffSalariesRow As ListRow
        Set NewStaffSalariesRow = tblStaffSalaries.ListRows.Add
        With NewStaffSalariesRow
            .Range(tblStaffSalaries.ListColumns("Employee").Index) = Me.TxtName.Value
            .Range(tblStaffSalaries.ListColumns("Salary Start Date").Index) = CDate(Me.TxtStartDate.Value)
            .Range(tblStaffSalaries.ListColumns("Salary").Index) = Me.TxtSalary.Value
        End With
        
        bClearForm = True
        
    End If

    'Valid Row added so clear the data ready for a new entry
    If bClearForm Then
        Me.TxtName.Value = ""
        Me.ComboBoxRole.Value = ""
        Me.TxtStartDate.Value = Format(Date, "dd/mm/yyyy")
        Me.TxtSalary.Value = ""
        Me.ComboBoxStatus.Value = ""
        Me.TxtName.SetFocus
    End If

End Sub

在向Staff Details和Staff Salaries表的底部添加新行并根据表单中输入的字段设置指定的列值之前，它首先运行一些健全性检查以确保用户输入了有效的数据。最后，清除表单，并将焦点设置为雇员姓名字段，准备进行另一项输入。
关闭按钮具有以下关闭表单的功能：

Private Sub CmdClose_Click()
    Unload Me
End Sub