Creating custom data entry assistants in Excel using VBA

Creating custom data entry assistants in Excel using VBA (Visual Basic for Applications) allows you to streamline data input and enhance user interaction. A common approach is to create a UserForm, which is a customized form where the user can input data, and then the data is transferred to specific cells in the worksheet.

Goal

The goal is to create a form that allows the user to input information like name, age, and city, and then record this data in a worksheet.

Steps

1. Create a UserForm: The UserForm is a graphical form where the user can input data. We’ll create a form with text fields for name, age, and city.
2. Add Controls to the UserForm: The form will contain controls like text boxes to enter data and a button to validate the input.
3. Write VBA Code to Handle the Data: The VBA code will collect the entered data and save it to the Excel worksheet.

Detailed VBA Code

1. Create the UserForm
   • Open Excel, then press Alt + F11 to open the VBA editor.
   • In the editor, right-click on VBAProject (YourWorkbook) in the left pane and choose Insert -> UserForm.
   • In the UserForm, add the following controls:
     • Three text boxes: TextBoxName, TextBoxAge, TextBoxCity
     • Three labels: LabelName, LabelAge, LabelCity
     • One button: CommandButtonSubmit
2. Add VBA Code to the UserForm

' Code inside the UserForm module
Private Sub CommandButtonSubmit_Click()
    ' Check if all fields are filled in
    If TextBoxName.Value = "" Or TextBoxAge.Value = "" Or TextBoxCity.Value = "" Then
        MsgBox "Please fill in all fields.", vbExclamation, "Error"
        Exit Sub
    End If
    ' Check if the age is a valid number
    If Not IsNumeric(TextBoxAge.Value) Then
        MsgBox "Age must be a number.", vbExclamation, "Error"
        Exit Sub
    End If
    ' Save the data to the worksheet
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Data") ' Make sure you have a sheet named "Data"   
    ' Find the next empty row
    Dim row As Long
    row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ' Save the data in the worksheet
    ws.Cells(row, 1).Value = TextBoxName.Value
    ws.Cells(row, 2).Value = TextBoxAge.Value
    ws.Cells(row, 3).Value = TextBoxCity.Value
    ' Display a success message
    MsgBox "Data has been saved.", vbInformation, "Success"
    ' Clear the input fields
    TextBoxName.Value = ""
    TextBoxAge.Value = ""
    TextBoxCity.Value = ""  
    ' Close the UserForm
    Unload Me
End Sub

Code Explanation

1. CommandButtonSubmit_Click: This procedure is triggered when the user clicks the submit button (CommandButtonSubmit). It performs several checks:
   • Checks if all fields are filled in. If any field is empty, an error message appears.
   • Checks if the age entered is a valid number. If it is not, an error message appears.
   • If all checks pass, the data is saved to the worksheet.
   • The input fields are cleared to allow new entries.
   • The UserForm is closed automatically after data entry.
2. Saving the Data to the Worksheet:
   • The code saves the data to the first empty row in the sheet named « Data ».
   • The data is saved in columns 1 (Name), 2 (Age), and 3 (City).
3. Clearing and Closing the Form: After saving the data, the text fields are cleared, and the form is closed automatically.
4. Create a Button to Open the Form

Now, we need a button on an Excel worksheet to open the form. Here is the VBA code for a button on a worksheet that opens the UserForm:

' Code in a standard module
Sub OpenForm()
    UserForm1.Show
End Sub

Additional Explanation

1. UserForm1.Show: This line of code opens the UserForm that we created in the VBA editor. Ensure that the name of the UserForm is UserForm1. If you named the form differently, modify the code accordingly.
2. Add a Button to the Excel Worksheet:
   • In Excel, go to the Developer tab, click on Insert -> Button.
   • Assign the OpenForm macro to the button.
3. Data Worksheet:
   • Ensure that you have a worksheet named « Data » in your workbook. This sheet will be used to store the data entered by the user.

Conclusion

This code creates a simple data entry assistant, allowing the user to enter data into a form and save it in an Excel worksheet. You can further customize this code by adding additional controls (such as checkboxes, dropdown menus, etc.) and adjusting the form structure to suit your specific needs.