To create a UserForm in Excel VBA, open the Visual Basic Editor, choose Insert > UserForm, add controls from the Toolbox, and write event code for those controls. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm); the example below builds a small form that collects a name and amount, validates the entries, and adds them to a worksheet.
What a VBA UserForm Does
A UserForm is a custom window for entering information or choosing options in an Excel workbook. Instead of asking someone to type directly into cells, you can present labeled fields, buttons, lists, and messages in a consistent layout. A form can then check the entries and decide what to do with them, such as append a row to a table, search for a record, or update a worksheet.
UserForms are part of desktop Excel VBA. They are different from controls placed directly on a worksheet: a form opens as its own dialog, while worksheet controls sit on the grid. A UserForm does not automatically make a workbook a database or guarantee that data is correct. Its code determines validation, storage, and what happens when a user clicks a button.
This walkthrough uses Microsoft Excel for Microsoft 365 on Windows. Excel for the web cannot create or run VBA macros, so users must open the workbook in desktop Excel. Excel for Mac supports VBA, but some controls and platform-specific code may differ; see the platform notes below.
Prepare the Workbook and Open the VBA Editor
Start with a copy of the workbook you intend to modify. Create a worksheet named Data and put Customer in cell A1 and Amount in B1. The sample code will add each submitted name and amount to the next available row beneath those headings. If your workbook uses different headings or a different sheet name, change the code to match.
If the Developer tab is not visible in Windows Excel, enable it through File > Options > Customize Ribbon, then select the Developer check box. Open the Visual Basic Editor with Alt+F11, or choose Developer > Visual Basic. In the editor, select the workbook project in Project Explorer and choose Insert > UserForm. If Project Explorer is hidden, use View > Project Explorer; the Toolbox can be shown with View > Toolbox.
Give the form a useful internal name. Select the form, open its Properties window with F4, and change the (Name) property to frmEntry. The Caption property controls the title shown to users, for example Enter a customer amount. Names such as frmEntry and txtName are used by VBA code, so keep them consistent when following the example.
Design the Form and Name Its Controls
Use the Toolbox to add two labels, two text boxes, and two command buttons. Drag the controls onto the UserForm, arrange them so the labels clearly identify the input fields, and adjust the form size by dragging its borders. Set each control’s (Name) property in the Properties window as follows:
- First text box:
txtName; place a label beside it that reads Customer name. - Second text box:
txtAmount; label it Amount. - First command button:
cmdAdd; set itsCaptionto Add. - Second command button:
cmdCancel; set itsCaptionto Cancel.
Control names are not the same as their captions. A caption is visible to the user; the name is the identifier used in code. A label is useful for explaining a field, but it does not validate the text box beside it. For a simple data-entry form, keep the layout uncluttered and make the primary action obvious. You can also set a button’s Default property to True so Enter activates it, but consider whether that is appropriate for your workflow.
Add the Form’s VBA Event Code
Double-click the Add button to open its Click event procedure, then enter the following code in the UserForm’s code window. The procedure checks that the name is present, verifies that the amount is numeric and nonnegative, and writes valid data to the Data sheet. The headings in row 1 are assumed to exist already.
Option Explicit
Private Sub cmdAdd_Click()
Dim ws As Worksheet
Dim nextRow As Long
Dim customerName As String
Dim amount As Double
' Read and validate the name.
customerName = Trim$(CStr(Me.txtName.Value))
If Len(customerName) = 0 Then
MsgBox "Enter a customer name.", vbExclamation
Me.txtName.SetFocus
Exit Sub
End If
' Check the amount before converting it to a number.
If Len(Trim$(CStr(Me.txtAmount.Value))) = 0 Then
MsgBox "Enter an amount.", vbExclamation
Me.txtAmount.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
amount = CDbl(Me.txtAmount.Value)
If amount < 0 Then
MsgBox "Enter an amount of zero or more.", vbExclamation
Me.txtAmount.SetFocus
Exit Sub
End If
On Error GoTo SheetError
Set ws = ThisWorkbook.Worksheets("Data")
nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
' Add the validated values beneath the existing records.
ws.Cells(nextRow, "A").Value = customerName
ws.Cells(nextRow, "B").Value = amount
Me.txtName.Value = vbNullString
Me.txtAmount.Value = vbNullString
Me.txtName.SetFocus
Exit Sub
SheetError:
MsgBox "The Data sheet could not be updated. Check that it exists and is available.", vbCritical
End Sub
Private Sub cmdCancel_Click()
' Close the form without adding a record.
Unload Me
End Sub
The End(xlUp).Row + 1 expression finds the next row by looking upward from the bottom of column A. It works for this example because the sheet has a heading in A1 and each saved record has a name in column A. If you later allow blank names or restructure the data, use a more suitable way to find the next row. The amount is converted with CDbl only after validation; how numeric text is interpreted can depend on the computer’s regional settings.
Double-clicking a control creates or opens an event procedure for that control. The Add button runs cmdAdd_Click when clicked, and the Cancel button runs cmdCancel_Click. If your buttons have different names, change the procedure names and references to match. The Me keyword refers to the form instance whose code is running.
Create a Macro to Open the Form
A UserForm needs a way to be shown. In the Visual Basic Editor, choose Insert > Module and put this procedure in the standard module. It opens the form whose name you set earlier:
Option Explicit
Public Sub ShowEntryForm()
' Display the data-entry form.
frmEntry.Show
End Sub
Back in Excel, run ShowEntryForm from Developer > Macros, select it, and choose Run. You can also assign the macro to a worksheet button using Developer > Insert > Button (Form Control), then select ShowEntryForm when prompted. That worksheet button is only a launch control; the data-entry window itself remains a UserForm.
Before running macros, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, including entries made by a macro. Save the working file as .xlsm so it can retain VBA code. A normal .xlsx workbook does not store VBA project code, and saving a macro-enabled file in that format can remove the macros.
Test the Form and Improve Its Usability
Test both ordinary and invalid entries before sharing the workbook. A basic test sequence is:
- Open the form and submit a name with a valid amount; confirm that both values appear in the next row on Data.
- Submit a blank name, a blank amount, and nonnumeric text separately; confirm the form shows a message and does not add a row.
- Try a negative amount and confirm the validation rejects it.
- Click Cancel and confirm the form closes without adding data.
- Try the form when the Data sheet is missing or unavailable; confirm the error message is understandable.
Usability also depends on how people will enter and review information. If amounts should be displayed as currency, apply an appropriate number format to column B on the worksheet rather than storing a currency symbol as part of the text. If users need to choose from a fixed set of categories, a ComboBox or ListBox can reduce inconsistent spelling. If a record has many fields, group related fields with labels or frames and explain which fields are required.
For a larger project, consider writing entries to an Excel table rather than relying on an open-ended range. Tables expand as rows are added and make it easier to apply consistent formatting and filters. The example intentionally keeps the storage logic simple; it does not prevent duplicate names, enforce a currency, or check business rules beyond the stated validation. Those requirements need explicit additional code.
Windows, Mac, and Excel for the Web
On Windows, Alt+F11 opens the Visual Basic Editor in desktop Excel. In Excel for Mac, use Option+F11; depending on the keyboard, you may also need the Fn key. You can also find the editor through Tools > Macro > Visual Basic Editor in Mac Excel. Ribbon and settings labels can vary slightly by release, but the editor’s Insert > UserForm workflow is the general VBA approach.
Excel for Mac does not support every Windows-specific VBA feature or control in the same way. Avoid assuming that Windows API calls, external components, or controls that depend on Windows will work on Mac. If a workbook must run on both platforms, keep the form and its code to common VBA features, test on each target version, and check whether the specific controls you choose are supported there. Excel for the web does not run VBA, so a web user must open the file in desktop Excel to use this form.
Common Problems and How to Diagnose Them
If the form does not appear in the Macro list, check that ShowEntryForm is in a standard module, not inside a worksheet or UserForm code module, and that it is declared Public without arguments. If clicking the launch button produces an error, verify that the form’s (Name) is exactly frmEntry. A form’s visible title does not determine its code name.
If code does not compile, use Debug > Compile VBAProject in the editor and read the highlighted line. Check for spelling differences between control names and code, unmatched quotation marks, or code pasted into the wrong module. If the form opens but reports that the sheet cannot be updated, check the sheet tab name for an exact match to Data and confirm that the workbook containing the code is the workbook being used. The sample uses ThisWorkbook, which means the workbook that contains the VBA project.
If macros are blocked, do not lower security settings indiscriminately or enable code from an untrusted file. Workbooks downloaded from the internet may be subject to security restrictions. Use a trusted source and follow your organization’s approved process for reviewing and enabling macros. A UserForm is not inherently secure: it can improve data entry, but it cannot replace appropriate workbook permissions, backups, or review of the code.
When a UserForm Is the Right Choice
A UserForm is useful when a workbook needs a guided entry process, repeated data collection, or controls that are clearer than a wide worksheet. It can gather information in a fixed order and validate it before writing to cells. It takes more setup than typing into a sheet, and someone maintaining the workbook needs to understand the VBA behind the form.
For a small shared list, a formatted worksheet table with data validation may be simpler and more transparent. Microsoft Forms or another approved online form tool may be a better fit when people need to submit responses without opening a macro-enabled desktop workbook. If you only need a single prompt or a simple choice, VBA’s built-in InputBox or a worksheet dropdown might be enough. Choose a UserForm when its tailored interface and validation justify the additional code and desktop requirement.
Frequently Asked Questions
Can I create a UserForm without writing VBA?
A form can be laid out visually in the Visual Basic Editor, but making its buttons save, validate, or otherwise process data requires VBA event code. A static form by itself does not automatically write entries to a worksheet.
Can I use a UserForm in Excel for the web?
No. Excel for the web does not create or run VBA macros. Open the workbook in desktop Excel on Windows or Mac, subject to the form’s control and code compatibility.
Why does my form not show in the Macros dialog?
The form itself is not the macro to run. Put a Public, argument-free procedure such as ShowEntryForm in a standard module and have it call frmEntry.Show.
Does saving as XLSX keep my UserForm?
No. Use the Excel Macro-Enabled Workbook format, .xlsm, to retain VBA code. Keep a separate backup before changing formats or running unfamiliar macros.
Can a UserForm prevent all incorrect data?
It can validate the inputs covered by its code, but it cannot guarantee that every entry is correct. Add checks for the actual rules your workbook needs, and consider worksheet-level validation and review as additional safeguards.
Related
- Can You Password Protect a Sheet in Excel? Here’s How
- Excel Row Groups: Grouping, Labels, and Naming Limits
- Insert a Row Every Other Row in Excel: Easy Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Hide a Column by Number in Your WorksheetTo hide a column by its number in Excel VBA, set that worksheet column’s Hidden property to True.…
- Excel Macro: Refresh All Data ConnectionsYes. In Excel for Microsoft 365 on Windows, a VBA macro can refresh the workbook’s refreshable external data…
- Excel Macros in Google Sheets: What Works?No: Google Sheets cannot run Excel VBA macros, even when you open an Excel workbook that contains them.…
- Excel VBA UserForm Animation Effects: What Really WorksExcel VBA UserForms have no built-in animation setting for effects such as fading, sliding, or zooming. You can…
- VBA MsgBox Arguments: Buttons, Icons, and ExamplesIn VBA, MsgBox displays a dialog and can return the button the user clicked. Its arguments are Prompt,…
- VBA Range Variables: How to Declare and Use ThemA VBA Range variable is an object variable that refers to one or more cells in an Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.