VBA UserForm

A UserForm is a custom dialog box, your own window with your own controls, that a macro can display to collect input or show output. It turns a macro you run into a tool anyone can run. Think about what you'd otherwise ask of a colleague: edit cell B2, press Alt+F8, pick the right macro name and hope. With a form you hand them a button that opens a clean window with labelled boxes. Insert one with Insert ▸ UserForm in the VBE. Beside it floats the Toolbox with the controls, and the Properties window (F4) edits the selected control's design-time properties. Double-click a control and you land in its code module, where its events live.

These are the controls you'll use ninety percent of the time:

• Label is static text, the caption beside a box. The user can't interact with it.
• TextBox takes free text. Its .Value (or .Text) is always a String, even when the user types a number. Every numeric check further down exists because of that one fact.
• ComboBox is a drop-down. The user picks one item, and can type a new one if .Style allows it. Fill it with .AddItem or .List.
• ListBox shows a visible list. It can be single- or multi-select depending on .MultiSelect.
• CheckBox is an independent on/off switch, and its .Value is True/False.
• OptionButton is a radio button. Only one button inside a Frame can be on at a time, so group related options in a Frame or they all fight each other.
• CommandButton is a clickable button, and its _Click event does the work. Set one button's .Default = True so it fires on Enter, and a Cancel button's .Cancel = True so it fires on Esc.

A few properties are worth setting at design time. Start with (Name). Rename every control you'll reference in code from the default TextBox1 to something meaningful like txtAmount, cboCategory or btnOK. Future-you reading If txtAmount.Value = "" will thank present-you. Then set Caption for labels and buttons, and TabIndex for the order the Tab key moves through controls. .Value/.Text hold the current contents.

How do you show a UserForm, and which events matter?

To show the form from a normal Sub, call frmEntry.Show. By default a form is modal. It blocks the rest of Excel until it closes, and for an input dialog that's what you want. Inside the form's own code, Me refers to the form. Unload Me closes it and destroys its state. Me.Hide only hides it, so the code that called .Show can still read what the user typed. Put those together and you get the two-step "OK hides, caller reads, caller unloads" pattern, which is how a form hands data back to the routine that launched it.

You'll lean on three events. UserForm_Initialize runs once, as the form loads and before it's visible, so use it to fill combo boxes, set defaults, and stamp today's date. A CommandButton's _Click runs when it's clicked, and it's the place to validate and act. A control's _Change runs on every edit. Use it sparingly, for example to enable the OK button only once the required boxes are filled.

Private Sub UserForm_Initialize()
    With Me.cboCategory
        .AddItem "Travel"
        .AddItem "Meals"
        .AddItem "Software"
        .AddItem "Office"
    End With
    Me.txtDate.Value = Format(Date, "yyyy-mm-dd")   ' sensible default
End Sub

How do you validate input in a UserForm?

Most of the code behind a good form is validation. A UserForm's entire reason to exist is to stop bad data reaching your sheet. Every value a TextBox yields is a String, so "is this a positive number?" is a question you have to answer in code. No control will answer it for you.

Private Sub btnOK_Click()
    ' 1) required
    If Trim(Me.txtVendor.Value) = "" Then
        MsgBox "Vendor is required.", vbExclamation
        Me.txtVendor.SetFocus
        Exit Sub
    End If
    ' 2) numeric and positive
    If Not IsNumeric(Me.txtAmount.Value) Then
        MsgBox "Amount must be a number.", vbExclamation
        Me.txtAmount.SetFocus: Exit Sub
    End If
    If CDbl(Me.txtAmount.Value) <= 0 Then
        MsgBox "Amount must be greater than zero.", vbExclamation
        Me.txtAmount.SetFocus: Exit Sub
    End If
    ' 3) from the allowed list
    If Me.cboCategory.ListIndex = -1 Then
        MsgBox "Pick a category from the list.", vbExclamation
        Me.cboCategory.SetFocus: Exit Sub
    End If
    ' 4) a real date
    If Not IsDate(Me.txtDate.Value) Then
        MsgBox "Enter a valid date (yyyy-mm-dd).", vbExclamation
        Me.txtDate.SetFocus: Exit Sub
    End If
    ' all good — hand off and close
    WriteExpenseRow Me.txtVendor.Value, Me.cboCategory.Value, _
                    CDbl(Me.txtAmount.Value), CDate(Me.txtDate.Value)
    Unload Me
End Sub

Look at the shape of it. Each check that fails shows a message, returns focus to the offending control, and Exit Subs, so nothing is written until every gate passes. IsNumeric and IsDate are the built-in testers. CDbl/CDate convert only after the test proves the conversion is safe. ListIndex = -1 means "nothing selected" in a combo or list box. You'll reuse this validation ladder on every form you ever build, so it's worth getting into your fingers now.