Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 37 ยท Projects and Practice

Project: Contact Manager with a UserForm

Build a contact manager in Excel VBA: a UserForm to add, search, edit and delete rows on a Contacts sheet, with validation, duplicate checks and a ListBox.

This project builds a small database application inside Excel. A sheet named Contacts stores the data, and a UserForm gives people a friendly way to add, search, edit and delete contacts. It brings together forms, controls, validation, ranges and error handling.

VBA and UserForms cannot run on this site, so the VBA is read-only. The runnable JavaScript examples model the data operations (search, duplicate check, update, delete), which are the logic you would test first.

Design

The sheet has one header row and these columns: ID | Name | Email | Phone | City. The form has a search box, a ListBox of matching contacts, text boxes for the fields, and buttons Add, Update, Delete and Close.

flowchart LR
  A["txtSearch"] -->|"Change"| B["lstContacts"]
  B -->|"Click"| C["fields (txtName ...)"]
  C -->|"Add / Update / Delete"| D["Contacts sheet"]
  D -->|"Reload"| B

The sheet is the single source of truth; the form only shows and edits it.

Keep the data layer separate from the form: a standard module modContacts with functions that know about the sheet, and a form that only calls them.

The data layer

VB
' Module: modContacts
Option Explicit
Private Const SHEET_NAME As String = "Contacts"

Private Function Sh() As Worksheet
    Set Sh = ThisWorkbook.Worksheets(SHEET_NAME)
End Function

Public Function LastDataRow() As Long
    LastDataRow = Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row
End Function

Public Function NextId() As Long
    NextId = Application.WorksheetFunction.Max(Sh.Range("A:A")) + 1
End Function

Public Function FindRowById(id As Long) As Long
    Dim m As Variant
    m = Application.Match(id, Sh.Range("A:A"), 0)
    If IsError(m) Then FindRowById = 0 Else FindRowById = CLng(m)
End Function

Public Function EmailExists(email As String, Optional ignoreId As Long = 0) As Boolean
    Dim r As Long
    For r = 2 To LastDataRow
        If StrComp(Sh.Cells(r, 3).Value2, email, vbTextCompare) = 0 Then
            If Sh.Cells(r, 1).Value2 <> ignoreId Then EmailExists = True: Exit Function
        End If
    Next r
End Function

Public Sub AddContact(nm As String, email As String, phone As String, city As String)
    Dim r As Long: r = LastDataRow + 1
    Sh.Cells(r, 1).Value2 = NextId
    Sh.Cells(r, 2).Value2 = nm
    Sh.Cells(r, 3).Value2 = email
    Sh.Cells(r, 4).NumberFormat = "@"          ' keep leading zeros
    Sh.Cells(r, 4).Value2 = phone
    Sh.Cells(r, 5).Value2 = city
End Sub

Public Sub DeleteContact(id As Long)
    Dim r As Long: r = FindRowById(id)
    If r > 1 Then Sh.Rows(r).Delete
End Sub

Functions have one purpose each and return values, instead of reading controls directly, which makes them reusable from other forms or from tests.

The form code

VB
' UserForm: frmContacts
Private Sub UserForm_Initialize()
    lstContacts.ColumnCount = 4
    lstContacts.ColumnWidths = "30 pt;110 pt;130 pt;80 pt"
    LoadList ""
End Sub

Private Sub LoadList(filterText As String)
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Contacts")
    Dim data As Variant, r As Long
    lstContacts.Clear
    If modContacts.LastDataRow < 2 Then Exit Sub
    data = ws.Range("A2:E" & modContacts.LastDataRow).Value2
    For r = 1 To UBound(data, 1)
        If Len(filterText) = 0 Or InStr(1, data(r, 2) & "|" & data(r, 3), filterText, vbTextCompare) > 0 Then
            lstContacts.AddItem data(r, 1)
            lstContacts.List(lstContacts.ListCount - 1, 1) = data(r, 2)
            lstContacts.List(lstContacts.ListCount - 1, 2) = data(r, 3)
            lstContacts.List(lstContacts.ListCount - 1, 3) = data(r, 4)
        End If
    Next r
End Sub

Private Sub txtSearch_Change()
    LoadList Trim$(txtSearch.Value)
End Sub

Private Sub lstContacts_Click()
    If lstContacts.ListIndex = -1 Then Exit Sub
    Dim r As Long: r = modContacts.FindRowById(CLng(lstContacts.List(lstContacts.ListIndex, 0)))
    With ThisWorkbook.Worksheets("Contacts")
        txtName.Value = .Cells(r, 2).Value2
        txtEmail.Value = .Cells(r, 3).Value2
        txtPhone.Value = .Cells(r, 4).Value2
        txtCity.Value = .Cells(r, 5).Value2
    End With
End Sub

Private Sub cmdAdd_Click()
    If Not IsValid(0) Then Exit Sub
    modContacts.AddContact Trim$(txtName.Value), Trim$(txtEmail.Value), txtPhone.Value, txtCity.Value
    LoadList txtSearch.Value
    ClearFields
End Sub

Private Sub cmdDelete_Click()
    If lstContacts.ListIndex = -1 Then Exit Sub
    If MsgBox("Delete this contact?", vbYesNo + vbQuestion) = vbYes Then
        modContacts.DeleteContact CLng(lstContacts.List(lstContacts.ListIndex, 0))
        LoadList txtSearch.Value
        ClearFields
    End If
End Sub

Private Function IsValid(ignoreId As Long) As Boolean
    Dim msg As String
    If Len(Trim$(txtName.Value)) = 0 Then msg = msg & "Name is required." & vbCrLf
    If Not txtEmail.Value Like "?*@?*.?*" Then msg = msg & "Enter a valid e-mail." & vbCrLf
    If Len(msg) = 0 Then If modContacts.EmailExists(txtEmail.Value, ignoreId) Then msg = "That e-mail already exists."
    If Len(msg) > 0 Then MsgBox msg, vbExclamation
    IsValid = (Len(msg) = 0)
End Function

Notes: the Like pattern "?*@?*.?*" is a quick plausibility check, not full e-mail validation. Phone numbers are stored as text so leading zeros survive. Deleting a sheet row is safe here because lookups find rows by ID again each time rather than by remembered row numbers.

Try it: the data operations

JS Add, duplicate check, search, delete

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Find a row by ID like Application.Match

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Improvements to try

  • An Update button that calls IsValid(currentId) and writes the fields back to the found row.
  • Sorting by name, an export-to-CSV button (see CSV files).
  • Protect the sheet and allow edits only through the form.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Why keep sheet access in a separate module instead of in the form code?

    Separating data access from the user interface keeps each part simple and reusable.
  2. 2. Why find a row by ID instead of storing the row number?

    An ID stays attached to the contact; a row number can point to the wrong person after a delete or sort.
  3. 3. Why store phone numbers as text?

    Numbers lose leading zeros; text keeps the value exactly as typed.

Summary

  • Split the app into a data module and a form that only handles interaction.
  • Look rows up by a stable ID; validate input and check duplicates before saving.
  • A search box that filters the ListBox on each Change gives instant results.
  • Confirm destructive actions and reload the list after each change.

Next: VBA Performance and Best Practices