To create an Excel UserForm in Microsoft 365 for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > UserForm, add controls from the Toolbox, and write VBA for their events. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm); the form needs macros to open and operate.
What an Excel UserForm is—and when to use one
A UserForm is a custom dialog box made with Visual Basic for Applications (VBA). It can collect information through text boxes, buttons, drop-down lists, checkboxes, and other controls, then pass that information to a worksheet or use it in a macro. For example, a small inventory form could ask for an item name, quantity, and date, check that the entries are valid, and add a new row to a table.
Forms are useful when a worksheet is awkward for occasional users to fill in directly, when an entry needs basic validation, or when a familiar sequence of prompts can prevent mistakes. They are not a replacement for every spreadsheet interface: a well-labelled Excel table may be simpler for bulk data entry, and a UserForm requires VBA and a macro-enabled file. A form also does not automatically make a workbook a secure database; worksheet protection and VBA are not substitutes for proper access controls.
What to prepare before building the form
Decide what information the form collects and where each value will be stored before adding controls. A short field list helps you avoid a crowded dialog and makes the VBA easier to check. For a basic expense log, you might collect a description and an amount, then store them in columns A and B beneath headers in row 1.
- Choose a destination worksheet and set up clear column headings.
- Decide which fields are required and what kinds of values they accept.
- Give controls meaningful names, such as txtDescription and cmdSave, rather than relying on default names.
- Plan what should happen after saving: keep the form open for another entry, clear it, or close it.
Use a copy of the workbook while developing. VBA changes cannot be undone with Ctrl+Z, and a coding mistake can write to the wrong range or overwrite data. If the workbook contains important records, test the form first with sample data in a separate copy.
Create the UserForm in the Visual Basic Editor
- Open the desktop Excel workbook and press Alt+F11. In the Visual Basic Editor, choose Insert > UserForm. Excel adds a blank form to the VBA project.
- If the Toolbox is not visible, choose View > Toolbox. Select a control, such as a Label, TextBox, or CommandButton, and click or drag on the form to place it.
- Open the Properties window with F4 if needed. Select each control and set its (Name) property to a clear VBA name. Set its Caption property to the text a user should see, where that property is available.
- Set the form’s (Name) property to a useful identifier such as frmExpenseEntry, and set its Caption to a readable title such as Add an Expense.
- Double-click a button to open its code window and create the button’s click event. Add a separate launcher macro in a standard module so the form can be shown from Excel.
These paths refer to the Windows desktop version of Excel. On a Mac, the Visual Basic Editor can be opened with Option+F11; on some keyboards you may also need Fn. Menu names and the editor layout can differ. Excel for the web cannot create or run VBA UserForms, so use desktop Excel to build and operate this solution.
Choose controls and arrange the layout
Each control serves a different purpose. A Label explains a field, a TextBox accepts typed input, and a CommandButton performs an action such as Save or Cancel. A ComboBox offers a list while allowing typed input in some configurations; a ListBox displays selectable choices. CheckBox controls represent independent yes-or-no options, while OptionButton controls are usually used for one choice among a group. Choose the simplest control that fits the information.
Make the form easy to scan. Put labels beside their fields, align the input boxes, and use button captions that describe an action. Keep related fields together and avoid requiring users to remember hidden rules. For instance, if an amount must be entered as a number, say so in a label or validation message. Use the Properties window to adjust dimensions, tab order, and other available settings. Test the form at a realistic window size rather than assuming every user has the same display.
A small working example: save two fields to a sheet
This example assumes a worksheet named Expenses with headers in A1 and B1, and a UserForm named frmExpenseEntry containing a TextBox named txtDescription, a TextBox named txtAmount, and a CommandButton named cmdSave. Add the following event code to the form by double-clicking its Save button. It requires both fields, checks that the amount is numeric, then writes the values to the next row based on column A.
Private Sub cmdSave_Click()
Dim ws As Worksheet
Dim nextRow As Long
Dim amountValue As Double
' Require a description and a numeric amount.
If Len(Trim$(Me.txtDescription.Value)) = 0 Then
MsgBox "Enter a description.", vbExclamation
Me.txtDescription.SetFocus
Exit Sub
End If
If Len(Trim$(Me.txtAmount.Value)) = 0 Or _
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("Expenses")
nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
' Append the two form values beneath the existing entries.
ws.Cells(nextRow, "A").Value = Trim$(Me.txtDescription.Value)
ws.Cells(nextRow, "B").Value = amountValue
Me.txtDescription.Value = ""
Me.txtAmount.Value = ""
Me.txtDescription.SetFocus
End Sub
In this layout, if row 1 contains the headers and rows 2 through 4 already contain entries, the next saved record goes into row 5. The code leaves the form open and clears both boxes for another entry. It assumes the sheet exists and that column A is the correct way to find the last record; adapt the sheet name, control names, and destination columns to match your workbook. A regional decimal separator may affect how users enter numeric values, so test amounts in the same Excel locale used by the people filling out the form.
To add a launcher, choose Insert > Module in the Visual Basic Editor and place this procedure in the standard module. Assign it to a worksheet button or run it from the Macro dialog.
Public Sub ShowExpenseEntry()
' Open the data-entry form.
frmExpenseEntry.Show
End Sub
Test the form and handle common errors
Test both valid and invalid entries before sharing the workbook. Try a blank description, a blank amount, text in the amount box, and a normal record. Check the resulting worksheet cells, not just the message shown by the form. If users can type values such as dates or currency, test those formats too. A simple numeric check does not enforce business rules such as a positive amount or a permitted date range; add those conditions only after deciding what the workbook should accept.
Common problems often come from names that do not match. If VBA reports that a method or data member cannot be found, compare the control’s (Name) property with the name used in the code. If you see a subscript-out-of-range error, check that the worksheet tab is named exactly Expenses. If the macro does not appear to run, verify that the file is saved as .xlsm and that macros are permitted under your organization’s settings. Do not respond to a warning by enabling all macros globally; follow your organization’s trusted-file policy.
Save, share, and use the form safely
Save the workbook as Excel Macro-Enabled Workbook (.xlsm) so its VBA project is retained. A standard .xlsx file cannot store VBA macros. When you distribute the workbook, tell recipients that it contains macros and explain what the macro does. Excel may block macros in files from the internet or show a security warning; users should only enable content when they trust the file and its source. Workplace security settings may prevent them from enabling macros at all.
Before each meaningful code change, preserve a clean copy or use a version-controlled location if one is available. Macros cannot be reversed with Ctrl+Z, so check what a procedure writes or clears before running it on real records. Avoid code that silently deletes or overwrites data. If several people need to enter information simultaneously, consider whether a shared list, database, or approved online form is more appropriate than a workbook that writes to one local sheet.
UserForms compared with simpler alternatives
A custom VBA UserForm is a good fit when the entry process needs a tailored dialog, multiple controls, or custom validation. For a single list, an Excel table with clear headings and data validation may be easier to maintain and does not require users to enable VBA. Excel also has a built-in Data Form command for simple row-by-row entry into a list, although it is less customizable and may need to be added to the Quick Access Toolbar. It is different from a VBA UserForm.
Microsoft Forms is another separate product: it collects responses through an online form and can be useful when people submit information without opening a workbook. Whether it fits depends on account, storage, and organizational requirements. A UserForm is not the same as an Excel worksheet form control, nor is it available in Excel for the web. Choose based on who enters data, whether macros are allowed, and whether the workbook must support simultaneous or remote submissions.
Frequently Asked Questions
Can I create an Excel UserForm without VBA?
No. A VBA UserForm is created in the Visual Basic Editor and uses VBA to show the form and respond to controls. If you need a no-macro approach, consider an Excel table with data validation or a suitable online form instead.
Can an Excel UserForm work in a browser?
No. Excel for the web does not run VBA macros or display VBA UserForms. The workbook must be opened in a compatible desktop version of Excel for this feature.
How do I open a UserForm from a worksheet button?
Create a public macro in a standard module that runs the form’s .Show method, then use Insert > Shapes to add a shape on the worksheet, right-click it, and choose Assign Macro. Select the launcher macro.
Why did my UserForm disappear after saving?
Check that the file was saved as .xlsm, not .xlsx. The .xlsx format does not retain the VBA project. Reopen the macro-enabled copy and confirm the form remains in the project in the Visual Basic Editor.
Can Mac users run an Excel UserForm?
Excel for Mac supports VBA, but its editor and available controls can differ from Windows, and some Windows-specific features are not available. Test the workbook on the Mac versions your users have before relying on a particular control or behavior.
Related
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find Position of a Substring in a StringIn VBA, use InStr to find a substring’s position within a string: it returns the position of the…
- VBA For Loop in Reverse Order: Examples and Common FixesTo run a VBA For loop in reverse order, start the counter at the highest value, set the…
- VBA Function Syntax: How to Write and Call FunctionsIn VBA, a function starts with a Function declaration, contains statements that calculate a result, and ends with…
- Excel VBA: Copy a Worksheet to Another Workbook SafelyTo copy a worksheet from one open workbook to another in Excel VBA, use the worksheet’s Copy method…
- Excel VBA Copy and Paste Formulas Between Cells SafelyTo copy a formula to another cell with Excel VBA, use Copy followed by PasteSpecial Paste:=xlPasteFormulas. This pastes…
- Macro Recorder With Mouse Tracking: How It WorksYes, macro recorders with mouse tracking can capture clicks and, in some tools, pointer movement, scrolling, and keystrokes…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.