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 download the data entry form.