Automated Data Entry Form in Excel

Updated: Feb 7, 2023

In this tutorial, we will learn how to write VBA code that automates adding, updating and deleting employee data through an Excel form.

What you need:

Excel

How to Video

I demonstrated the steps on how to create the Excel automated form in this video. Feel free to watch and don't forget to subscribe.

Part 1

Part 2 Source Code:

Declare Global Variables

Public shDbase As Worksheet
Public shEform As Worksheet
Public fname As Variant
Public lname As Variant
Public age As Variant
Public gndr As Variant
Public post As Variant
Public myArray As Variant
Public eid As Variant
Public row_ As String
Public empStatus As String
Public eRange As Range

Add New Employee Data

Sub Add_Emp_Details()
'add new employee details to database

Set shEform = Worksheets("Emp Form")
Set shDbase = Worksheets("Database")

Application.ScreenUpdating = False

    fname = shEform.Range("D7").Value
    lname = shEform.Range("D8").Value
    age = shEform.Range("D9").Value
    gndr = shEform.Range("D10").Value
    post = shEform.Range("D11").Value

    'validate fields are not empty
    'if empty display error message
    If IsEmpty(fname) Then
        shEform.Range("C18").Value = "First name is empty."
    ElseIf IsEmpty(lname) Then
        shEform.Range("C18").Value = "Last name is empty."
    ElseIf IsEmpty(age) Then
        shEform.Range("C18").Value = "Age is empty."
    ElseIf IsEmpty(gndr) Then
        shEform.Range("C18").Value = "Gender is empty."
    ElseIf IsEmpty(post) Then
        shEform.Range("C18").Value = "Position is empty."
    Else
        'if not delete error message
        shEform.Range("C18").Value = ""
        
        'find the next empty row
        'and add employee records
        lRow = shDbase.Range("B" & Rows.Count).End(xlUp).Row + 1
        
        'create EID
        eid = "E00" & lRow
        
        'store field values in array
        empStatus = "Active"
        myArray = Array(eid, fname, lname, age, gndr, post)
 
        
        shDbase.Range("A" & lRow & ":" & "F" & lRow).Value = myArray
        shDbase.Range("G" & lRow).Value = empStatus
        
        
        'display message box
        MsgBox "Employee recorded successfully."
        
        'delete field values
        shEform.Range("D7:D11").Value = ""
    
    End If
    
 Application.ScreenUpdating = True

End Sub

Retrieve Employee Record

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$5" Then
         If IsEmpty(Range("D5").Value) Then
            'do nothing
         Else
            Call Find_Emp_Details
         End If
    End If
End Sub

Sub Find_Emp_Details()
    
    Set shEform = Worksheets("Emp Form")
    Set shDbase = Worksheets("Database")
    
    eid = shEform.Range("D5").Value
    
    Set eRange = shDbase.Range("A:A").Find(eid, lookat:=xlPart)
    
    If Not eRange Is Nothing Then
        row_ = Range(eRange.Address).Row
        shEform.Range("D7:D11").Value = WorksheetFunction.Transpose _
        (shDbase.Range("B" & row_ & ":" & "F" & row_))
    Else
    
        MsgBox "No records found for the given EID"
        shEform.Range("D5:D11").Value = ""
        
    End If

End Sub

Update Employee Record

Sub Update_Emp_Details()

Set shEform = Worksheets("Emp Form")
Set shDbase = Worksheets("Database")

Application.ScreenUpdating = False

    fname = shEform.Range("D7").Value
    lname = shEform.Range("D8").Value
    age = shEform.Range("D9").Value
    gndr = shEform.Range("D10").Value
    post = shEform.Range("D11").Value

    eid = shEform.Range("D5").Value

    Set eRange = shDbase.Range("A:A").Find(eid, lookat:=xlPart)
    
    If Not eRange Is Nothing Then
    
    row_ = Range(eRange.Address).Row
   
        If IsEmpty(eid) Then
            shEform.Range("C18").Value = "Employee ID is empty."
        ElseIf IsEmpty(fname) Then
            shEform.Range("C18").Value = "First name is empty."
        ElseIf IsEmpty(lname) Then
            shEform.Range("C18").Value = "Last name is empty."
        ElseIf IsEmpty(age) Then
            shEform.Range("C18").Value = "Age is empty."
        ElseIf IsEmpty(gndr) Then
            shEform.Range("C18").Value = "Gender is empty."
        ElseIf IsEmpty(post) Then
            shEform.Range("C18").Value = "Position is empty."
        Else
            shEform.Range("C18").Value = ""
        
        myArray = Array(eid, fname, lname, age, gndr, post)
 
        shDbase.Range("A" & row_ & ":" & "F" & row_).Value = myArray
        
        MsgBox "Employee updated successfully."
        
        shEform.Range("D5:D11").Value = ""
        
        End If
        
     Else
         'do nothing
    
     End If
        
 Application.ScreenUpdating = True

End Sub

Delete or Deactivate Employee

Sub Delete_Emp_Details()

Set shEform = Worksheets("Emp Form")
Set shDbase = Worksheets("Database")

Application.ScreenUpdating = False


    eid = shEform.Range("D5").Value

    Set eRange = shDbase.Range("A:A").Find(eid, lookat:=xlPart)
    
    If Not eRange Is Nothing Then
    
    row_ = Range(eRange.Address).Row
   
        If IsEmpty(eid) Then
            shEform.Range("C18").Value = "Employee ID is empty."
        Else
            shEform.Range("C18").Value = ""
        
        empStatus = "Inactive"
        shDbase.Range("G" & row_).Value = empStatus
        
        MsgBox "Employee deleted successfully."
        
        shEform.Range("D5:D11").Value = ""
        
        End If
        
     Else
         MsgBox "No records found for the given EID"
    
     End If
        
 Application.ScreenUpdating = True


End Sub

Clear Form

Sub Clear_Form()

    Set shEform = Worksheets("Emp Form")
    shEform.Range("D5:D11").Value = ""

End Sub

CODE WITH

EMILY