Excel VBA UserForm Coding: Build a Data-Entry Form

VBA & macros · Updated November 21, 2026

Excel VBA UserForm coding means creating a custom dialog in Visual Basic for Applications and writing code that responds to its controls. In Microsoft 365 for Windows, you can build a simple data-entry form by adding a UserForm in the VBA editor, placing text boxes and buttons on it, and using a button’s click event to validate and save entries to a worksheet.

What an Excel VBA UserForm Does

A UserForm is a VBA form window that appears when a macro opens it. It can collect information, display choices, and guide a person through a task without requiring them to type directly into worksheet cells. For example, a small team could use a form to enter a person’s name, email address, department, and an optional amount, with each submission appended as a new row in a records sheet.

Forms are useful when a workbook needs a more controlled input process than a blank grid provides. They can make required fields obvious, offer fixed choices in a drop-down list, and show a message when information is missing. They do not, by themselves, make a workbook a database or prevent every kind of bad data. A worksheet, its permissions, and the form’s validation still need to be designed appropriately for the people and information involved.

How UserForms and VBA Events Work

A form contains controls, such as text boxes, labels, combo boxes, and command buttons. Each control has properties, including its name, caption, size, and position. VBA code can read or change those properties. The code also uses event procedures: for instance, btnSave_Click runs when a user clicks a button named btnSave.

The usual sequence is to open the form, initialize its controls, collect values, check them, and then write them to a worksheet. The form is a front end; the worksheet is where this example stores the records. Keeping the worksheet column order and the code’s output order consistent makes later sorting, filtering, and reporting easier.

Create the Form and Add Its Controls

First save a working copy of the workbook as an Excel Macro-Enabled Workbook (.xlsm). On Windows, show the Developer tab from File > Options > Customize Ribbon by selecting Developer. Then press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > UserForm. If the Toolbox is not visible, use View > Toolbox.

Add the controls below by choosing them in the Toolbox and drawing them on the form. Select each control and use the Properties window to set its (Name) property. The name is what VBA uses; the caption is the text a user sees.

  • Add four labels and four input controls: text boxes named txtName, txtEmail, and txtAmount, plus a combo box named cboDepartment.
  • Add command buttons named btnSave, btnClear, and btnCancel. Set their Caption properties to Save, Clear, and Cancel.
  • Set the form’s (Name) property to frmEntry and its Caption property to something descriptive, such as New record.
  • Create a worksheet named Records. Put the headers Name, Email, Department, and Amount in cells A1:D1.

Keep labels beside their matching inputs and arrange the buttons consistently. A sensible tab order lets a person move through fields with Tab instead of clicking each one. You can adjust tab order in the form designer, and test it after changing the layout.

Write the Save, Clear, and Cancel Code

Double-click the form background to open its code module. Add the following code there, not in a worksheet module or a standard module. It assumes the control names and worksheet layout above. The department choices are examples; replace them with options that fit the workbook.

Option Explicit

Private Sub UserForm_Initialize()
    ' Load the choices each time the form opens.
    With Me.cboDepartment
        .AddItem "Finance"
        .AddItem "Operations"
        .AddItem "Sales"
    End With
End Sub

Private Sub btnSave_Click()
    Dim ws As Worksheet
    Dim nextRow As Long
    Dim amountValue As Double
    Dim hasAmount As Boolean

    ' Check required entries before writing a row.
    If Len(Trim$(Me.txtName.Value)) = 0 Then
        MsgBox "Enter a name before saving.", vbExclamation
        Me.txtName.SetFocus
        Exit Sub
    End If

    If Len(Trim$(Me.txtEmail.Value)) = 0 Then
        MsgBox "Enter an email address before saving.", vbExclamation
        Me.txtEmail.SetFocus
        Exit Sub
    End If

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

    ' Allow the amount to be blank, but reject nonnumeric text.
    hasAmount = (Len(Trim$(Me.txtAmount.Value)) > 0)
    If hasAmount Then
        If Not IsNumeric(Me.txtAmount.Value) Then
            MsgBox "Enter a numeric amount or leave the field blank.", vbExclamation
            Me.txtAmount.SetFocus
            Exit Sub
        End If
        amountValue = CDbl(Me.txtAmount.Value)
    End If

    ' Records must exist and have headers in row 1.
    Set ws = ThisWorkbook.Worksheets("Records")
    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)
    ws.Cells(nextRow, 3).Value = Me.cboDepartment.Value
    If hasAmount Then
        ws.Cells(nextRow, 4).Value = amountValue
    Else
        ws.Cells(nextRow, 4).ClearContents
    End If

    MsgBox "Record saved.", vbInformation
    ClearForm
End Sub

Private Sub btnClear_Click()
    ClearForm
End Sub

Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub ClearForm()
    Me.txtName.Value = vbNullString
    Me.txtEmail.Value = vbNullString
    Me.cboDepartment.ListIndex = -1
    Me.txtAmount.Value = vbNullString
    Me.txtName.SetFocus
End Sub

The save procedure finds the next available row by looking down column A, which is why the name field is written there and the header row must be present. It writes the four values in the same order as the headers. The amount field is optional; if it contains text, IsNumeric checks whether VBA can interpret it as a number, and CDbl converts it before writing. Number interpretation can depend on regional settings, so test decimal and thousands separators in the environment where the workbook will be used.

The email check here only requires a nonblank entry; it does not verify that an address is deliverable or fully valid. More elaborate checks are possible, but simple validation should not be presented as proof that contact details are correct. Likewise, if the Records sheet is missing or renamed, the save procedure will raise a VBA error. Create that sheet with the expected name, or add explicit error handling if users may change workbook structure.

Open the Form from a Workbook

To show the form during development, click inside a procedure in its code module and press F5. For a normal workbook workflow, add a macro in a standard module and assign that macro to a worksheet button or another suitable trigger. In the VBA editor, choose Insert > Module, then add:

Option Explicit

Public Sub OpenEntryForm()
    ' Display the data-entry form.
    frmEntry.Show
End Sub

To assign it to a worksheet button, return to Excel, choose Developer > Insert > Button (Form Control), draw the button, and select OpenEntryForm when prompted. This is a Form Control button, not an ActiveX control. A shape can also be assigned a macro through its context menu. Use clear button text and consider putting brief instructions near the form trigger.

Test the Form and Protect the Workbook

Before using the form for real records, test normal and incorrect entries in a copy of the workbook. Confirm that each saved value lands under the correct header, that a blank required field stops the save, that an unselected department is rejected, and that a blank amount stays blank. Also check that Clear empties the controls and Cancel closes the form without adding a row.

Save the file as .xlsm; an ordinary .xlsx file cannot store VBA macros. Excel may show a security warning when a macro-enabled workbook is opened. Only enable macros in files you trust and understand. VBA code can change workbook data, files, and other settings, so macro security is a real consideration, not just a setup hurdle. Keep an untouched backup before running unfamiliar code. Macros cannot be undone with Ctrl+Z, and saving after an unintended change can make recovery harder.

Common Problems and Practical Fixes

  • “Subscript out of range” on save: check that the worksheet is named exactly Records and belongs to the workbook containing the form. Spelling and spaces matter.
  • “Method or data member not found” while editing: compare every control name in the code with the control’s (Name) property. A caption such as Save is not the same as the name btnSave.
  • The form opens but the list is empty: confirm that the initialization procedure is named UserForm_Initialize and that the combo box is named cboDepartment.
  • Values appear in the wrong columns: compare the worksheet headers, cell assignments, and expected data order. The example writes name, email, department, and amount to columns A through D.
  • The macro will not run: check that the workbook is saved as .xlsm, that macros are permitted under the organization’s security policy, and that the button is assigned to the correct macro.

When debugging, use the Visual Basic Editor’s Debug > Compile VBAProject command to catch some compile-time issues, then step through code with F8 if needed. Compilation will not detect every runtime problem, such as a missing worksheet. Read the error message and inspect the highlighted line instead of suppressing errors indiscriminately with broad On Error Resume Next statements.

When a UserForm Is the Right Choice

A UserForm is a good fit when a workbook needs a custom Windows desktop input window, event-driven behavior, or a guided sequence of fields. It may be excessive for a small list that can be entered directly into a well-structured Excel table with data validation. A table is often easier to filter, extend, and share, while a form gives more control over the entry experience.

VBA UserForms are not available in Excel for the web, which cannot create or run VBA macros. Mac users can use VBA in desktop Excel, but menus, keyboard shortcuts, and some interface details differ from Windows; the Windows-specific Developer and editor paths above may not match exactly. Test any VBA workbook on the intended Mac or Windows version before distributing it. If users need browser-based entry or shared multi-user data collection, consider a suitable web-based form or application rather than assuming a VBA form will work online.

Frequently Asked Questions

How do I create a UserForm in Excel?
Save a macro-enabled workbook, open the Visual Basic Editor, choose Insert > UserForm, add controls from the Toolbox, and write event code in the form’s code module. On Windows, Alt+F11 opens the editor; the Mac shortcut can differ, and Excel for the web does not support VBA UserForms.

Where should UserForm code go?
Put form-specific events, such as a Save button’s click procedure, in that UserForm’s code module. Put a general macro that opens the form in a standard module. Worksheet-specific event code belongs in the corresponding worksheet module.

Can an Excel UserForm save data to a worksheet?
Yes. VBA can read control values and assign them to worksheet cells, as in the example. The destination sheet and column arrangement must match the code, and the workbook should be saved as a macro-enabled file.

Why does a UserForm macro work on Windows but not Excel for the web?
Excel for the web does not run VBA macros or display VBA UserForms. The workbook can be opened there, but the form workflow needs desktop Excel. Mac desktop Excel supports VBA, although platform differences should be checked and the workbook tested on the target version.

Can I undo a UserForm macro with Ctrl+Z?
No. Excel’s Undo history does not reverse VBA macro actions. Save a backup before running code, test on sample records, and review any write or delete logic before using a macro on important data.

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.