An Excel VBA input form is a custom window, usually built as a VBA UserForm, that lets people enter information without typing directly into worksheet cells. In Excel for Microsoft 365 on Windows, create one in the Visual Basic Editor, add controls such as text boxes and buttons, then write VBA to validate the entries and save them to a worksheet.
What an Excel VBA input form does
A UserForm presents a guided way to collect data. Instead of asking someone to find the next blank row and fill in columns in the correct order, a form can label each field, explain what belongs there, and provide a Save button. VBA responds to actions such as opening the form or clicking a button.
A form is useful for repeated, structured entry: customer inquiries, inventory changes, service requests, or attendance records. It can reduce accidental edits to formulas and headings, but it does not automatically make the underlying data accurate. The code still needs sensible validation, and the worksheet remains the place where records are stored.
A UserForm is different from Excel’s built-in data-entry features. It is a custom VBA interface; it is also different from a worksheet form control, such as a button placed on a sheet. A button can launch a UserForm, but the form itself is designed in the Visual Basic Editor.
Plan the fields and worksheet layout
Decide what one record contains before you build the interface. A small request log might use four columns: Name, Department, Notes, and Submitted At. Put those headings in cells A1:D1 on a worksheet named Records. Each submission will occupy one row below the headings.
Keep the form and the data table aligned. For example, use one text box for each of the first three fields and let VBA add the submission time. Write down which control corresponds to which column; clear naming makes it easier to maintain the code later. A concise plan might be:
- Name: required, stored in column A.
- Department: required, stored in column B.
- Notes: optional, stored in column C.
- Submitted At: generated when Save is clicked, stored in column D.
Use a proper Excel table if you want convenient filtering and consistent formatting as the list grows. The simple example below writes to worksheet cells and does not require a table. If your workbook already has a data layout, adapt the column assignments rather than inserting sample headers over existing information.
Create the UserForm and its controls
First save a working copy of the workbook. On Windows, show the Developer tab if needed by opening File > Options > Customize Ribbon, checking Developer, and selecting OK. Choose Developer > Visual Basic to open the editor, or press Alt+F11.
- In the Visual Basic Editor, choose Insert > UserForm. The form designer and Toolbox should appear.
- Use the Properties window to set the form’s
(Name)tofrmEntryand itsCaptionto something readable, such asNew request. If the Properties window is hidden, press F4. - Add three labels and three text boxes from the Toolbox. Set the text boxes’
(Name)properties totxtName,txtDepartment, andtxtNotes. - Add a CommandButton. Set its
(Name)tocmdSaveand itsCaptiontoSave. A second button namedcmdCancelcan close the window.
Labels should tell users what to enter, and the controls should be large enough for realistic values. For longer notes, set the notes text box’s MultiLine property to True; consider setting EnterKeyBehavior to True so Enter can create a line break. Control names are identifiers used by VBA, so spelling and capitalization must match the code.
Write code to validate and save entries
Double-click the Save button in the form designer to open its click-event procedure, then put this code in the form’s code module. It assumes the worksheet named Records has the four headings described above, with records beginning in row 2.
Private Sub cmdSave_Click()
Dim ws As Worksheet
Dim nextRow As Long
Dim personName As String
Dim department As String
Dim notes As String
' Read and trim the text entered in the form.
personName = Trim$(Me.txtName.Value)
department = Trim$(Me.txtDepartment.Value)
notes = Trim$(Me.txtNotes.Value)
' Do not save a record without the required fields.
If Len(personName) = 0 Or Len(department) = 0 Then
MsgBox "Enter a name and department before saving.", vbExclamation
Exit Sub
End If
' Find the next available row in the name column.
Set ws = ThisWorkbook.Worksheets("Records")
nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
' Write one record across columns A:D.
ws.Cells(nextRow, 1).Value = personName
ws.Cells(nextRow, 2).Value = department
ws.Cells(nextRow, 3).Value = notes
ws.Cells(nextRow, 4).Value = Now
' Clear the form ready for another entry.
Me.txtName.Value = vbNullString
Me.txtDepartment.Value = vbNullString
Me.txtNotes.Value = vbNullString
Me.txtName.SetFocus
MsgBox "The record was saved.", vbInformation
End Sub
The validation checks that Name and Department are not blank after leading and trailing spaces are removed. Notes remain optional. The row-finding statement looks upward from the bottom of column A and selects the row after its last non-empty cell; therefore, keep the Name column populated for each saved record. The timestamp is the computer’s current date and time, not a separately verified audit record.
To close the form from its Cancel button, double-click that button and add this event procedure:
Private Sub cmdCancel_Click()
' Close the form without saving the current entries.
Unload Me
End Sub
To show the form, choose Insert > Module in the editor and put this macro in the standard module:
Public Sub ShowEntryForm()
' Display the data-entry window.
frmEntry.Show
End Sub
Change the form, worksheet, or control names in the code if your workbook uses different names. VBA refers to the workbook containing the code through ThisWorkbook, which is generally safer than relying on whichever workbook happens to be active.
Run the form and save the workbook correctly
In the Visual Basic Editor, click inside ShowEntryForm and press F5, or return to Excel and run the macro from Developer > Macros. You can also assign the macro to a worksheet button: use Developer > Insert, choose the Button under Form Controls, draw it on the sheet, and assign ShowEntryForm. A button makes the entry point obvious, but it does not change how the form stores records.
Save the file as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. A standard .xlsx workbook cannot retain VBA project code when saved in that format. When the file is reopened, Excel may display a security notification; only enable macros in files you trust and expected to receive. Do not advise users to lower their global macro security settings just to run one workbook.
Before running a new or modified macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so a mistaken macro may overwrite or append data without an easy undo route.
Validation, usability, and design choices
The sample includes only basic required-field checks. A real form should match checks to the information being collected. For example, a quantity might need to be numeric and positive, while a department could be selected from a ComboBox to limit spelling variations. Date fields may need a clear expected format and validation; typing a date into a text box alone does not guarantee Excel interprets it as intended.
Useful improvements include:
- Use ComboBox or OptionButton controls for a short, fixed set of choices, rather than asking users to type the same category in different ways.
- Show a specific message when an entry is missing or invalid, and leave the user’s existing input in place so it can be corrected.
- Set a clear tab order so pressing Tab moves through fields in a sensible sequence.
- Consider duplicate checks or limits on text length if those rules matter to the process.
- Protect formula cells and keep the data sheet’s headings intact; a form does not itself prevent someone from editing the worksheet directly.
Do not treat a simple VBA form as a secure database or a complete multi-user system. If several people edit a shared workbook at once, simultaneous saves, file locking, and conflicts can complicate record entry. A worksheet-based solution may be sufficient for a small, controlled task, but a shared data service or purpose-built application may be more appropriate for important or high-volume records.
Common problems and how to fix them
If the macro cannot find a control, compare the control’s (Name) property with the name used in the code. If it reports that a worksheet subscript is out of range, check that a sheet is named exactly Records; spaces and spelling matter. If the form opens but saving fails, confirm that the code is in the Save button’s event procedure and that the expected columns and headings exist.
If a record overwrites or skips an unexpected row, inspect column A: the example uses that column to locate the last record. A blank in the Name column can make the next-row calculation stop too early. If the form or macro is unavailable, check that the workbook is saved as .xlsm, that macros are permitted by the organization’s security policy, and that the code is in the intended workbook.
Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use desktop Excel to work with this UserForm. Excel for Mac has VBA support, but the editor interface and some controls or Windows-specific code differ; avoid Windows API calls and ActiveX dependencies if cross-platform use is required. On Mac, use the available Visual Basic Editor from Excel’s macro tools, and verify the form on the target version rather than assuming every Windows control behaves identically.
When to use a UserForm instead of alternatives
A UserForm is most useful when the workbook needs a custom sequence of questions, conditional behavior, or a tailored interface. For a very small list, entering data directly into a well-labeled Excel table may be faster and easier to maintain. Excel’s built-in table features also make sorting and filtering straightforward without any VBA.
A worksheet-based form can place labels and input cells on a dedicated sheet, then use a button or macro to transfer the values. This is often easier to inspect and may be more approachable for occasional users, though it does not provide the same floating dialog interface. For collaboration, web access, or collection from people who do not use desktop Excel, consider Microsoft Forms or another supported data-collection tool; check how responses will be stored and whether the tool meets the process’s access and privacy needs.
VBA is not the only automation option in Microsoft 365. Office Scripts are designed for certain web-based Excel automation workflows, but they do not create VBA UserForms. Choose based on where people will enter data, whether they need a custom dialog, the expected number of users, and how much code the workbook’s owner can maintain.
Frequently Asked Questions
How do I create an input form in Excel VBA?
Open the Visual Basic Editor, choose Insert > UserForm, add labels and input controls, and write VBA in the controls’ event procedures. Add a macro to display the form, then save the workbook as an .xlsm file.
Can an Excel VBA input form save data to a worksheet?
Yes. A button’s click procedure can validate the fields, find the next row, and write each value to the intended columns. The example above saves a name, department, notes, and a timestamp to a worksheet named Records.
Can I use a VBA UserForm in Excel for the web?
No. Excel for the web does not run VBA macros or display VBA UserForms. Open the workbook in desktop Excel to use the form; for browser-based collection, consider a web-compatible alternative such as Microsoft Forms.
Why does my VBA form not appear when I click the button?
Check that the button is assigned to the correct public macro, the form’s name matches the name in the macro, and the workbook is macro-enabled. Also confirm that Excel has not blocked the macros and that the code is in the workbook you opened.
Can I undo a VBA form submission with Ctrl+Z?
No. Excel’s normal Undo history does not reverse changes made by VBA macros. Save a backup copy before testing or running a macro that writes data, and consider adding deliberate correction or deletion procedures if the workflow needs them.
Related
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Delete an Excel Macro: Safe Steps for Windows and MacTo delete an Excel macro in Microsoft 365 for Windows, open the workbook, choose Developer > Macros, select…
- Excel VBA: Filter a PivotTable by Field or ValueYes. In Excel VBA, filter a PivotTable by setting a report-filter field’s CurrentPage, applying a label or value…
- VBA Excel Calculate Range: Methods and ExamplesTo calculate a specific range in Excel VBA, use the range’s Calculate method: for example, Worksheets("Sheet1").Range("B2:B100").Calculate. It recalculates…
- How to Use Macros in SteelSeries GGTo use a macro in SteelSeries GG, open Engine, select a compatible device, create or record a macro…
- Excel VBA Range Interior Color: Set and Read Cell FillsIn Excel VBA, use the Interior.Color property to set or read a cell’s fill color: for example, Range("A1").Interior.Color…
- VBA Import CSV Files: Methods, Code, and Common IssuesTo import a CSV file with VBA, use Excel’s Workbooks.OpenText method to parse it with the correct delimiter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.