To enter data through an Excel VBA UserForm, create a form with text boxes and a Save button, validate the entries in the button’s VBA event, then write the values to the next available row on a worksheet. In Excel for Microsoft 365 on Windows, the example below saves a name, an ISO-formatted date, an amount, and notes to a sheet named Data.
What a UserForm does
An Excel UserForm is a custom dialog built with VBA controls such as text boxes, labels, and command buttons. Rather than typing directly into worksheet cells, a user fills in named fields and clicks Save. VBA can check that required values are present and in the expected format before putting them into a table-like range.
This approach is useful when people repeatedly add records, when the worksheet layout should remain out of sight, or when consistent input matters. A form can make a workbook friendlier, but it is not a database or a substitute for access controls. The saved records still reside in the workbook, and anyone with sufficient access to that file may be able to change them.
Prepare the workbook and worksheet
First, make a copy of the workbook. The macro below adds rows, but VBA changes cannot be reversed with Ctrl+Z. Create a worksheet named Data and put these headers in row 1:
- Column A: Name
- Column B: Date
- Column C: Amount
- Column D: Notes
Keep each record on one row. The sample determines the next row by looking for the last occupied cell in column A, so it expects every saved record to have a name and does not handle deliberately blank name cells. If your workbook already contains a table with a different structure, adjust the column references in the code rather than pasting it unchanged.
Save the file as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. A standard .xlsx workbook cannot store VBA project code. On Windows, show the Developer tab if it is hidden: choose File > Options > Customize Ribbon, then select Developer.
Create the form and its controls
On the Developer tab, choose Visual Basic, or press Alt+F11. In the Visual Basic Editor, choose Insert > UserForm. If the Toolbox is not visible, use View > Toolbox. Add four text boxes, four labels, and two command buttons. Arrange the controls so users can identify each field, then set the controls’ (Name) properties in the Properties window as follows:
- Text boxes:
txtName,txtDate,txtAmount, andtxtNotes. - Save button:
cmdSave, with its Caption set to Save. - Cancel button:
cmdCancel, with its Caption set to Cancel.
Labels are for display, so their names do not affect this example. The names of the text boxes and buttons do matter: VBA refers to those exact names. In the form’s Properties window, you can set its Caption to something like Add a Record.
Add the Save and Cancel code
Double-click the Save button to open its code window and put the following event procedure in the UserForm’s code module. Add the Cancel procedure there as well. The date field deliberately requires yyyy-mm-dd, such as 2026-11-02, so the year, month, and day can be checked without relying on a user’s regional date order. Amounts use the Windows regional decimal convention.
Option Explicit
Private Sub cmdSave_Click()
Dim ws As Worksheet
Dim dateParts() As String
Dim entryDate As Date
Dim yearPart As Long
Dim monthPart As Long
Dim dayPart As Long
Dim amountValue As Double
Dim lastRow As Long
Dim nextRow As Long
If Len(Trim$(Me.txtName.Value)) = 0 Then
MsgBox "Enter a name before saving.", vbExclamation
Me.txtName.SetFocus
Exit Sub
End If
dateParts = Split(Trim$(Me.txtDate.Value), "-")
If UBound(dateParts) <> 2 Then
MsgBox "Enter the date as yyyy-mm-dd.", vbExclamation
Me.txtDate.SetFocus
Exit Sub
End If
If Len(dateParts(0)) <> 4 Or Len(dateParts(1)) <> 2 Or _
Len(dateParts(2)) <> 2 Then
MsgBox "Enter the date as yyyy-mm-dd.", vbExclamation
Me.txtDate.SetFocus
Exit Sub
End If
If Not IsNumeric(dateParts(0)) Or Not IsNumeric(dateParts(1)) Or _
Not IsNumeric(dateParts(2)) Then
MsgBox "The date must contain numbers in yyyy-mm-dd format.", vbExclamation
Me.txtDate.SetFocus
Exit Sub
End If
yearPart = CLng(dateParts(0))
monthPart = CLng(dateParts(1))
dayPart = CLng(dateParts(2))
If yearPart < 1900 Or yearPart > 9999 Or _
monthPart < 1 Or monthPart > 12 Or _
dayPart < 1 Or dayPart > 31 Then
MsgBox "Enter a valid date as yyyy-mm-dd.", vbExclamation
Me.txtDate.SetFocus
Exit Sub
End If
entryDate = DateSerial(yearPart, monthPart, dayPart)
If Year(entryDate) <> yearPart Or Month(entryDate) <> monthPart Or _
Day(entryDate) <> dayPart Then
MsgBox "That date does not exist. Check the month and day.", vbExclamation
Me.txtDate.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtAmount.Value) Then
MsgBox "Enter a numeric amount.", vbExclamation
Me.txtAmount.SetFocus
Exit Sub
End If
amountValue = CDbl(Me.txtAmount.Value)
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
nextRow = lastRow + 1
' Prefix text with an apostrophe so it is stored as text, not a formula.
ws.Cells(nextRow, 1).Value = "'" & Trim$(Me.txtName.Value)
ws.Cells(nextRow, 2).Value = entryDate
ws.Cells(nextRow, 2).NumberFormat = "yyyy-mm-dd"
ws.Cells(nextRow, 3).Value = amountValue
ws.Cells(nextRow, 4).Value = "'" & Me.txtNotes.Value
MsgBox "Record saved to row " & nextRow & ".", vbInformation
Unload Me
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
The apostrophe prefix on the name and notes helps keep text that begins with characters such as an equals sign from being interpreted as a worksheet formula. Excel treats the prefix as a text-entry marker, so it generally does not display as part of the cell’s visible text. The procedure converts the date into an actual Excel date value and applies a display format; the amount is stored as a number, not as text.
Show the form and test a record
The form needs a way to open. In the Visual Basic Editor, choose Insert > Module and add this separate procedure. It assumes the UserForm is named frmEntry; set that value in the form’s (Name) property if necessary.
Option Explicit
Public Sub ShowEntryForm()
frmEntry.Show
End Sub
Return to Excel and run ShowEntryForm from Developer > Macros, or assign it to a worksheet button. To create a button, use Developer > Insert > Button (Form Control), draw the button, and assign the macro. On the first test, enter a name, a valid date such as 2026-11-02, and a number such as 125.50 if that decimal separator is accepted by your regional settings. Click Save and confirm the new record appears beneath the headers.
Try an empty name, an invalid date such as 2026-02-30, and a nonnumeric amount. Each should produce a message and prevent the row from being written. If the workbook is in a different folder or the form does not open, verify that you are running the macro in the intended workbook and that the form’s name and worksheet name match the code.
How the entry process works
When a user clicks Save, Excel runs the button’s cmdSave_Click event. The procedure checks fields before changing the sheet. It splits the date at hyphens, verifies the component lengths and numeric values, and then uses DateSerial. The follow-up comparison matters because date constructors can roll an out-of-range day into the next month; comparing the resulting parts catches an impossible date such as February 30.
After validation, the macro finds the last occupied cell in column A and writes the record to the next row. For example, if row 1 contains headers and rows 2 through 8 contain names, the next record is written in row 9. A valid date is stored as a date value, and a valid amount as a number, which allows later sorting, filtering, and arithmetic. The form then closes.
Validation, formatting, and practical improvements
Validation should reflect the rules of the data rather than simply block unusual input. This sample requires a name, date, and numeric amount, but permits negative amounts because they may represent credits or adjustments. If negatives are never valid in your process, add a separate rule after converting the amount, such as checking whether amountValue is less than zero. Do not impose that restriction without confirming what the values mean.
For a real workbook, consider these refinements:
- Use a worksheet table, such as one created with Insert > Table, so filters and formatting can expand as records are added. If you change to a table-based approach, update the VBA to add a table row rather than assuming a fixed range.
- Set clear formats for amounts, such as currency, based on the actual currency and reporting needs. A number format changes display, not the underlying numeric value.
- Decide whether duplicate entries are allowed. The sample does not search for duplicates, so submitting the same details twice adds two rows.
- Consider a confirmation message before saving if an accidental click would create a costly duplicate. For larger workflows, add a unique record ID and a deliberate duplicate-check rule.
- Keep labels, column headers, and instructions consistent. A form field called Amount should not silently mean different things to different users.
The sample writes directly to the first four columns of the Data sheet and does not include error handling for a renamed or missing sheet, protected cells, or a full disk. Those are workbook-specific conditions. Test with a copy and add checks where your workflow requires them.
VBA forms compared with worksheet entry
A UserForm is not always the best solution. A formatted worksheet with data validation can be easier to maintain and keeps the records visible. Excel’s Data Validation command can restrict entries, for example, to a date range or a list of allowed values, without requiring a VBA form. A table also provides built-in filtering and makes it straightforward for several experienced users to enter rows.
A UserForm is more useful when the input sequence needs guidance, when people should not navigate the data sheet, or when VBA must apply several checks before saving. However, forms require macro-enabled files and users must be able to run macros under their organization’s security policy. Excel for the web does not run VBA macros. Excel for Mac has VBA capabilities, but the example is written for Windows; verify UserForm and control compatibility in the Mac version and environment you need to support before distributing the workbook.
Security and maintenance
VBA macros can change workbook contents, so only enable macros in files from sources you trust. In a workplace, follow your organization’s approved macro policy rather than weakening security settings to make a downloaded workbook run. A UserForm does not encrypt the worksheet or prevent a knowledgeable user from accessing the data.
Document the sheet name, column order, and control names alongside the workbook. If someone renames Data, deletes a column, or changes a control name, the code may stop working or write values to the wrong place. Keep a clean backup before changing code, and test changes with representative inputs, including blanks, invalid dates, decimal amounts, and text that begins with an equals sign.
Frequently Asked Questions
How do I open a VBA UserForm in Excel?
Run a public macro that calls the form’s name followed by .Show, such as frmEntry.Show. You can run the macro from Developer > Macros or assign it to a worksheet button.
Can a UserForm add data to the next empty row?
Yes. VBA can find the last used cell in a chosen column and write to the following row. The example uses column A, so it relies on every saved record having a name in that column.
Can I use this VBA form in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA macros. Use desktop Excel to run the form, or consider a non-VBA entry method for browser-based users.
Why does the date field require yyyy-mm-dd?
A fixed year-month-day format makes the example’s validation unambiguous across common regional date orders. The macro checks that the date exists and saves it as an Excel date value; it does not store the date as plain text.
Can I undo a UserForm save with Ctrl+Z?
Do not rely on it. VBA macros generally clear Excel’s undo history, so Ctrl+Z cannot reliably reverse a record written by a macro. Save a backup copy before testing or changing the code.
Related
- Excel Power Query: Import Multiple CSV Files
- Can You Import Excel and CSV Files Into QuickBooks?
- Compare Two Columns in Excel for Differences
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- SteelSeries Macro: How to Add and Assign One in GGTo add a macro in SteelSeries GG, open GG > Engine, select your connected device, choose the key…
- TinyTask Macro: How to Record, Save, and Replay TasksTo use a TinyTask macro, open TinyTask on a Windows PC, click Record, perform the mouse and keyboard…
- Excel VBA Paste Values: Methods, Code, and Common FixesTo paste values with Excel VBA, copy a source range and use PasteSpecial Paste:=xlPasteValues, or assign the source…
- Excel VBA UserForm Tutorial: Build a Data Entry FormTo build an Excel VBA UserForm, open the Visual Basic Editor, insert a UserForm, add controls such as…
- Add Libraries in Excel: Add-ins, VBA References & MoreTo add a library in Excel, first decide whether you mean an Office add-in, an Excel add-in file,…
- VBA to Save Excel Tabs as Separate FilesYes. A VBA macro can copy each visible worksheet tab into a new workbook and save it as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.