How to Create a Data Entry Form in Excel (Step-by-step Guide)
In this page, we will discuss a fully function 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.
Pasword code pliss.
Thanks
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!
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.