5. VBA: UserForms

A UserForm is your own input window. In the VBA editor choose Insert › UserForm, then add controls from the Toolbox: Label (a caption), TextBox (a field), ComboBox (a drop-down list), ListBox, CheckBox, and CommandButton (a button). You set control names in the Properties window (Name). Good names are txtAmount, cmbCity, btnOK.

• show the form: frmRequest.Show (modal by default: the sheet is unavailable while the form is open);
• UserForm_Initialize is the event before the form appears. This is where you fill lists with .AddItem;
• btnOK_Click runs when the button is clicked;
• Unload Me closes the form and unloads it from memory; Me.Hide only hides it, and the field values are kept.

Private Sub UserForm_Initialize()
    cmbCity.AddItem "Boston"
    cmbCity.AddItem "Chicago"
    cmbCity.AddItem "Denver"
End Sub

Private Sub btnOK_Click()
    Dim r As Long
    If cmbCity.Value = "" Then MsgBox "Choose a city": Exit Sub
    If Not IsNumeric(txtAmount.Value) Then MsgBox "The amount must be a number": Exit Sub
    With Worksheets("Requests")
        r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1    ' first empty row
        .Cells(r, 1).Value = Date
        .Cells(r, 2).Value = cmbCity.Value
        .Cells(r, 3).Value = CDbl(txtAmount.Value)
    End With
    Unload Me
End Sub

Everything typed into a TextBox is text. Before calculating, check with IsNumeric and convert (CDbl, CLng), or the sheet will receive the text "1500", which doesn't add up. The With … End With block lets you avoid repeating Worksheets("Requests"): everything that starts with a dot refers to that sheet.

Forms exist only in desktop Excel. Excel for the web and Office Scripts don't have them. For input there, people use tables, Microsoft Forms, or Power Apps.

The simulator doesn't run macros or scripts. Run the code from the lesson in desktop Excel (VBA) or in Excel for the web (Office Scripts). Here you learn to read code and check yourself with formulas and a quiz.