Employee Database with Search & VBA Entry Form

An employee database stores staff details in one searchable place. You build it in Excel with lookups and a small VBA form, and use Copilot and ChatGPT to write the search and add-record code.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It keeps employee records (ID, name, department, salary) and lets you search any employee by ID.
• Where it is used: HR teams and small offices that need a quick staff directory.
• What you learn: tables, XLOOKUP, data validation, and a VBA user form.

Features

• Central employee table
• Instant search by employee ID
• Department headcount summary
• A VBA form to add new employees
• AI-written VBA for the add-record button

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• Developer tab enabled (File > Options > Customize Ribbon > Developer)
• Basic understanding of cell references

Folder Structure

employee-database.xlsx
│
├── Sheet: Employees   (EmpID, Name, Department, Salary)
├── Sheet: Search     (lookup box + result cells)
├── Sheet: Summary    (department counts)
└── Module: EmpMacros (VBA form code)

• Employees: the master list.
• Search: where you type an ID and see the result.
• Summary: headcount per department.
• EmpMacros: VBA to add records.

Step-by-Step Development

Step 1 – Create the employee table

What are we building?

A table named Emp with EmpID, Name, Department, Salary.

Why do we need it?

All lookups read from this single source.

How do we build it?

Type the headers, add rows, press Ctrl + T, name it Emp.

=COUNTA(Emp[EmpID])

• What it does: counts how many employees are stored.
• Why needed: a quick size check on the database.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Show me how to make an Excel table and count rows with COUNTA on one column."

Step 2 – Search by employee ID with XLOOKUP

What are we building?

A search box that returns the employee's name and department.

Why do we need it?

Finding a person by ID should take one keystroke.

How do we build it?

On the Search sheet, cell B1 holds the ID.

=XLOOKUP(B1, Emp[EmpID], Emp[Name], "Not found")
=XLOOKUP(B1, Emp[EmpID], Emp[Department], "Not found")

• What it does: looks up B1 in the EmpID column and returns the matching Name or Department.
• Important line: "Not found" is the result when the ID is missing.
• Why needed: XLOOKUP is simpler and safer than older VLOOKUP.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write an XLOOKUP that finds a name from EmpID and shows Not found if missing."

Step 3 – Department headcount

What are we building?

A count of employees per department.

Why do we need it?

Managers want to know team sizes.

How do we build it?

=COUNTIF(Emp[Department], A2)

• What it does: counts rows where Department equals A2.
• Why needed: each department gets a headcount for the Summary sheet.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write a COUNTIF that counts employees in a department listed in A2."

Step 4 – VBA form to add employees

What are we building?

A button that opens a form and appends a new employee.

Why do we need it?

Typing into the table is slow and error-prone.

How do we build it?

Open the Developer tab, Visual Basic, insert a UserForm with boxes for the four fields and a button. Code the button:

Private Sub cmdAdd_Click()
    Dim ws As Worksheet
    Set ws = Sheets("Employees")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(lastRow, 1).Value = txtID.Value
    ws.Cells(lastRow, 2).Value = txtName.Value
    ws.Cells(lastRow, 3).Value = txtDept.Value
    ws.Cells(lastRow, 4).Value = txtSalary.Value
    MsgBox "Employee added"
End Sub

• What it does: finds the next empty row and writes the four form values there.
• Important lines: End(xlUp).Row + 1 locates the first blank row; txtID.Value reads each text box.
• Why needed: adds records without manual scrolling.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write VBA that adds a new row to an Excel table named Employees from a user form with ID, Name, Department, Salary."

Paste the returned code into the form's button, then adjust the text box names to match yours.

Complete Source Code

Search formulas

=XLOOKUP(B1, Emp[EmpID], Emp[Name], "Not found")
=XLOOKUP(B1, Emp[EmpID], Emp[Department], "Not found")
=COUNTIF(Emp[Department], A2)

VBA add-record

Private Sub cmdAdd_Click()
    Dim ws As Worksheet
    Set ws = Sheets("Employees")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(lastRow, 1).Value = txtID.Value
    ws.Cells(lastRow, 2).Value = txtName.Value
    ws.Cells(lastRow, 3).Value = txtDept.Value
    ws.Cells(lastRow, 4).Value = txtSalary.Value
    MsgBox "Employee added"
End Sub

AI prompts

ChatGPT: "Make an Excel table and COUNTA one column."
Copilot: "XLOOKUP name from EmpID, Not found if missing."
ChatGPT: "COUNTIF count employees in department A2."
ChatGPT: "VBA add row to Employees from user form."

Running the Project

1. Open employee-database.xlsx.
2. On the Search sheet type an EmpID in B1; name and department appear.
3. Click the Add Employee button, fill the form, and submit.
4. Expected output: the new row appears at the bottom of Employees.

Testing

Test Case 1

Input: EmpID E003 exists with Name "Ana". Expected Output: Search shows "Ana" and her department. Explanation: XLOOKUP matches the ID and returns the row.

Test Case 2

Input: EmpID Z999 (does not exist). Expected Output: "Not found". Explanation: The fourth XLOOKUP argument handles missing IDs.

Test Case 3

Input: Add employee E010 via the form. Expected Output: New row E010 at the bottom; COUNTA increases by 1. Explanation: VBA appends to the next blank row.

Common Errors

XLOOKUP shows #N/A

Problem: Lookup fails even for a real ID. Reason: The ID has trailing spaces or wrong type. Solution: Trim the search cell and make sure both ID columns are the same format.

Macro blocked

Problem: The button does nothing. Reason: Macros are disabled. Solution: File > Info > Enable Content, and save as .xlsm.

Wrong row written

Problem: Data lands in the wrong place. Reason: lastRow counted a hidden blank. Solution: Ensure the table has no empty rows in the middle.

Improvements

• Add a photo or hire-date column
• Protect the sheet but allow the form to write
• Use Power Query to import from HR system
• Ask ChatGPT to find duplicate IDs
• Export the directory to PDF

Challenge Exercises

1. Add a salary search that returns the pay for a given ID.
2. Write a formula that lists all employees in a chosen department.
3. Use ChatGPT to add input validation to the VBA form (no empty ID).
4. Build a second form to edit an existing employee's department.
5. Connect ChatGPT to summarize the department headcount in plain text.