Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 37 ยท Forms, Events and Errors

VBA UserForms Basics

Build your first VBA UserForm: add controls, show it modal or modeless, read values, handle Initialize and Click events, and pass data back to the sheet.

A UserForm is a custom dialog box that you design in the VBA editor. It lets people enter data, make choices and press buttons, instead of typing into cells or answering InputBox prompts. Forms make macros usable by people who do not know VBA.

A UserForm has two parts: the visual design (controls dragged from the Toolbox) and the code behind it (event procedures such as CommandButton1_Click). This lesson covers creating a form, showing it, reading what the user typed and writing it to a sheet.

VBA and UserForms cannot run on this site, so the VBA is read-only. The JavaScript examples model the same event-driven ideas so you can practice the logic.

Creating a form

  1. In the VBA editor choose Insert > UserForm.
  2. Open the Properties window (F4) and set (Name) to something meaningful, for example frmCustomer, and Caption to the title text.
  3. Drag controls from the Toolbox: Label, TextBox, ComboBox, CheckBox, OptionButton, CommandButton, ListBox.
  4. Name every control with a prefix: txtName, cboCountry, chkNewsletter, cmdOK, cmdCancel.

Good names make the code readable: txtName.Value says more than TextBox1.Value.

Showing and hiding

VB
Sub OpenForm()
    frmCustomer.Show              ' modal: blocks the sheet until closed
End Sub

Sub OpenFormModeless()
    frmCustomer.Show vbModeless   ' user can keep working in Excel
End Sub

Inside the form, Me refers to the form itself.

  • Me.Hide hides it but keeps the object and the values in memory.
  • Unload Me closes it and frees the memory; the next Show starts fresh.

Pick one on purpose. A common pattern is: code after Show reads the values, so the form hides itself and the caller unloads it afterwards.

The first events

Double-click the form or a control in the designer to create an event procedure.

VB
Private Sub UserForm_Initialize()
    cboCountry.AddItem "Germany"
    cboCountry.AddItem "Japan"
    cboCountry.AddItem "Brazil"
    cboCountry.ListIndex = 0
    txtName.SetFocus
End Sub

Private Sub cmdOK_Click()
    If Len(Trim$(txtName.Value)) = 0 Then
        MsgBox "Please enter a name.", vbExclamation
        txtName.SetFocus
        Exit Sub
    End If
    WriteToSheet
    Unload Me
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

UserForm_Initialize runs once when the form is first loaded: the right place to fill drop-downs and set defaults. Set cmdOK.Default = True so Enter presses OK, and cmdCancel.Cancel = True so Esc presses Cancel.

Writing the data to a sheet

VB
Private Sub WriteToSheet()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Customers")
    Dim r As Long
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(r, 1).Value = Trim$(txtName.Value)
    ws.Cells(r, 2).Value = cboCountry.Value
    ws.Cells(r, 3).Value = chkNewsletter.Value      ' True / False
End Sub

The Value property of a TextBox is always text. Convert with CDbl, CLng or CDate after checking it is valid (see the next lesson).

Opening the form from a button

Put a button on a worksheet (Developer > Insert > Button), assign OpenForm to it, or add the macro to the Quick Access Toolbar. For add-ins, use a ribbon button.

The event-driven idea

sequenceDiagram
  participant U as User
  participant F as UserForm
  participant S as Sheet
  U->>F: opens form (Show)
  F->>F: Initialize fills lists
  U->>F: types and clicks OK
  F->>F: cmdOK_Click validates
  F->>S: writes a row
  F->>U: Unload Me closes the form

Your code does not run in a straight line: the form waits, and each user action triggers the matching event procedure.

Try it: simulate form events

JS A tiny event-driven form model

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Hide versus Unload

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Leaving default control names (TextBox1), which makes code unreadable.
  • Using Unload Me and then reading a control afterwards, which silently reloads a blank form.
  • Forgetting that TextBox.Value is text and needs conversion.
  • Putting setup code in the form's Show caller instead of Initialize.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between Me.Hide and Unload Me?

    After Hide the form can be shown again with its old values; Unload resets it.
  2. 2. Where should you fill a ComboBox with items?

    Initialize runs once when the form loads, the right place for defaults and list items.
  3. 3. How do you make Esc trigger the Cancel button?

    The Cancel property makes Esc click that button; Default makes Enter click it.
  4. 4. What type does TextBox.Value return?

    Text boxes always hold text; convert with CLng, CDbl or CDate after validating.

Summary

  • Insert a UserForm, name it and every control, and design it from the Toolbox.
  • Show displays it (modal by default); Hide keeps state, Unload Me clears it.
  • UserForm_Initialize prepares the form; control events such as cmdOK_Click react to the user.
  • Read values from controls and write them to a sheet; convert text to numbers or dates explicitly.

Next: UserForm Controls and Validation