excelavon

Excel VBA UserForm Examples: Forms, Code, and Setup Guide

VBA & macros · Updated November 3, 2026

Excel VBA UserForm Examples: Forms, Code, and Setup Guide

Common Excel VBA UserForm examples include a contact-entry form, a search box, a data-editing dialog, and a task-specific menu. A UserForm is a custom window built in the Visual Basic Editor; its controls collect input or trigger actions, and VBA code decides what happens next.

What an Excel VBA UserForm Does

A UserForm gives a workbook a guided interface instead of asking people to type directly into cells or remember a macro name. It can display text boxes, buttons, lists, check boxes, and other controls. A user might enter a name, choose a department, and click Save; VBA can check the entries and put them in the next available row on a worksheet.

Forms are useful when a workbook is used repeatedly by people who are not comfortable navigating its sheets. They can reduce accidental edits to formulas and make a process easier to follow. A UserForm does not replace the workbook or database behind it: it is a front end, and the worksheet, table, or other data store still holds the information.

The examples below target desktop Excel for Microsoft 365 on Windows. UserForms and VBA macros do not run in Excel for the web. Excel for Mac supports VBA, but some controls and features available on Windows may be unavailable or behave differently; keep a form simple and check it on the target platform.

Example: A Contact Data-Entry Form

A practical first project is a contact form with three fields: name, email address, and department. In the Visual Basic Editor, insert a UserForm and set its (Name) property to frmContact. Add two TextBox controls named txtName and txtEmail, a ComboBox named cboDepartment, and CommandButton controls named cmdSave and cmdCancel. Set their Caption properties to labels people will understand, such as Save and Cancel. Add labels beside the input controls.

Create a worksheet named Contacts and put Name, Email, and Department in cells A1:C1. The following event code belongs in the form’s code module. It fills the department list when the form opens, checks for a name and a basic email indicator, and appends accepted entries below the headings.

Option Explicit

Private Sub UserForm_Initialize()
    ' Offer a fixed set of department choices.
    With cboDepartment
        .AddItem "Sales"
        .AddItem "Operations"
        .AddItem "Support"
    End With
End Sub

Private Sub cmdSave_Click()
    Dim ws As Worksheet
    Dim nextRow As Long

    If Len(Trim$(CStr(txtName.Value))) = 0 Then
        MsgBox "Enter a name before saving.", vbExclamation
        txtName.SetFocus
        Exit Sub
    End If

    If InStr(1, CStr(txtEmail.Value), "@", vbTextCompare) = 0 Then
        MsgBox "Enter an email address containing @.", vbExclamation
        txtEmail.SetFocus
        Exit Sub
    End If

    If cboDepartment.ListIndex = -1 Then
        MsgBox "Choose a department.", vbExclamation
        cboDepartment.SetFocus
        Exit Sub
    End If

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

    ws.Cells(nextRow, 1).Value = Trim$(CStr(txtName.Value))
    ws.Cells(nextRow, 2).Value = Trim$(CStr(txtEmail.Value))
    ws.Cells(nextRow, 3).Value = cboDepartment.Value

    MsgBox "Contact saved.", vbInformation
    Unload Me
End Sub

Private Sub cmdCancel_Click()
    ' Close without writing a new record.
    Unload Me
End Sub

The email check is deliberately modest: finding an @ character does not prove that an address exists or is correctly formed. For a real workflow, define suitable validation requirements rather than treating this sample as a complete email validator. The code also assumes the Contacts sheet exists and has headings in row 1; spelling the sheet name differently causes a runtime error.

Opening the Form from a Macro

To give users a reliable way to launch the form, add a standard module in the Visual Basic Editor with Insert > Module. Put this procedure in that module, not in the UserForm’s code module:

Option Explicit

Public Sub OpenContactForm()
    Dim contactForm As frmContact

    Set contactForm = New frmContact
    contactForm.Show vbModal
    Set contactForm = Nothing
End Sub

A modal form, shown with vbModal, keeps focus on the form until it is closed. You can assign the macro to a worksheet button or a shape so users do not need to open the macro dialog. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it must retain its VBA project.

Other Useful UserForm Examples

The same pattern can support many different jobs. Choose controls based on the kind of response needed, then write event procedures that handle a clear action such as searching or updating.

  • Inventory entry: Text boxes capture an item code and quantity, while a ComboBox selects a category. Code can check that the quantity is numeric before adding a row.
  • Record search: A text box accepts an ID or keyword, and a button searches a worksheet table. The form can display a match or explain that no record was found.
  • Task or request intake: A description box, priority list, and due-date field can collect consistent submissions. Validation can flag missing required information before saving.
  • Settings dialog: Check boxes can turn optional behaviors on or off, and buttons can apply or cancel changes. This is useful when users need to change a few workbook settings without editing cells.
  • Navigation menu: Buttons can open specific sheets, reports, or other forms. Keep the menu focused; a long collection of buttons can be harder to use than the workbook tabs it is meant to simplify.

For a search form, the result display might be a ListBox rather than a series of text boxes. For a small fixed choice, a ComboBox or OptionButton group can reduce inconsistent entries such as different spellings of the same category. The best control depends on whether the user should type a value, select one, or select several.

How to Create a UserForm in Excel

On Windows, first show the Developer tab if needed: choose File > Options > Customize Ribbon, select Developer, and choose OK. Open the Visual Basic Editor with Alt+F11, then choose Insert > UserForm. The Toolbox provides controls to place on the form; use the Properties window to set names, captions, and other properties. If the Toolbox or Properties window is hidden, open it from the VBE View menu.

  1. Plan the fields and the destination for the data. For a worksheet-based form, decide the sheet name, column headings, and whether each field is required.
  2. Insert the form, add the controls, and give each control a meaningful (Name), such as txtName or cmdSave. The Name is what VBA refers to; Caption is the visible text.
  3. Open the form’s code window and add event procedures. Double-clicking a button in the form designer commonly creates its Click event procedure.
  4. Add a launch macro in a standard module, save as .xlsm, and try normal, missing, and unexpected input cases.

On a Mac, the Developer tab can be enabled from Excel > Settings (or Preferences, depending on the release) > Ribbon & Toolbar. The Visual Basic Editor shortcut is often Option+F11; some keyboards require Fn as well. Menu labels and available controls can differ by Excel version and operating system, so use the Mac VBE menus if a Windows instruction does not match. Excel for the web cannot create or run VBA UserForms.

How Controls and Events Work

A form is made up of controls, properties, and event procedures. A control’s Name identifies it in code, while properties such as Caption, Value, and Enabled affect what the user sees or can do. A TextBox holds typed text; a ComboBox offers a list and may also permit typing; a ListBox displays selectable items; and a CommandButton typically starts an action.

Event procedures respond to something the user does or to a form lifecycle event. UserForm_Initialize runs when the form is initialized, which is a convenient place to load choices or set defaults. cmdSave_Click runs when the Save button is clicked. Keeping setup code, validation, and saving in appropriate procedures makes a project easier to understand than putting all behavior in one oversized procedure.

For a dropdown that should only accept listed values, set its Style property to 2 - fmStyleDropDownList in the Properties window. That prevents users from typing an unlisted department. A normal ComboBox allows typing, which can be preferable when the list is long or changes often.

Choosing a UserForm Instead of Other Input Methods

A UserForm is not always the simplest choice. If a task involves a handful of cells and users already understand the worksheet, clearly labeled cells with data validation may be easier to maintain. Excel’s built-in data-entry features are visible in the sheet and do not require VBA or macro permissions.

An InputBox is suitable for a quick one-off prompt, such as asking for a short value before a macro runs. It becomes cumbersome when a process needs several fields, dropdowns, instructions, or validation messages. A UserForm gives more control over layout and interaction, but it requires VBA maintenance and a macro-enabled file. Consider who will use the workbook, whether macros are permitted in their organization, and whether the form must work in a browser before choosing.

Common Problems and Safe Use

  • “Subscript out of range”: Check that the worksheet name in the code exactly matches the tab name. Also confirm that the code is using the intended workbook.
  • “Object required” or a missing control error: Check the control’s (Name) property against the code. A control’s visible Caption does not set its VBA name.
  • Form does not open: Confirm the launch procedure is in a standard module, the form name is correct, and macros are allowed. Opening a workbook with macros disabled can prevent VBA from running.
  • New entries overwrite existing data: Make sure the chosen column is consistently populated and that the worksheet has the expected headers. For more complex sheets, use an Excel Table or a deliberate record-finding method rather than assuming column A always identifies the last record.

Before running or distributing a macro, save a separate copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a mistaken write may require restoring a backup. Do not enable macros in an untrusted workbook merely to view a form. Only run code from a source you trust, and follow your organization’s macro-security rules. On Windows, macro settings are under File > Options > Trust Center > Trust Center Settings > Macro Settings; avoid lowering security globally just to make one workbook work.

Frequently Asked Questions

How do I show a UserForm when a workbook opens?
Put a call such as frmContact.Show in the Workbook_Open event in the ThisWorkbook code module. Save as .xlsm and remember that the event runs only when macros are enabled. A launch button is often less intrusive because it lets users choose when to open the form.

Can I use an Excel VBA UserForm without macros?
No. UserForms are part of a VBA project and need VBA to run. If macros are disabled, the form’s code will not provide its interactive behavior. Consider worksheet cells with data validation or a non-VBA form tool if users cannot run macros.

Can UserForms run 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 or display VBA UserForms. Use desktop Excel for the form, or choose a web-compatible data-collection approach if browser use is required.

What is the difference between a UserForm Name and Caption?
Name is the identifier used by VBA, for example frmContact or cmdSave. Caption is the text displayed to the user, such as “Contact details” or “Save.” Changing a caption does not rename the object in code.

How do I clear a form after saving?
The sample closes the form with Unload Me. To keep it open for another entry, leave it loaded and clear the controls after a successful save, for example by assigning an empty string to text boxes and resetting the ComboBox selection. Decide whether users should enter one record or several before choosing that behavior.

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.