How to Create a Data Entry Form in Excel Step-by-step Guide.
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 a step-by-step tutorial to learn how to create this fully functional data entry form in excel.
Click on the below button to download the data entry form.
Reviewed by Time 2 Tech
on
June 07, 2020
Rating:
No comments: