Advance Data Entry Form

How to Create a Data Entry Form in Excel (Step-by-step Guide)

In this page, we will discuss a fully functional Data Entry form in Excel with Add, Search, Edit, Delete and Reset features.

You will learn

• How to design user interface
• How to write code
• Adding Module
• Declaring variables
• Creating functions and sub routine
• Validating inputs
• Transferring data from on Form to Database sheet through VBA code
• Searching a specific record
• Editing an existing recording with the help of macro
• Updating the data
• Deleting records
• Reset the form
• Compiling and testing the application

Below mentioned codes are for module1

Option Explicit

Function Validate() As Boolean

    Dim frm As Worksheet

    Set frm = ThisWorkbook.Sheets(“Form”)

    Validate = True

    With frm

        .Range(“I6”).Interior.Color = xlNone

        .Range(“I8”).Interior.Color = xlNone

        .Range(“I10”).Interior.Color = xlNone

        .Range(“I12”).Interior.Color = xlNone

        .Range(“I14”).Interior.Color = xlNone

        .Range(“I16”).Interior.Color = xlNone

    End With

    ‘Validating Employee ID

    If Trim(frm.Range(“I6”).Value) = “” Then

        MsgBox “Employee Id is blank.”, vbOKOnly + vbInformation, “Emloyee ID”

        frm.Range(“I6”).Select

        frm.Range(“I6”).Interior.Color = vbRed

        Validate = False

        Exit Function

    End If

    ‘Validating Employee Name

    If Trim(frm.Range(“I8”).Value) = “” Then

        MsgBox “Employee Name is blank.”, vbOKOnly + vbInformation, “Emloyee Name”

        frm.Range(“I8”).Select

        frm.Range(“I8”).Interior.Color = vbRed

        Validate = False

        Exit Function

    End If

    ‘Validating Gender

    If Trim(frm.Range(“I10”).Value) <> “Female” And Trim(frm.Range(“I10”).Value) <> “Male” Then

        MsgBox “Please select valid Gender from Drop-down.”, vbOKOnly + vbInformation, “Gender”

        frm.Range(“I10”).Select

        frm.Range(“I10”).Interior.Color = vbRed

        Validate = False

        Exit Function

    End If

    ‘Validating Department

    If Trim(frm.Range(“I12”).Value) <> “HR” And Trim(frm.Range(“I12”).Value) <> “Operation” _

    And Trim(frm.Range(“I12”).Value) <> “Training” And Trim(frm.Range(“I12”).Value) <> “Quality” Then

        MsgBox “Please select valid Department name from Drop-down.”, vbOKOnly + vbInformation, “Department”

        frm.Range(“I12”).Select

        frm.Range(“I12”).Interior.Color = vbRed

        Validate = False

        Exit Function

    End If

    ‘Validating Salary

    If Trim(frm.Range(“I14”).Value) = “” Or Not IsNumeric(Trim(frm.Range(“I14”).Value)) Then

        MsgBox “Please ender valid Salary.”, vbOKOnly + vbInformation, “Salary”

        frm.Range(“I14”).Select

        frm.Range(“I14”).Interior.Color = vbRed

        Validate = False

        Exit Function

    End If

    ‘Validating Address

    If Trim(frm.Range(“I16”).Value) = “” Then

        MsgBox “Please enter valid address.”, vbOKOnly + vbInformation, “Address”

        frm.Range(“I16”).Select

        frm.Range(“I16”).Interior.Color = vbRed

        Validate = False

        Exit Function

    End If

End Function

‘Code to rese the form

Sub Reset()

    With Sheets(“Form”)

         .Range(“I6”).Interior.Color = xlNone

         .Range(“I6”).Value = “”

         .Range(“I8”).Interior.Color = xlNone

         .Range(“I8”).Value = “”

         .Range(“I10”).Interior.Color = xlNone

         .Range(“I10”).Value = “”

         .Range(“I12”).Interior.Color = xlNone

         .Range(“I12”).Value = “”

         .Range(“I14”).Interior.Color = xlNone

         .Range(“I14”).Value = “”

         .Range(“I16”).Interior.Color = xlNone

         .Range(“I16”).Value = “”

    End With

End Sub

‘Code to save the data

Sub Save()

    Dim frm As Worksheet

    Dim database As Worksheet

    Dim iRow As Long

    Dim iSerial As Long

    Set frm = ThisWorkbook.Sheets(“Form”)

    Set database = ThisWorkbook.Sheets(“Database”)

    If Trim(frm.Range(“M1”).Value) = “” Then

        iRow = database.Range(“A” & Application.Rows.Count).End(xlUp).Row + 1

        If iRow = 2 Then

            iSerial = 1

      Else

          iSerial = database.Cells(iRow – 1, 1).Value + 1

      End If

    Else

        iRow = frm.Range(“L1”).Value

        iSerial = frm.Range(“M1”).Value

    End If

    With database

        .Cells(iRow, 1).Value = iSerial

        .Cells(iRow, 2).Value = frm.Range(“I6”).Value

        .Cells(iRow, 3).Value = frm.Range(“I8”).Value

        .Cells(iRow, 4).Value = frm.Range(“I10”).Value

       .Cells(iRow, 5).Value = frm.Range(“I12”).Value

        .Cells(iRow, 6).Value = frm.Range(“I14”).Value

       .Cells(iRow, 7).Value = frm.Range(“I16”).Value

        .Cells(iRow, 8).Value = Application.UserName

        .Cells(iRow, 9).Value = [Text(Now(), “DD-MM-YYYY HH:MM:SS”)]

    End With

    frm.Range(“L1”).Value = “”

    frm.Range(“M1”).Value = “”

End Sub

‘Code to modify the existing records

Sub Modify()

    Dim iRow As Long

    Dim iSerial As Long

    iSerial = Application.InputBox(“Please enter Serial Number to make modification.”, “Modify”, , , , , , 1)

    On Error Resume Next

    iRow = Application.WorksheetFunction.IfError _

    (Application.WorksheetFunction.Match(iSerial, Sheets(“Database”).Range(“A:A”), 0), 0)

    On Error GoTo 0

    If iRow = 0 Then

         MsgBox “No record found.”, vbOKOnly + vbCritical, “No Record”

        Exit Sub

   End If

    Sheets(“Form”).Range(“L1”).Value = iRow

    Sheets(“Form”).Range(“M1”).Value = iSerial

    Sheets(“Form”).Range(“I6”).Value = Sheets(“Database”).Cells(iRow, 2).Value

    Sheets(“Form”).Range(“I8”).Value = Sheets(“Database”).Cells(iRow, 3).Value

    Sheets(“Form”).Range(“I10”).Value = Sheets(“Database”).Cells(iRow, 4).Value

    Sheets(“Form”).Range(“I12”).Value = Sheets(“Database”).Cells(iRow, 5).Value

    Sheets(“Form”).Range(“I14”).Value = Sheets(“Database”).Cells(iRow, 6).Value

    Sheets(“Form”).Range(“I16”).Value = Sheets(“Database”).Cells(iRow, 7).Value

 End Sub

‘Code to delete the records

Sub DeleteRecord()

    Dim iRow As Long

    Dim iSerial As Long

    iSerial = Application.InputBox(“Please enter S.No. to delete the recor.”, “Delete”, , , , , , 1)

    On Error Resume Next

    iRow = Application.WorksheetFunction.IfError _

    (Application.WorksheetFunction.Match(iSerial, Sheets(“Database”).Range(“A:A”), 0), 0)

     On Error GoTo 0

    If iRow = 0 Then

        MsgBox “No record found.”, vbOKOnly + vbCritical, “No Record”

        Exit Sub

    End If

    Sheets(“Database”).Cells(iRow, 1).EntireRow.Delete shift:=xlUp

End Sub

Please use the below mentioned VBA codes in Sheet1 (form).

Option Explicit

Private Sub cmdDelete_Click()

    Dim msgValue As VbMsgBoxResult

    msgValue = MsgBox(“Do you want to delete the record?”, vbYesNo + vbQuestion, “Delete”)

    If msgValue = vbYes Then

        Call DeleteRecord

    End If

End Sub

Private Sub cmdModify_Click()

    Dim msgValue As VbMsgBoxResult

    msgValue = MsgBox(“Do you want to modify the record?”, vbYesNo + vbQuestion, “Modify”)

    If msgValue = vbYes Then

        Call Modify

    End If

End Sub

Private Sub cmdReset_Click()

    Dim msgValue As VbMsgBoxResult

    msgValue = MsgBox(“Do you want to reset the Form?”, vbYesNo + vbQuestion, “Reset”)

    If msgValue = vbYes Then

        Call Reset

    End If

End Sub

Private Sub cmdSave_Click()

    If Validate = True Then

        Dim msgValue As VbMsgBoxResult

        msgValue = MsgBox(“Do you want to save the data?”, vbYesNo + vbQuestion, “Save”)

          If msgValue = vbYes Then

                Call Save

                Call Reset

         End If

    End If

End Sub

Please watch step-by-step tutorial to learn how to create this fully functional data entry form in excel.

Click on below button to support our website and buy the Employee Data Entry Form with Save, Modify, Delete and Reset Features.