VBA UserForm in Excel — Build a Real Data-Entry Form

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-06

TL;DR — A UserForm is a custom dialog you design once and drive with event code. Add controls, write what each button does, then show it. The whole thing in three pieces:

' 1. In the form module — code that runs when OK is clicked:
Private Sub btnOK_Click()
    If txtName.Value = "" Then
        MsgBox "Name is required.", vbExclamation
        Exit Sub
    End If
    Me.Hide          ' hide, but KEEP the values readable by the caller
End Sub

' 2. In the form module — Cancel just closes it:
Private Sub btnCancel_Click()
    Me.Tag = "cancel"
    Me.Hide
End Sub

' 3. In a normal module — show it and read what was typed:
Sub AddRecord()
    With frmEntry
        .Show                              ' blocks here until the form hides
        If .Tag = "cancel" Then Unload frmEntry: Exit Sub
        Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = .txtName.Value
    End With
    Unload frmEntry                        ' destroy it only after reading
End Sub

If you've ever wondered "why is the textbox empty after the user filled it in?" — that's the Hide vs Unload rule, and it's the heart of this guide.

The mental model: you stop driving, the form does

MsgBox and InputBox are synchronous: one line runs, you get one value back, the next line runs. A UserForm breaks that rhythm. When you call .Show, your code hands the wheel to the form and pauses. What happens next isn't decided by the next line of your macro — it's decided by which control the user clicks, and each control runs its own little chunk of code.

So the question every beginner asks — "where did my code go after UserForm1.Show?" — has a clean answer: it's waiting. The real work moved into the controls' event procedures, like btnOK_Click. A UserForm is three things glued together:

1. The design — the controls you drag on (TextBox, ComboBox, CommandButton), set up once at design time.
2. The event code — Private Sub procedures inside the form's module, one per thing the user can do.
3. The call — frmEntry.Show from a normal module.

Once you see it as "an object that runs code when poked," not "a function that returns a value," UserForms stop being mysterious.

Build one in three steps

1. In the VBA editor (Alt+F11), Insert ▸ UserForm. In Properties, set (Name) to frmEntry — naming forms and controls now saves you from TextBox1, TextBox2 chaos later.
2. From the Toolbox, drag on: two TextBox controls (txtName, txtQty), one ComboBox (cboDept), and two CommandButton controls (btnOK, btnCancel). Add labels next to each.
3. Double-click a button — the editor drops you straight into its _Click event. That's where its behaviour lives.

The one rule: Me.Hide keeps values, Unload Me destroys them

This is the rule that decides whether your form actually works as a data-entry tool.

• Unload Me removes the form from memory and resets every control to empty. Read txtName.Value after an Unload and you get "" — the data is gone.
• Me.Hide makes the form invisible but leaves it alive in memory, values intact. Execution returns to the line after .Show, and the caller can still read frmEntry.txtName.Value.

So the pattern for any form that returns data is: OK calls Me.Hide, the caller reads the controls, and only then does the caller Unload. Doing Unload Me inside btnOK_Click is the single most common reason people say "the form loses what I typed."

Private Sub btnOK_Click()
    ' validate first, then HIDE (do not Unload here)
    If Not IsNumeric(txtQty.Value) Then
        MsgBox "Quantity must be a number.", vbExclamation
        txtQty.SetFocus
        Exit Sub
    End If
    Me.Hide
End Sub

Set the form up in UserForm_Initialize

Anything the form needs before the user sees it — dropdown choices, default values, today's date — belongs in the UserForm_Initialize event, not in the calling macro. That keeps the form self-contained: it's correct every time it opens, no matter who shows it.

Private Sub UserForm_Initialize()
    cboDept.List = Array("Sales", "Finance", "Ops")   ' fill the dropdown
    txtQty.Value = "1"                                ' sensible default
    txtName.SetFocus                                  ' cursor starts here
End Sub

Modal or modeless — pick on purpose

.Show defaults to modal (vbModal): the user must deal with the form before touching the sheet, and your calling code waits. That's what you want for data entry. Use .Show vbModeless only for a floating helper palette the user keeps open while they work — and know that your calling macro then runs straight on without waiting.

frmEntry.Show               ' modal — caller pauses (the usual choice)
frmTools.Show vbModeless    ' modeless — caller keeps running, form floats

Don't over-wire it

A tempting trap is to scatter _Change events across every control for "live" validation. They fire constantly, trigger each other, and turn debugging into whack-a-mole. Validate once, in btnOK_Click, then hide. Keep each event procedure short and single-purpose.

And know when a UserForm is the wrong answer entirely: if you're laying out 10+ fields and it spills past one screen, that's a signal to split it across a MultiPage — or to admit the data really belongs in a plain Excel table the user types into directly. A UserForm earns its keep at roughly 3–8 fields with real validation; below that, an InputBox chain is lighter, and above that, a worksheet often beats a form.

Common UserForm mistakes (and the fix)

A form is a lot of plumbing for "ask me three things"

UserForms are the right tool for real data entry — but the controls, the Initialize, the Hide-vs-Unload dance, and the validation are a lot of wiring before a single row gets saved. When the goal is just "collect a name, quantity and department, then append them as a row," ExcelMaster Agent lets you describe it in plain English and builds the input flow and the write-back for you — no form module to maintain. Try it free →

Related guides

• VBA MsgBox in Excel — Yes/No, Buttons & the Brackets Rule
• VBA InputBox in Excel — The Two InputBoxes & When to Use Each
• VBA Range in Excel — Reference, Read & Write Cells
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

What is a UserForm in VBA? A UserForm is a custom dialog box you design in the VBA editor and control with event code. You drag on controls (text boxes, combo boxes, buttons), write a procedure for each thing the user can do, then display it with .Show. It's how VBA collects several inputs at once, beyond what MsgBox or InputBox can do.

How do I create a UserForm in Excel VBA? Press Alt+F11, then Insert ▸ UserForm. Drag controls from the Toolbox onto it, name them in the Properties window, and double-click a button to write its _Click event. Show the form from a normal module with frmName.Show.

Why is my UserForm textbox empty after the user types in it? Because you called Unload Me inside the OK button, which destroys the form and clears every control. Use Me.Hide in the OK event instead — it keeps the form (and its values) in memory so the calling code can read frmName.txtName.Value, then Unload it afterwards.

What is the difference between Unload Me and Me.Hide? Me.Hide makes the form invisible but keeps it loaded, so its control values are still readable. Unload Me removes the form from memory and resets all controls. Use Hide when you need to read the inputs after the form closes; Unload when you're truly done.

How do I fill a ComboBox in a UserForm? Populate it in the UserForm_Initialize event so it's ready before the form appears: cboDept.List = Array("Sales", "Finance", "Ops"), or set cboDept.RowSource = "Sheet1!A1:A10" to bind it to a worksheet range.

Should the form be modal or modeless? Use modal (the default .Show) for data entry — the user must finish the form before doing anything else, and your code waits for the result. Use .Show vbModeless only for a floating toolbar-style form the user keeps open while working on the sheet.