How to Create an Excel VBA Data-Entry Form with Calculations
Question details
The user wants to build an Excel VBA UserForm that automatically sets the current date, loads a drop-down list from a worksheet, calculates an ending number based on user input, and saves the data to a database worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an automated data-entry form using VBA to streamline data collection, perform automatic calculations, and record specific fields to a SharePoint-based workbook.
- Observed behavior
- A fully functional UserForm that calculates values dynamically and appends submitted entries to the next available row in a specified workbook.
Ensure the Developer tab is enabled in your spreadsheet application and remember to save your file as a Macro-Enabled Workbook (.xlsm) so you do not lose your VBA code.
Build and Code the VBA UserForm
This solution walks through creating the form, initializing default values like the current date, handling calculations dynamically, and saving the data to your target worksheet.
To accomplish this task, you need to combine several VBA event handlers: UserForm_Initialize for setup, Control_Change for live calculations, and CommandButton_Click for submitting the data.
Open the VBA Editor (Alt + F11), insert a UserForm, and add your text boxes and combo boxes. To automatically populate the date and a drop-down list when the form opens, use the Initialize event: Private Sub UserForm_Initialize() Me.txtDate.Value = Format(Date, "dd/mm/yyyy") Dim ws As Worksheet Dim cell As Range Set ws = ThisWorkbook.Sheets("RestrictionsTab") For Each cell In ws.Range("A2:A10") Me.cboOptions.AddItem cell.Value Next cell End Sub Replace 'txtDate', 'RestrictionsTab', 'A2:A10', and 'cboOptions' with your actual control and sheet names.
To automatically calculate a result when a quantity is entered, add a Change event to your quantity text box. This ensures the ending number updates instantly: Private Sub txtQuantity_Change() If IsNumeric(Me.txtStart.Value) And IsNumeric(Me.txtQuantity.Value) Then Me.txtEnd.Value = Val(Me.txtStart.Value) + Val(Me.txtQuantity.Value) End If End Sub You can also duplicate this logic inside a 'txtStart_Change' event so it updates if the user modifies the starting number.
To save the inputs when the user clicks 'Submit', add this code to your button's Click event. It automatically finds the next blank row in the destination sheet: Private Sub btnSubmit_Click() Dim ws As Worksheet Dim nextRow As Long Set ws = ThisWorkbook.Sheets("DatabaseSheet") nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1 ws.Cells(nextRow, 1).Value = Me.txtDate.Value ws.Cells(nextRow, 2).Value = Me.txtStart.Value ws.Cells(nextRow, 3).Value = Me.txtEnd.Value End Sub Ensure 'DatabaseSheet' matches the exact name of the tab where your records should be stored.
Create VBA Data-Entry Forms Seamlessly in WPS Office
WPS Spreadsheet provides robust support for VBA macros and UserForms. You can effortlessly design your data-entry interfaces, execute automated calculations, and manage database sheets with an integrated developer environment that perfectly mirrors your familiar workflow.
- 1. Enable the Developer Tab: Open WPS Spreadsheet, go to the options to enable the Developer tab, giving you full access to macro tools.
- 2. Open the Visual Basic Editor: Click on 'Visual Basic' or press Alt + F11 to launch the integrated coding environment.
- 3. Design Your UserForm: Insert a new UserForm, drag and drop text boxes or combo boxes, and paste in your data calculation and entry code.
Frequently Asked Questions
How do I ensure my VBA UserForm opens automatically when the workbook is opened?
You can trigger the form to open automatically by accessing the 'ThisWorkbook' module in the VBA Editor and adding 'UserFormName.Show' inside the 'Workbook_Open()' event.
Why am I getting a 'Type Mismatch' error when my UserForm calculates the ending number?
This usually happens when VBA tries to add text or empty fields as numbers. Ensure you are using the 'Val()' function to convert text box strings into numbers, and wrap your calculation in an 'If IsNumeric()' statement to check the inputs first.
How do I save a workbook that contains VBA UserForms?
You must save the file as an Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, Excel or WPS will strip out and permanently delete all your VBA code and UserForms.
Can I clear the form fields automatically after the user clicks submit?
Yes. Inside your 'btnSubmit_Click' event, simply add lines at the very end to reset the values. For example, add 'Me.txtStart.Value = ""' and 'Me.txtQuantity.Value = ""' to clear out those specific boxes for the next entry.
