How to Create an Automated Data Entry Form in Excel VBA
Visual Basic is an excellent language for automating repetitive tasks in Excel. Imagine taking your automation up a notch by creating highly functional user forms that also look clean for end users.
User forms in VBA present you with a blank canvas; You can design and organize the forms to meet your needs at any time.
In this guide, you will learn to create a student-based data entry form that captures relevant information in linked Excel sheets.
Creating a User Form with Excel VBA
Before you begin creating your data-entry form, open a new Excel workbook and follow a few preparatory steps.
Save your workbook with the desired name; Don’t forget to change the file type to Excel Macro-Enabled Workbook.
Feel free to change these names according to your needs.
In the Home sheet, add a button to control the user form macro. Go to the Developer tab and click on the Button option from the Insert drop-down list. Place the button anywhere on the sheet.
After you’ve placed the button, rename it. Right-click on it, and click New to assign a new macro to show the form.
Once the Home and Student Database sheets are created, it is time to design the User Form. Navigate to the Developer tab, and click on Visual Basic to open the editor. Alternatively, you can press ALT+F11 to open the editor window.
A blank user form is ready to use; An accompanying toolbox opens with the form, which contains all the necessary tools for designing the layout.
In the (Name) option, you can change the name of the frame. To show the name on the front-end, you can change the name in the caption column.
Next, choose the Labels option from the Toolbox and insert two labels within this frame. Rename the first as Application Number and the second as Student ID.
The same renaming logic applies; Change the name via the Caption option within the Properties window. Make sure you have selected the relevant label before renaming it.
Next, insert two text boxes next to the Label box. These will be used to capture the user’s input. Change the names of the two text boxes via the (Name) column within the Properties window.
Enter the corresponding text boxes next to these labels; Insert a Two (or More) Option button box from the User Form Toolbox next to the Gender label. Name them male and female (with custom) respectively.
If you’re not sure what parts or any codes mean, don’t worry. We will explain this in detail in the next section.
Automated Form Code Explained
The textbox will contain a mix of text and numeric values, so it is necessary to restrict the user’s input. Application Number, Student ID, Age, Phone, Course ID and Course Duration should only contain numbers, while the rest will contain text.
Using the IF statement, the code triggers an error pop-up if the user enters a character or text value in any of the numeric fields.
Since error validations are present, you need to link the text box to the sheet cell.
lastrow variables will count the last populated row, and store values in them for dynamic use.
Finally, the values are pasted from the text boxes into the linked Excel sheet.
VBA Automation Makes Work Easier
VBA is a multifunctional language that serves many purposes. User forms are just one aspect within VBA—there are many other uses such as consolidating workbooks and worksheets, merging multiple Excel sheets, and other handy automation uses.
No matter the automation goal, VBA is up to the task. If you keep learning and practicing, there’s no aspect of your workflow you can’t improve.