Create a data entry form with validation in Excel using VBA

This code will guide you step-by-step in creating a simple form where the user can enter data. We will also include validation to ensure that the entered data is correct.

Steps to Create the Form:

• Access the VBA Editor:
  Open Excel, then press Alt + F11 to open the VBA editor.
• Create a Form (UserForm):
  In the VBA editor, go to the Insert menu and select UserForm to add a new form.
• Add Controls to the Form:
  You can add several controls (TextBox, ComboBox, Label, CommandButton) using the toolbox.

For this example, we will use:

• • 3 TextBox: to enter the name, age, and city.
  • 1 ComboBox: to select gender (Male, Female).
  • 2 CommandButton: one button to save the data and another to cancel.

Add VBA Code:
After creating the form, here is the code you can use for validation and saving the data.

Detailed VBA Code for the Data Entry Form with Validation

1. Code for the Form (UserForm)

Here’s the code for the form, with input fields and validation.

Private Sub UserForm_Initialize()
    ' Fill the ComboBox with default values
    ComboBoxSex.AddItem "Male"
    ComboBoxSex.AddItem "Female"
End Sub
Private Sub CommandButtonSave_Click()
    ' Validation of fields
    If TextBoxName.Value = "" Then
        MsgBox "Name is required!", vbExclamation, "Error"
        TextBoxName.SetFocus
        Exit Sub
    End If   
    If TextBoxAge.Value = "" Or Not IsNumeric(TextBoxAge.Value) Then
        MsgBox "Please enter a valid age!", vbExclamation, "Error"
        TextBoxAge.SetFocus
        Exit Sub
    End If   
    If ComboBoxSex.Value = "" Then
        MsgBox "Please select a gender!", vbExclamation, "Error"
        ComboBoxSex.SetFocus
        Exit Sub
    End If   
    If TextBoxCity.Value = "" Then
        MsgBox "City is required!", vbExclamation, "Error"
        TextBoxCity.SetFocus
        Exit Sub
    End If   
    ' Save data to the Excel sheet
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(xlUp).Row + 1   
    Sheets("Sheet1").Cells(LastRow, 1).Value = TextBoxName.Value
    Sheets("Sheet1").Cells(LastRow, 2).Value = TextBoxAge.Value
    Sheets("Sheet1").Cells(LastRow, 3).Value = ComboBoxSex.Value
    Sheets("Sheet1").Cells(LastRow, 4).Value = TextBoxCity.Value  
    MsgBox "Data saved successfully!", vbInformation, "Success"  
    ' Reset the fields
    TextBoxName.Value = ""
    TextBoxAge.Value = ""
    ComboBoxSex.Value = ""
    TextBoxCity.Value = ""
End Sub
Private Sub CommandButtonCancel_Click()
    ' Close the form without saving
    Unload Me
End Sub

2. Code Explanation

• UserForm_Initialize:
  This procedure runs when the form is initialized. It populates the ComboBoxSex with two options (« Male » and « Female »).
• CommandButtonSave_Click:
  This procedure triggers when the user clicks the « Save » button.
  • It starts by checking if all required fields are filled correctly. If a field is empty or invalid, an error message is displayed, and the user is asked to correct it.
  • Then, the data is saved into the Excel sheet. The information is inserted into the first empty row on Sheet1.
  • After saving, a confirmation message appears, and the form fields are reset for new data entry.
• CommandButtonCancel_Click:
  This procedure closes the form without saving the data when the user clicks the « Cancel » button.

3. Example Excel Sheet Structure

Before testing the form, make sure that Sheet1 in your workbook contains the following columns:

• Column A: Name
• Column B: Age
• Column C: Gender
• Column D: City

4. Launch the Form

To open the form from an Excel sheet, you can add a command button and link this button to the following macro in a module:

Sub OpenForm()
    UserForm1.Show
End Sub

Then, assign this macro to the button in your Excel sheet.

5. Summary of Key Points

• Data Validation: The form checks if all fields are correctly filled before saving the data.
• Reset Fields: After each save, the form is reset to allow for new data entry.
• Interaction with Excel Sheet: The entered data is saved into the Excel sheet, starting from the first empty row.