UserForm basics in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office assistant managing employee data.

📋 Request: Your manager wants an easy way to enter new employee details into the spreadsheet without typing directly into cells.

📊 Data: You have an Excel sheet with columns: Employee ID, Name, Department, and Start Date. Currently, data is entered manually.

🎯 Deliverable: Create a UserForm that lets users enter employee details and adds them to the next empty row in the sheet.

Sample Data

Employee ID	Name	Department	Start Date
101	Alice Smith	Sales	2023-01-15
102	Bob Jones	Marketing	2023-02-20
103	Carol Lee	HR	2023-03-05

1

Step 1: Open the Excel workbook and press Alt+F11 to open the VBA editor.

No formula needed.

Expected Result

VBA editor window opens.

2

Step 2: In the VBA editor, click Insert > UserForm to add a new UserForm.

No formula needed.

Expected Result

A blank UserForm appears ready for design.

3

Step 3: Add four TextBox controls to the UserForm for Employee ID, Name, Department, and Start Date. Add Labels next to each TextBox describing the field.

No formula needed.

Expected Result

UserForm shows four labeled text boxes for data entry.

4

Step 4: Add a CommandButton to the UserForm and set its caption to 'Add Employee'.

No formula needed.

Expected Result

UserForm has a button labeled 'Add Employee'.

5

Step 5: Double-click the 'Add Employee' button to open its code window. Enter VBA code to find the next empty row and write the TextBox values into the sheet columns.

Private Sub CommandButton1_Click() Dim ws As Worksheet Dim nextRow As Long Set ws = ThisWorkbook.Sheets("Sheet1") nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1 ws.Cells(nextRow, 1).Value = Me.TextBox1.Value ws.Cells(nextRow, 2).Value = Me.TextBox2.Value ws.Cells(nextRow, 3).Value = Me.TextBox3.Value ws.Cells(nextRow, 4).Value = Me.TextBox4.Value MsgBox "Employee added successfully!" Me.TextBox1.Value = "" Me.TextBox2.Value = "" Me.TextBox3.Value = "" Me.TextBox4.Value = "" End Sub

Expected Result

Clicking the button adds the entered data to the next empty row and clears the form.

6

Step 6: Create a macro to show the UserForm. In a new module, add code: Sub ShowForm() UserForm1.Show End Sub

Sub ShowForm() UserForm1.Show End Sub

Expected Result

Running this macro opens the UserForm for data entry.

7

Step 7: Save the workbook as a macro-enabled file (.xlsm). Test the UserForm by running the ShowForm macro and entering new employee data.

No formula needed.

Expected Result

New employee data appears in the sheet after submitting the form.

Final Result

__________

↓

__________

↓

__________

↓

__________

↓

Add Employee

✓UserForm simplifies data entry and reduces errors by avoiding direct cell typing.

✓The form clears after each entry, ready for the next employee.

✓Data is added to the next empty row automatically.

Bonus Challenge

Add validation to the UserForm so that the Employee ID must be a number and Start Date must be a valid date before allowing data entry.

Show Hint

Use VBA If statements to check IsNumeric for Employee ID and IsDate for Start Date. Show a message box if validation fails and do not add data.