I have a table of employees on a Staff Details sheet with the following data:

The Role is determined through data validation, the Discipline is looked up from a table, as is the Dev Role, and the Employment Status is also determined through data validation.

On a different sheet called Staff Salaries I have:

The user currently enters the data for a new starter on Staff Details and then enters the details for their salary on the Staff Salaries sheet. This is obviously error prone so I was looking in to using a data entry form. I have the form working for entering data for the Staff Details sheet but it's lacking the ability to have data validation dropdowns for the Role and Employment Status, is it possible to add these or is there a workaround?

I'd also like the form to include an additional entry for Salary and then inject the following specific values from the form in to the Staff Salaries sheet when adding the new employee to the Staff Details sheet, Employee in to Employee, Start Date in to the Salary Start Date column and salary in to the Salary column. How would I go about doing this with forms or is there a better way?

CodePudding user response：

It doesn't seem to be possible to add data valdation dropdowns using the built in data entry form in excel so I opted for the VBA custom form building option which also allowed me to send the data to the two sheets.

To create the foundations of the vba user form I followed the followng tutorial:

The form has an initialise function which populates the two combo boxes from named ranges for the Role and Employment Status, sets the date format for the Employment Start Date text box and sets the cursor to the Employee Name entry field.

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

The following code runs when clicking add employee :

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

It initially runs some sanity checking to ensure the user has input valid data before adding new rows to the bottom of the Staff Details and Staff Salaries tables and setting the specified column values based on the entered fields from the form. Finally the form is cleared, and the focus is set to the employee name field ready for another entry.

The close button has the following function to close the form:

Private Sub CmdClose_Click()
    Unload Me
End Sub