Macro to Create a Message Box and Input Box

Message boxes and input boxes are how a macro talks to the person using it: showing a message, asking Yes or No, or asking for a number or a range. Here are the versions you’ll use most.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Show a message

Sub ShowMessage()
    MsgBox "Report updated successfully.", vbInformation, "XL n CAD"
End Sub

The second part sets the icon: vbInformation, vbExclamation, vbCritical or vbQuestion. The third is the title.

Ask Yes or No

Sub AskYesNo()
    If MsgBox("Delete the old data?", vbYesNo + vbQuestion) = vbYes Then
        Range("A2:D100").ClearContents
    End If
End Sub

Use vbYesNoCancel or vbOKCancel for other buttons, and check for vbNo, vbCancel or vbOK.

Ask for text

Sub AskForName()
    Dim n As String
    n = InputBox("What's your name?", "Welcome")
    If n = "" Then Exit Sub
    MsgBox "Hello, " & n & "!"
End Sub

If the user clicks Cancel, InputBox returns an empty string, so always check for it.

Ask for a number or a range

Sub AskForNumberAndRange()
    Dim n As Double, r As Range
    n = Application.InputBox("Increase by what %?", Type:=1)
    On Error Resume Next
    Set r = Application.InputBox("Select the cells:", Type:=8)
    On Error GoTo 0
    If r Is Nothing Then Exit Sub
    Dim c As Range
    For Each c In r
        If Not IsEmpty(c) And IsNumeric(c.Value) Then c.Value = c.Value * (1 + n / 100)
    Next c
End Sub

Application.InputBox can insist on a type: Type:=1 for a number, Type:=2 for text and Type:=8 for a range the user selects with the mouse.

MsgBox button options

Things to know

• Use vbLf to start a new line in a message: MsgBox "Line 1" & vbLf & "Line 2".
• On Error Resume Next around the range InputBox stops an error when the user clicks Cancel.

Related macros