Fully Automated Data Entry UserForm in Excel using VBA: A Comprehensive Guide
Table of Contents
Watch on YouTube @thedatalabs fully automated data entry userform
Introduction to fully automated data entry userform
MS Excel is a widely used application for data entry and data analysis job in the business world. However, manual data entry can be time-consuming and error prone. Data entry forms provide a convenient and efficient solution to these issues. Automated data entry forms are especially useful in eliminating human error and saving time. In this comprehensive guide, we will walk you through the process of creating a fully automated data entry form in Microsoft Excel using VBA.
Note: Our YouTube channel, @TheDataLabs, has step-by-step video tutorials for a Data Entry User Form. The tutorials are divided into five parts that cover all the essential features, explanations, and VBA codes needed.
Topics covered in this tutorial ‘fully automated data entry userform’
This tutorial is the first part of a series that will cover the necessary steps for creating an automated data entry form using Excel and VBA. Throughout this section, you will learn how to:
- Create a new Excel file that includes two worksheets named ‘Home’ and ‘Database.’
- Set up a Database table with the required headers in the ‘Database’ sheet.
- Add a Launch Button to the ‘Home’ sheet.
- Insert a UserForm and Module in the VBA Window.
- Create the necessary Sub Routines and Functions to submit the data and reset the UserForm.
- Assign the Macro to the Launch Form button on the ‘Home’ sheet.
Prerequisites for Automated Data Entry Form in Excel using VBA
To get the most out of this tutorial, it is suggested that you have a fundamental understanding of Excel operations like worksheets, cells, and ranges, as well as some knowledge of the VBA programming language.
If you face any challenges during the process or need assistance with your current project, please leave your comments. Our team will review your feedback and queries and provide a suitable response.
Step-by-step guide for creating Fully Automated Date Entry UserForm
Preparing Worksheet and Database Table
- Open a new Excel file and save it with the macro-enabled extension ‘.xlsm‘. Name the file ‘Fully Automated Data Entry Form’.
- Insert two worksheets and rename them to ‘Home’ and ‘Database’.
- In the ‘Database’ sheet, create a table consisting of 9 columns titled as ‘S.No.’, ‘Employee ID’, ‘Employee Name’, ‘Gender’, ‘Department’, ‘City’, ‘Country’, ‘Submitted By’, and ‘Submitted On’. This table will serve as a storage space for data entered through the data entry form.
- Insert a rectangle shape on the ‘Home’ sheet and add the caption ‘Launch Form.’ This rectangle will function as a button to open the data entry form on the click event. Later, a macro will be assigned to it after completing the VBA code.
Opening Visual Basic Editor Window to Design the Form and Write Code for fully automated data entry userform
- To begin creating the UserForm and writing VBA code, click on the Developer Tab and select the Visual Basic button located in the code group. Alternatively, use the shortcut key ALT + F11 to launch the Visual Basic Application code.
- In the Visual Basic Application window, add a UserForm and Module to create the User Interface of the data entry form and write the essential VBA code, including Sub Routines and Functions.
Designing the UserForm
- The UserForm needs to be designed with two sections: “Enter Details” and “Database.” In the “Enter Details” section, we will include several input controls, such as an entry text box for Employee ID and Name, option buttons for Gender (male and female), a Combobox for the department, and text boxes for City and Country. We will also add a Submit button to submit the data and a Reset button to clear the input fields.
In the “Database” section, we will add a list box to display all the data submitted through the data entry form. This list box will be linked to the “Database” sheet.
Writing Subroutines and VBA code
- Proceed to the module window and start writing the required VBA codes. Here, we will add three separate subroutines: ‘Reset’, ‘Submit’, and ‘Show_Form’ to manage the essential functions of the first part.
- Paste the ‘Reset()’ subroutine provided below into the Module window. This subroutine will enable the resetting of the form upon clicking the Reset button or initializing the User Form.
Sub Reset() Dim iRow As Long iRow = WorksheetFunction.CountA(Sheets("Database").Range("A:A")) ' identifying the last row With frmForm .txtID.Value = "" .txtName.Value = "" .optMale.Value = False .optFemale.Value = False .cmbDepartment.Clear .cmbDepartment.AddItem "HR" .cmbDepartment.AddItem "Operation" .cmbDepartment.AddItem "Training" .cmbDepartment.AddItem "Quality" .txtCity.Value = "" .txtCountry.Value = "" .lstDatabase.ColumnCount = 9 .lstDatabase.ColumnHeads = True .lstDatabase.ColumnWidths = "30,60,75,40,60,45,55,70,70" If iRow > 1 Then .lstDatabase.RowSource = "Database!A2:I" & iRow Else .lstDatabase.RowSource = "Database!A2:I2" End If End With End Sub
Understanding the VBA Code of Reset Subroutine
The “Reset” subroutine is used to reset the data entry form when the user wants to start over. It starts by declaring a variable named “iRow” of the “Long” data type, which is determined by using the “COUNTA” function. This function counts the number of non-blank cells in column A of the “Database” sheet and gives us the index of the last row with data in the sheet.
The subroutine then updates the values of various controls on the data entry form using a “With…End With” block. The values of the “Emp ID”, “Emp Name”, “Gender”, “City”, and “Country” textboxes are reset to blank or default values. The “Department” combo box options are also reset to default values, which are “HR”, “Operation”, “Training”, and “Quality”.
The subroutine also sets the properties of the “lstDatabase” list box control to configure its appearance. The “ColumnCount”, “ColumnHeads”, and “ColumnWidths” properties are set to display the appropriate number of columns, column headers, and column widths, respectively. Depending on whether there are existing records in the “Database” sheet or not, the “RowSource” property of the list box is set to display either all records in the sheet or just the headers.
Overall, the “Reset” subroutine sets the form back to its initial state so that the user can enter new data.
- Use the “Submit()” subroutine provided below to submit the data from the User Form to the Database sheet. Simply copy the code and paste it into the module window. This subroutine will handle the process of submitting data.
Sub Submit() Dim sh As Worksheet Dim iRow As Long Set sh = ThisWorkbook.Sheets("Database") iRow = [Counta(Database!A:A)] + 1 With sh .Cells(iRow, 1) = iRow - 1 .Cells(iRow, 2) = frmForm.txtID.Value .Cells(iRow, 3) = frmForm.txtName.Value .Cells(iRow, 4) = IIf(frmForm.optFemale.Value = True, "Female", "Male") .Cells(iRow, 5) = frmForm.cmbDepartment.Value .Cells(iRow, 6) = frmForm.txtCity.Value .Cells(iRow, 7) = frmForm.txtCountry.Value .Cells(iRow, 8) = Application.UserName .Cells(iRow, 9) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")] End With End Sub
Understanding the VBA Code of Submit Subroutine
This code is used to submit data from a user form to a worksheet named “Database”.
First, the code declares a variable “sh” as a worksheet object and assigns it to the “Database” sheet of the workbook. The code then uses the COUNTA function to determine the index of the next empty row in column A and assigns it to the variable “iRow”.
The “With…End With” block is used to set the values of various cells in the “Database” sheet. The values of the cells in columns 1 through 9 are set to the values of various controls on the user form, such as “Emp ID”, “Emp Name”, “Gender”, “Department”, “City”, and “Country”.
The code also sets the username and current date/time in columns 8 and 9, respectively. Finally, the code submits the data to the next empty row in the “Database” sheet.
- Add the “Show_Form” subroutine to the module window by copying and pasting the code. This subroutine is responsible for showing the UserForm when it is called. To open the UserForm, you can assign this subroutine to the Launch Form button on the home sheet.
Sub Show_Form() frmForm.Show End Sub
Call Reset and Submit subroutines on Click and Initialize events
- In the code window of User Form, call the ‘Reset’ subroutine on the Initialize event of UserForm. Use the VBA code as mentioned below.
Private Sub UserForm_Initialize() Call Reset End Sub
Explanation of code: This VBA code is used to automatically reset the user form when it is loaded into memory. The code is triggered by the “Initialize” event of the user form and calls the “Reset” subroutine.
- In the User Form code window, call the ‘Reset’ subroutine on the click event of the ‘cmdReset’ button. Use the VBA code as mentioned below.
Private Sub cmdReset_Click() Dim msgValue As VbMsgBoxResult msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Confirmation") If msgValue = vbNo Then Exit Sub Call Reset End Sub
Explanation of code: This VBA code is for the button named “cmdReset”. When clicked, it displays a message box asking the user for confirmation to reset the form. If the user selects “No”, then the code exits. If the user selects “Yes”, then it calls the “Reset” subroutine to reset the form.
- Keep your cursor in the code window of User Form, call the ‘Submit’ and ‘Reset’ subroutines on the click event of the ‘cmdSave’ button. Please check the below codes.
Private Sub cmdSave_Click() Dim msgValue As VbMsgBoxResult msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Confirmation") If msgValue = vbNo Then Exit Sub Call Submit Call Reset End Sub
Explanation of the code: This VBA code is executed when the user clicks the Save button on the UserForm. It displays a message box asking the user to confirm whether they want to save the data or not. If the user clicks “No”, the code exits the subroutine. If the user clicks “Yes”, the code calls the “Submit” subroutine to save the data to the worksheet and then calls the “Reset” subroutine to clear the UserForm for the next entry.
Assign macro on Launch Form button in Home sheet
- Move to the Excel window and assign the ‘Show_Form’ subroutine on the Launch Form button available on the ‘Home’ sheet by right-clicking on the ‘Launch Form’ button and selecting Assign Macro. In the Assign Macro window, select the ‘Show_Form’ subroutine and click on ‘Okay’ button.
You have now completed the first part of creating a Fully Automated Data Entry form.
Download the Excel file with VBA code
Click on download button to download the Tracker with source code (Part 1 with Add features only). It is completely free!
Next parts of Fully automated data entry userform tutorial (2 to 5)
The upcoming sections of this tutorial fully automated data entry userform (2-5) will cover the following features:
- In Part 2 of the tutorial, you’ll learn about the Edit and Delete Features.
- Part 3 of the tutorial will focus on the Search and Filter Features.
- The next part, Part 4 of the tutorial, will cover Validation, Dynamic Serial, and Print Features.
- Finally, in Part 5 of the tutorial, you’ll discover Additional Features, including Full Screen, Zoom, and Dynamic Drop-down.