CRUD Operations Using VBA UserForms
Learn how to Create, Read, Update, and Delete Excel records using VBA UserForms with practical examples.
1. Introduction to CRUD Operations
CRUD represents four basic operations used to manage data: Create, Read, Update, and Delete. In Excel VBA, these operations can be performed through a UserForm that interacts with worksheet records.
| Operation | Description | Example |
|---|---|---|
| Create | Add a new record. | Add a new employee. |
| Read | Find and display a record. | Search by Employee ID. |
| Update | Modify an existing record. | Change an employee's salary. |
| Delete | Remove a record. | Delete an employee entry. |
2. Prepare the Excel Worksheet
Create a worksheet named Employees. Enter the
following column headings in row 1.
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| EmployeeID | EmployeeName | Department | Salary |
| 101 | Amit Sharma | IT | 35000 |
| 102 | Neha Patil | HR | 30000 |
3. Design the UserForm
Open the Visual Basic Editor using Alt +
F11. Insert a UserForm and set its
(Name) property to frmEmployee.
Add the following controls using the Toolbox.
| Control | Name Property | Purpose |
|---|---|---|
| TextBox | txtID |
Employee ID |
| TextBox | txtName |
Employee name |
| TextBox | txtDepartment |
Department |
| TextBox | txtSalary |
Salary |
| CommandButton | cmdCreate |
Create a record |
| CommandButton | cmdRead |
Read a record |
| CommandButton | cmdUpdate |
Update a record |
| CommandButton | cmdDelete |
Delete a record |
| CommandButton | cmdClear |
Clear the form |
4. Create Helper Procedures
Open the code window of frmEmployee. Add the
following code. These helper procedures find an employee by ID
and clear the input fields.
Option Explicit
Private Function GetEmployeeSheet() As Worksheet
On Error Resume Next
Set GetEmployeeSheet = ThisWorkbook.Worksheets("Employees")
On Error GoTo 0
If GetEmployeeSheet Is Nothing Then
MsgBox "Worksheet 'Employees' was not found.", vbExclamation
End If
End Function
Private Function FindEmployeeRow(ByVal employeeID As String) As Long
Dim ws As Worksheet
Dim foundCell As Range
Set ws = GetEmployeeSheet()
If ws Is Nothing Then Exit Function
Set foundCell = ws.Columns("A").Find( _
What:=employeeID, _
After:=ws.Cells(1, "A"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not foundCell Is Nothing Then
If foundCell.Row >= 2 Then
FindEmployeeRow = foundCell.Row
End If
End If
End Function
Private Sub ClearFields()
Me.txtID.Value = vbNullString
Me.txtName.Value = vbNullString
Me.txtDepartment.Value = vbNullString
Me.txtSalary.Value = vbNullString
End Sub
Explanation:
FindEmployeeRow returns the worksheet row number
when an employee exists. If the ID is not found, it returns zero.
5. CREATE Operation
The Create operation inserts a new employee record into the next available row. It also validates required fields and prevents duplicate employee IDs.
Private Sub cmdCreate_Click()
Dim ws As Worksheet
Dim nextRow As Long
Dim employeeID As String
Dim employeeName As String
Dim department As String
Dim salaryValue As Double
employeeID = Trim$(CStr(Me.txtID.Value))
employeeName = Trim$(CStr(Me.txtName.Value))
department = Trim$(CStr(Me.txtDepartment.Value))
If employeeID = "" Or employeeName = "" Or department = "" _
Or Trim$(CStr(Me.txtSalary.Value)) = "" Then
MsgBox "Please fill in all fields.", vbExclamation
Exit Sub
End If
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox "Salary must be numeric.", vbExclamation
Exit Sub
End If
salaryValue = CDbl(Me.txtSalary.Value)
If salaryValue < 0 Then
MsgBox "Salary cannot be negative.", vbExclamation
Exit Sub
End If
If FindEmployeeRow(employeeID) > 0 Then
MsgBox "Employee ID already exists.", vbExclamation
Exit Sub
End If
Set ws = GetEmployeeSheet()
If ws Is Nothing Then Exit Sub
nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
If nextRow < 2 Then nextRow = 2
ws.Cells(nextRow, "A").NumberFormat = "@"
ws.Cells(nextRow, "A").Value = employeeID
ws.Cells(nextRow, "B").Value = employeeName
ws.Cells(nextRow, "C").Value = department
ws.Cells(nextRow, "D").Value = salaryValue
MsgBox "Employee created successfully.", vbInformation
ClearFields
End Sub
How it works
- Read the values from the TextBox controls.
- Check for empty fields.
- Validate salary and reject negative values.
- Check whether the Employee ID already exists.
- Insert the record into the next available row.
6. READ Operation
The Read operation searches for an employee using Employee ID and displays the matching details in the UserForm.
Private Sub cmdRead_Click()
Dim ws As Worksheet
Dim employeeID As String
Dim recordRow As Long
employeeID = Trim$(CStr(Me.txtID.Value))
If employeeID = "" Then
MsgBox "Enter an Employee ID to search.", vbExclamation
Exit Sub
End If
recordRow = FindEmployeeRow(employeeID)
If recordRow = 0 Then
MsgBox "Employee record not found.", vbInformation
Exit Sub
End If
Set ws = GetEmployeeSheet()
If ws Is Nothing Then Exit Sub
Me.txtID.Value = CStr(ws.Cells(recordRow, "A").Value)
Me.txtName.Value = CStr(ws.Cells(recordRow, "B").Value)
Me.txtDepartment.Value = CStr(ws.Cells(recordRow, "C").Value)
Me.txtSalary.Value = CStr(ws.Cells(recordRow, "D").Value)
MsgBox "Employee record loaded.", vbInformation
End Sub
7. UPDATE Operation
Update modifies an existing employee record. First, use Read to load the record. Edit its details and click Update to save the changes.
Private Sub cmdUpdate_Click()
Dim ws As Worksheet
Dim employeeID As String
Dim employeeName As String
Dim department As String
Dim salaryValue As Double
Dim recordRow As Long
employeeID = Trim$(CStr(Me.txtID.Value))
employeeName = Trim$(CStr(Me.txtName.Value))
department = Trim$(CStr(Me.txtDepartment.Value))
If employeeID = "" Then
MsgBox "Enter an Employee ID.", vbExclamation
Exit Sub
End If
recordRow = FindEmployeeRow(employeeID)
If recordRow = 0 Then
MsgBox "Employee record not found. Read the record first.", vbExclamation
Exit Sub
End If
If employeeName = "" Or department = "" _
Or Trim$(CStr(Me.txtSalary.Value)) = "" Then
MsgBox "Please fill in all fields.", vbExclamation
Exit Sub
End If
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox "Salary must be numeric.", vbExclamation
Exit Sub
End If
salaryValue = CDbl(Me.txtSalary.Value)
If salaryValue < 0 Then
MsgBox "Salary cannot be negative.", vbExclamation
Exit Sub
End If
If MsgBox("Update this employee record?", _
vbQuestion + vbYesNo, "Confirm Update") <> vbYes Then
Exit Sub
End If
Set ws = GetEmployeeSheet()
If ws Is Nothing Then Exit Sub
ws.Cells(recordRow, "B").Value = employeeName
ws.Cells(recordRow, "C").Value = department
ws.Cells(recordRow, "D").Value = salaryValue
MsgBox "Employee record updated successfully.", vbInformation
End Sub
8. DELETE Operation
Delete removes the worksheet row matching the entered Employee ID. The user must confirm the operation before the row is removed.
Private Sub cmdDelete_Click()
Dim ws As Worksheet
Dim employeeID As String
Dim recordRow As Long
employeeID = Trim$(CStr(Me.txtID.Value))
If employeeID = "" Then
MsgBox "Enter an Employee ID to delete.", vbExclamation
Exit Sub
End If
recordRow = FindEmployeeRow(employeeID)
If recordRow = 0 Then
MsgBox "Employee record not found.", vbInformation
Exit Sub
End If
If MsgBox("Permanently delete Employee ID " & employeeID & "?", _
vbQuestion + vbYesNo + vbDefaultButton2, _
"Confirm Delete") <> vbYes Then
Exit Sub
End If
Set ws = GetEmployeeSheet()
If ws Is Nothing Then Exit Sub
ws.Rows(recordRow).Delete
MsgBox "Employee record deleted successfully.", vbInformation
ClearFields
End Sub
9. Clear the UserForm
The Clear button removes the values from the form fields without changing any worksheet records.
Private Sub cmdClear_Click()
ClearFields
End Sub
10. Run the UserForm
Insert a standard VBA Module and add the following procedure to open the form.
Public Sub OpenEmployeeForm()
frmEmployee.Show
End Sub
Execution Steps
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm). - Confirm the worksheet is named
Employees. - Confirm the UserForm is named
frmEmployee. - Confirm all control names match the code.
- Run
OpenEmployeeFormfrom the VBA editor. - Test Create, Read, Update, and Delete using sample records.
11. Practice Exercises
Exercise 1: Add Email
Add an Email TextBox and save the email address in column E when creating and updating employee records.
Exercise 2: Search by Name
Create a search feature that finds an employee using their name instead of Employee ID.
Exercise 3: Display All Records
Add a ListBox to display all employee records from the worksheet.
Exercise 4: Improve Validation
Add additional validation for Employee ID format, department values, and salary limits.
Important Points to Remember
- CRUD stands for Create, Read, Update, and Delete.
- Use a unique Employee ID to identify each record.
- Validate user input before saving data.
- Confirm destructive operations before deleting records.
- Worksheet and control names must match the VBA code.
- Keep a backup before testing delete functionality.
Summary
VBA UserForms provide a simple interface for managing worksheet data. Create inserts records, Read retrieves records, Update modifies existing records, and Delete removes records after confirmation.
Combining CRUD operations with validation and unique IDs helps you build a basic Employee Management System in Excel.