Cat On A Spreadsheet

Extending Your VBA UserForm with Database-Like Features

In the last post, we built a clean, modern-looking UserForm for entering customer data into Excel. That worked well for data entry, but real-world users often want more:

Browse through existing records
Search for specific entries
Edit and update records
Delete old or incorrect data

In other words, they want a mini database app inside Excel. Let’s build that.

Step 1: The Setup

We’ll work with a Customers sheet that has the following columns:

A: Name | B: Email | C: Phone | D: Type

Our UserForm will now have:

Input fields (as before: txtName, txtEmail, txtPhone, cboType)
Navigation buttons (btnNext, btnPrevious)
Action buttons (btnSave, btnDelete, btnNew)
A hidden variable to track the current row being viewed

Step 2: Loading a Record into the Form

We’ll store the row number of the current record in a module-level variable:

  Dim currentRow As Long
 
 Private Sub LoadRecord(rowNum As Long)
 
 Dim ws As Worksheet
 
 Set ws = ThisWorkbook.Sheets("Customers")
 
 ' Exit if invalid row
 
 If rowNum < 2 Or rowNum > ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Then Exit Sub
 
 ' Load values into form
 
 Me.txtName.Value = ws.Cells(rowNum, 1).Value
 
 Me.txtEmail.Value = ws.Cells(rowNum, 2).Value
 
 Me.txtPhone.Value = ws.Cells(rowNum, 3).Value
 
 Me.cboType.Value = ws.Cells(rowNum, 4).Value
 
 currentRow = rowNum
 
 End Sub

Step 3: Navigation Between Records

We can add Next and Previous buttons to scroll through rows.

  Private Sub btnNext_Click()
 
 Call LoadRecord(currentRow + 1)
 
 End Sub
 
 Private Sub btnPrevious_Click()
 
 Call LoadRecord(currentRow - 1)
 
 End Sub

Step 4: Editing and Saving Changes

If the user makes edits, clicking Save should overwrite the current row.

  Private Sub btnSave_Click()
 
 Dim ws As Worksheet
 
 Set ws = ThisWorkbook.Sheets("Customers")
 
 If currentRow = 0 Then
 
      ;MsgBox "No record loaded. Use New to add a record.", vbExclamation
 
      Exit Sub
 
 End If
 
 ' Write values
 
 ws.Cells(currentRow, 1).Value = Me.txtName.Value
 
 ws.Cells(currentRow, 2).Value = Me.txtEmail.Value
 
 ws.Cells(currentRow, 3).Value = Me.txtPhone.Value
 
 ws.Cells(currentRow, 4).Value = Me.cboType.Value
 
 MsgBox "Record updated!", vbInformation
 
 End Sub

Step 5: Adding New Records

We’ll clear the form and append the record at the bottom of the sheet.

  Private Sub btnNew_Click()
 
 Me.txtName.Value = ""
 
 Me.txtEmail.Value = ""
 
 Me.txtPhone.Value = ""
 
 Me.cboType.Value = ""
 
 currentRow = ThisWorkbook.Sheets("Customers").Cells(Rows.Count, "A").End(xlUp).Row + 1
 
 End Sub

Step 6: Deleting Records

Deleting means clearing the row and shifting up the remaining records

  Private Sub btnDelete_Click()
 
 Dim ws As Worksheet
 
 Set ws = ThisWorkbook.Sheets("Customers")
 
 If currentRow < 2 Then Exit Sub
 
 ws.Rows(currentRow).Delete
 
 MsgBox "Record deleted.", vbInformation
 
 Call LoadRecord(2)
 
 End Sub

Step 7: Adding Search

Let’s allow the user to search by name.

  Private Sub btnSearch_Click()
 
 Dim ws As Worksheet
 
 Dim f As Range
 
 Set ws = ThisWorkbook.Sheets("Customers")
 
 Set f = ws.Columns(1).Find(What:=Me.txtName.Value, LookAt:=xlWhole)
 
 If Not f Is Nothing Then
 
      Call LoadRecord(f.Row)
 
 Else
 
      MsgBox "Name not found.", vbExclamation
 
 End If
 
 End Sub

Step 8: Initializing the Form

Finally, when the UserForm opens, load the first record:

  Private Sub UserForm_Initialize()
 
 Call LoadRecord(2)
 
 End Sub

Final Thoughts

With a few extra buttons and some well-structured VBA, you’ve transformed a simple UserForm into a CRUD app:

Create: Add new customers
Read: Browse and search records
Update: Edit existing entries
Delete: Remove records

It’s not a full database, but for many small business or team applications, this approach is powerful enough to manage data smoothly—without leaving Excel.