VBA UserForms: What They Are and How to Use Them

VBA & macros · Updated November 18, 2026

A VBA UserForm is a custom dialog window you build in the Visual Basic for Applications editor and use to collect or display information in Excel. It can contain controls such as text boxes, buttons, labels, and drop-down lists, with VBA code that responds when someone interacts with them.

What a VBA UserForm is

A UserForm is a VBA object that provides a small, purpose-built interface for a workbook. Instead of asking someone to type directly into cells, you can present labeled fields, guide their choices, check their entries, and then send the results to a worksheet or use them in a macro. A data-entry form might ask for a customer name, date, and order amount; a utility form might offer buttons to run a report or select a folder.

“UserForm” is the name of the VBA feature, while “form” is often used informally for the window itself. It is not the same thing as Microsoft Forms, the web-based survey and questionnaire service. A UserForm is built into a workbook’s VBA project and generally runs in desktop Excel when macros are enabled.

How a UserForm works

A form has a visual design and a code module. You add controls to the design surface, set their properties, and write event procedures that run in response to actions. For example, a button’s Click event can validate text entered in a text box and write it to a worksheet. A form can also run code when it opens, closes, or changes state.

Each control has a name that VBA uses to refer to it. A text box named txtName exposes its contents through its Value property; a command button named cmdSave can have a procedure called cmdSave_Click. The form’s code module is separate from a standard module, though both are part of the same workbook’s VBA project.

By default, showing a UserForm with .Show opens it modally: the user must close or dismiss it before working elsewhere in that Excel instance. You can show it modelessly with .Show vbModeless, which lets the user interact with the workbook while the form remains open. Modeless forms can be useful for tools, but they need extra care because the sheet’s selection or contents may change while the form is still visible.

Create a basic UserForm in Excel

In Excel for Microsoft 365 on Windows, enable the Developer tab if it is not visible: choose File > Options > Customize Ribbon, select Developer, and click OK. Then use Developer > Visual Basic to open the VBA editor. In the editor, choose Insert > UserForm. The Toolbox provides controls to place on the form; if it is hidden, use View > Toolbox. The Properties window, commonly opened with F4, lets you set properties such as the form’s caption and a control’s name.

A sensible first build is a short form that writes two fields to a worksheet:

  1. Set the form’s (Name) property to frmContact and its Caption to Enter a contact.
  2. Add two labels and two text boxes. Name the text boxes txtName and txtEmail, and give the labels clear captions.
  3. Add a command button named cmdSave and set its caption to Save.
  4. Create a worksheet named Data, with headings such as Name and Email in cells A1 and B1.
  5. Put the save procedure in the form’s code module, and put the launch procedure in a standard module.

These names matter: the sample code below expects those exact control, form, and sheet names. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so it can retain its VBA project.

Example: validate and save form entries

In the frmContact code module, add this event procedure. It checks that the name field is not blank, finds the next available row based on column A, and writes both entries:

Option Explicit

Private Sub cmdSave_Click()
    ' Validate the required name before writing to the sheet.
    Dim ws As Worksheet
    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

    Set ws = ThisWorkbook.Worksheets("Data")
    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(nextRow, 1).Value = Trim$(Me.txtName.Value)
    ws.Cells(nextRow, 2).Value = Trim$(Me.txtEmail.Value)

    Unload Me
End Sub

In a standard module, add a macro that opens the form:

Option Explicit

Public Sub ShowContactForm()
    ' Display the contact-entry form.
    frmContact.Show
End Sub

Run ShowContactForm from the VBA editor or assign it to a worksheet button. The example deliberately keeps validation simple: it requires a name but does not claim to verify that an email address is real or correctly formatted. Before running any macro that changes a workbook, save a separate copy; VBA macros cannot be undone with Ctrl+Z.

Choosing controls and designing the form

Choose controls according to the kind of answer you need, not just their appearance. A text box accepts typed input; a combo box can offer a list while allowing typed text, depending on its settings; a list box displays selectable choices; and option buttons or check boxes suit a small number of choices. Labels explain what to enter, while command buttons make actions explicit. For a fixed department list, for example, a drop-down can reduce spelling variations that would otherwise complicate filtering or reporting.

Use properties to make the form easier to understand. Give the window a descriptive caption, use meaningful control names, and set labels in plain language. Arrange related fields together and make the primary action easy to find. A Cancel button should have clear behavior—typically it closes the form without saving—rather than looking like another save action. For lengthy forms, consider whether a worksheet table or multiple smaller steps would be clearer than crowding controls into one window.

Design for people who use keyboards as well as a mouse. Set a logical tab order so pressing Tab moves through fields in the order they should be completed. Provide clear instructions for required fields and show a useful message when validation fails. Avoid relying only on color to communicate an error. If a form will be used at different display sizes or with different Windows scaling settings, check its layout on the machines that matter; fonts and control spacing can appear different.

Common problems and their causes

  • The macro does not run: confirm the workbook is saved as .xlsm, that the correct macro is being run, and that Excel’s security settings allow the workbook’s macros. Do not enable macros in an unexpected file just to make a form open.
  • “Subscript out of range” appears: the code may be looking for a worksheet whose tab name does not exactly match Data. Check the workbook and sheet names, including spaces.
  • “Method or data member not found” appears: a control name in the code may not match its (Name) property. Check spelling in both places; the visible caption is not necessarily the control’s name.
  • Entries overwrite the wrong row: the example finds the next row using column A. If column A has gaps or is not reliably filled, choose a different consistently populated column or use an Excel table and write to its next row.
  • The form opens but is hard to use: check control captions, tab order, and the size of the form. Also check whether a previous modeless instance is still open or whether the code uses Hide when it should unload the form.

When diagnosing a VBA error, note the highlighted line and the exact message before changing code. A missing sheet and a misspelled control name require different fixes. Small, clear procedures are easier to maintain than one large event procedure that mixes validation, worksheet operations, and unrelated tasks.

Security, compatibility, and saving

A UserForm is part of a VBA project, so it depends on macros being available. Excel may block macros from files downloaded from the internet or received through other channels. Follow your organization’s policy and use files from trusted sources; lowering macro security globally is not a safe workaround. A form is an interface, not a security boundary: code should still validate inputs and should not assume that every value is well-formed.

Excel for the web can open and edit many workbooks, but it does not run VBA macros or display VBA UserForms. Users need desktop Excel to use a VBA form. Excel for Mac includes VBA support, but the editor, interface, and some controls or behaviors can differ from Windows. If a workbook must work across Windows and Mac, test the actual form and its controls on both platforms rather than assuming identical rendering. ActiveX controls are especially Windows-specific; they are not a dependable cross-platform choice.

Keep a working copy before changing a form or running a macro that writes data. Store important records in a worksheet table or another appropriate data system, and consider adding error handling and checks for duplicate entries when the workbook’s purpose requires them. A simple demonstration macro is not a substitute for a production data-entry process with defined backup and access controls.

UserForms compared with other ways to collect data

A worksheet can be the better choice when users need to review many records at once, sort or filter entries, or work with formulas beside the data. Excel tables provide a familiar grid, and data validation can restrict entries without requiring a custom VBA window. The trade-off is that users may find it easier to change cells or paste unsuitable values unless the sheet is designed and protected thoughtfully.

Microsoft Forms is a separate service suited to questionnaires and responses collected through a browser, especially when respondents should not interact with the workbook itself. It is not a VBA UserForm and does not run from a workbook’s VBA project. Other options include an Office add-in, a database front end, or a dedicated application; these may be more appropriate when many people need simultaneous access, centralized permissions, or a workflow that must also work on phones and the web.

Choose a UserForm when a desktop Excel workflow benefits from a guided, workbook-specific interface and VBA is acceptable. Choose a worksheet when visibility and direct spreadsheet editing matter more. Choose a web-based or managed solution when access across platforms, shared data, or administration matters more than keeping everything inside one macro-enabled workbook.

Frequently Asked Questions

How do I open a VBA UserForm?
Run a macro that calls the form’s .Show method, such as frmContact.Show, or run that statement from the VBA editor. You can also assign a public launch macro to a worksheet button.

Can I use a UserForm in Excel for the web?
No. Excel for the web does not run VBA macros or show VBA UserForms. Open the macro-enabled workbook in desktop Excel to use its VBA interface.

Is a UserForm the same as Microsoft Forms?
No. A VBA UserForm is a custom window in a workbook’s VBA project. Microsoft Forms is a separate service for creating online forms and collecting responses.

Can a UserForm write answers to a worksheet?
Yes. Its event procedures can read control values and write them to cells, as in the example above. Code must identify the destination sheet and decide how to validate and place each entry.

Do I need to save the workbook as XLSM?
Use an Excel Macro-Enabled Workbook (.xlsm) to preserve a VBA project in a standard Excel workbook. Saving as a macro-free format such as .xlsx removes the VBA project.

Will the same UserForm work on Mac and Windows?
Not necessarily without adjustment. Excel for Mac supports VBA, but interface details and control compatibility can differ. Test the workbook on each platform you need to support, and avoid Windows-only controls for cross-platform use.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.