UserForms, MsgBox and InputBox

Prompt and validate with the built-in dialogs, lay out a UserForm, and pass values back to the caller cleanly.

MsgBox and InputBox

Sub BuiltInDialogs()
    Dim answer As VbMsgBoxResult
    Dim text As String

    ' the return value is what makes MsgBox useful
    answer = MsgBox("Delete the selected rows?", _
                    vbYesNo + vbQuestion + vbDefaultButton2, _
                    "Confirm")
    If answer <> vbYes Then Exit Sub

    ' a three-way prompt, and a warning icon
    answer = MsgBox("Save changes before closing?", _
                    vbYesNoCancel + vbExclamation, "Unsaved work")
    Select Case answer
        Case vbYes:    Debug.Print "save"
        Case vbNo:     Debug.Print "discard"
        Case vbCancel: Exit Sub
    End Select

    ' InputBox returns "" when the user clicks OK with nothing typed,
    ' and returns "" when the user cancels. They are indistinguishable.
    text = InputBox("Customer name:", "New customer", "ada")
    If Len(text) = 0 Then Exit Sub

    ' Application.InputBox has a Type argument and returns False on cancel,
    ' which makes the two cases distinguishable. Type 1 = number, 2 = text,
    ' 8 = a Range the user selects with the mouse.
    Dim v As Variant
    v = Application.InputBox("Pick a range:", "Range", Type:=8)
    If VarType(v) = vbBoolean Then Exit Sub        ' the user cancelled
    Debug.Print v.Address

    ' build a multi-line message
    MsgBox "Summary" & vbCrLf & String$(20, "-") & vbCrLf & _
           "Rows: 10" & vbCrLf & "Total: 1,234.56", vbInformation, "Report"
End Sub

• The vbCrLf constant is the correct line break in a message box. vbLf alone shows as a box or is ignored in some Office dialogs.
• MsgBox blocks the calling code until the user responds, which is why a macro that shows one inside a loop over a thousand rows is unusable.
• Application.InputBox is the one to use when you need a number or a range: it validates the type and its cancel behaviour is unambiguous.
• Set the vbDefaultButton2 flag on any confirmation where the destructive answer is Yes, so an accidental Enter does not delete data.

Building a UserForm

' ---- in the UserForm module: frmCustomer ----
Option Explicit

' A public property is how the form hands a result back to the caller.
' Nothing outside should read the controls directly.
Public Property Get CustomerName() As String
    CustomerName = Trim$(txtName.Text)
End Property

Public Property Get Quantity() As Long
    Quantity = CLng(Val(txtQuantity.Text))
End Property

Public Property Get Cancelled() As Boolean
    Cancelled = mCancelled
End Property

Private mCancelled As Boolean
Private mValidated As Boolean

Private Sub UserForm_Initialize()
    ' runs before the form is shown: fill lists, set defaults
    cboCountry.List = Array("UK", "US", "DE")
    cboCountry.ListIndex = 0
    txtQuantity.Text = "1"
    mCancelled = True             ' assume cancel until OK is pressed
End Sub

Private Sub btnOK_Click()
    If Not Validate() Then Exit Sub
    mValidated = True
    mCancelled = False
    Me.Hide                       ' Hide, do not Unload: the caller still needs the data
End Sub

Private Sub btnCancel_Click()
    mCancelled = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' the X button must set the same flag as the Cancel button
    If CloseMode = vbFormControlMenu Then
        mCancelled = True
    End If
End Sub

Private Function Validate() As Boolean
    If Len(Trim$(txtName.Text)) = 0 Then
        MsgBox "Name is required.", vbExclamation, "Validation"
        txtName.SetFocus
        Exit Function
    End If
    If Val(txtQuantity.Text) <= 0 Then
        MsgBox "Quantity must be a positive number.", vbExclamation, "Validation"
        txtQuantity.SetFocus
        Exit Function
    End If
    Validate = True
End Function

' ---- in a standard module ----
Sub AskForCustomer()
    Dim frm As frmCustomer
    Set frm = New frmCustomer

    frm.Show vbModal             ' vbModal blocks until the form is hidden

    If frm.Cancelled Then
        Unload frm
        Exit Sub
    End If

    Debug.Print frm.CustomerName, frm.Quantity
    Unload frm
End Sub

Use Me.Hide rather than Unload Me inside the form. Unload destroys the form and its controls, so any code that reads a property afterwards reads empty or raises an error.

Modal, modeless and re-use

' vbModal blocks the caller and the spreadsheet until the form is hidden.
' vbModeless lets the user keep working while the form is open.
Sub ShowModeless()
    Dim frm As frmCustomer
    Set frm = New frmCustomer
    frm.Show vbModeless
    ' control returns immediately; do NOT Unload here, or the form vanishes
End Sub

' A modeless form needs its own lifetime management. A common pattern
' is to keep a reference in a module-level variable and let the form
' unload itself:
'   Private Sub btnClose_Click(): Unload Me: End Sub
' and to guard against a second copy:
Public Sub ToggleForm()
    Static showing As Boolean
    If showing Then Exit Sub
    showing = True
    Dim frm As frmCustomer
    Set frm = New frmCustomer
    frm.Show vbModeless
    showing = False
End Sub

' A multi-step wizard: one form, several pages, one visible at a time
Private Sub btnNext_Click()
    If mPage < 3 Then
        mPage = mPage + 1
        ShowPage mPage
    End If
End Sub

Private Sub ShowPage(ByVal page As Long)
    ' Multipage control does this for you; a manual layout needs explicit control
    fraPage1.Visible = (page = 1)
    fraPage2.Visible = (page = 2)
    fraPage3.Visible = (page = 3)
    btnBack.Enabled = (page > 1)
    btnNext.Caption = IIf(page = 3, "Finish", "Next &>")
End Sub

FAQ

Related

Sub, Function and passing arguments Debugging: breakpoints, Watch and the Immediate window

Last refreshed 2026-09-18.