Template Store

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.

6 COMMENTS

  1. Warm Greetings
    Sir your works are very educative and helpful.
    Thanks a lot for the informative videos which have been very handy.

    I need Help with the following codes:

    If Trim(usr.Range(“I1”).Value) = “” Then

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

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

    Else

    iRow = usr.Range(“H1”).Value

    iSerial = usr.Range(“I1”).Value

    End If

    Thanks For Reading my comment do reply me @mail

    • Are you facing any issue on this? Please let me know so that I can provide you the updated code. Thanks!

  2. Good afternoon, I have just gone through your excellent tutorial and everything works except the delete record section. I have double checked everything and I cannot work out what is happening, but the form sheet gets deleted everytime instead of the single line of data. Do you have any suggestions? I even went tothe website and checked the code line for line. Puzzled!

    Many thanks for your hard work and support, I am using Excel 2019 if that helps.

    • Hello Allen, Thanks for your feedback! Could you please post Delete Sub Procedure code and Sheets name here. I will go through that and make the correction, if required.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Captcha verification failed!
CAPTCHA user score failed. Please contact us!

Recommended Reads

Latest Articles

Verified by MonsterInsights