Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 37 ยท Language Basics

MsgBox and InputBox: Talking to the User

Show messages and ask questions in VBA with MsgBox and InputBox: button and icon constants, reading the result, Application.InputBox, cancel handling and validation.

Macros often need to tell the user something or ask for a value. VBA's simplest tools for this are MsgBox (show a message, get a button click) and InputBox (ask for text). They are quick, need no form design and cover many small tools.

MsgBox as a statement

VB
MsgBox "Report finished."

This shows an OK button and nothing else. Use vbCrLf for line breaks:

VB
MsgBox "Line one" & vbCrLf & "Line two", vbInformation, "Title text"

The arguments are MsgBox(prompt, buttons, title). The title defaults to the application name.

MsgBox as a function

With parentheses and an assignment, MsgBox returns which button was pressed:

VB
Dim answer As VbMsgBoxResult
answer = MsgBox("Delete all rows?", vbYesNo + vbQuestion + vbDefaultButton2, "Confirm")
If answer = vbYes Then
    Debug.Print "Deleting..."
Else
    Debug.Print "Cancelled"
End If

Button sets

vbOKOnly OK only. The default.
vbOKCancel OK and Cancel.
vbYesNo Yes and No.
vbRetryCancel Retry and Cancel.
1 / 2

You add one value from each group: button set, icon, default button (vbDefaultButton1..3) and modality. Results are vbOK, vbCancel, vbYes, vbNo, vbRetry, vbAbort, vbIgnore.

Use + or Or to combine: vbYesNoCancel + vbExclamation. MsgBox text is limited to about 1,024 characters, so large messages need a UserForm (covered in the Forms chapter later in the course).

Writing MsgBox ("text") with a space and parentheses is allowed for one argument, but the moment you pass more than one argument you must drop the parentheses or use the return value.

InputBox

InputBox asks for a line of text and returns it as a String:

VB
Dim name As String
name = InputBox("What is your name?", "Welcome", "Guest")
If name = "" Then
    MsgBox "No name entered (or Cancel was pressed)."
Else
    MsgBox "Hello, " & name
End If

Arguments: InputBox(prompt, title, default, xpos, ypos). Problem: pressing Cancel and pressing OK with an empty box both return "", so you cannot tell them apart. Use StrPtr(name) = 0 to detect Cancel, or switch to Application.InputBox in Excel.

Application.InputBox (Excel)

VB
Dim n As Variant
n = Application.InputBox("How many copies?", "Print", 1, Type:=1)   ' Type 1 = number
If n = False Then Exit Sub      ' Cancel returns False

The Type argument validates for you: 1 number, 2 text, 4 Boolean, 8 a cell range (the user selects cells with the mouse), 64 an array. For a range you must use Set:

VB
Dim rng As Range
Set rng = Application.InputBox("Select the data", Type:=8)

Validating input

The input is text, so convert and check. A loop keeps asking until the value is valid:

VB
Function AskAge() As Long
    Dim s As String
    Do
        s = InputBox("Enter your age (1-120):", "Age")
        If s = "" Then Exit Function                ' user quit, returns 0
        If IsNumeric(s) Then
            If CLng(s) >= 1 And CLng(s) <= 120 Then
                AskAge = CLng(s): Exit Function
            End If
        End If
        MsgBox "Please enter a whole number from 1 to 120.", vbExclamation
    Loop
End Function

(Note this uses a Do...Loop; loops come in Do Loops.) A careless CLng(s) on "abc" raises error 13, which is why IsNumeric is tested first.

Stand-in: validation logic you can run

The dialogs themselves cannot appear here, but the validation logic can. This JavaScript stand-in (not VBA) runs the age check on a list of pretend answers.

JS Stand-in: validating pretend input

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

No output.

It prints rejections for "abc", "200" and "34.5", then age = 42.

Good practice

  • Keep dialogs for decisions and short prompts; use cells or forms for lots of input.
  • Do not display a MsgBox inside a long loop; it makes the macro unusable. Use Application.StatusBar instead.
  • Always give dialogs a meaningful title and icon.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you get the button the user clicked?

    answer = MsgBox(prompt, buttons, title) returns vbYes, vbNo and so on.
  2. 2. Which expression shows Yes and No buttons with a question icon?

    Add one constant from the button group and one from the icon group.
  3. 3. What does InputBox return when the user presses Cancel?

    InputBox returns an empty string, the same as an empty OK. Application.InputBox returns False.
  4. 4. Which Application.InputBox Type value lets the user pick a range?

    Type 8 returns a Range object, assigned with Set.

Summary

  • MsgBox shows messages; with parentheses it returns the clicked button.
  • Combine button, icon and default constants with +.
  • InputBox returns text; validate with IsNumeric before converting.
  • Application.InputBox offers typed input and range selection in Excel.

Next: If...Then...Else