VBA Course: MsgBox and InputBox

MsgBox

So far, we have used the MsgBox dialog box only to display information:

Sub clearB2()

    Range("B2").ClearContents
    MsgBox "The content of B2 has been cleared!"

End Sub

In this case, MsgBox is used with only one argument.

Preview of the result of this code:

1 dialog boxes

Now, we will create a dialog box that asks for confirmation before executing the instructions.

Here are the 3 arguments we will provide:

MsgBox([TEXT], [BUTTONS], [TITLE])
Sub clearB2()

    If MsgBox("Are you sure you want to delete the contents of B2?", vbYesNo, "Confirmation") = vbYes Then
        Range("B2").ClearContents
        MsgBox "The content of B2 has been cleared!"
    End If

End Sub

Preview:

confirmation dialog boxes

vbYesNo indicates that the dialog box has "Yes" and "No" buttons, and vbYes corresponds to the "Yes" button:

If MsgBox("Text", vbYesNo, "Title") = vbYes Then 'If the Yes button is clicked
MsgBox can be used as both a procedure and a function. If you need to know the user's choice and get a return value, use MsgBox as a function by adding parentheses () to the arguments.

Different Options for the Second Argument of MsgBox

ConstantValueDescription
vbOKOnly0ok dialog boxes
vbOKCancel1ok dialog boxescancel dialog boxes
vbAbortRetryIgnore2abort dialog boxesretry dialog boxesignore dialog boxes
vbYesNoCancel3yes dialog boxesno dialog boxescancel dialog boxes
vbYesNo4yes dialog boxesno dialog boxes
vbRetryCancel5retry dialog boxescancel dialog boxes
vbCritical16critical dialog boxes
vbQuestion32question dialog boxes
vbExclamation48exclamation dialog boxes
vbInformation64information dialog boxes
vbDefaultButton10Default Button: Button 1
vbDefaultButton2256Default Button: Button 2
vbDefaultButton3512Default Button: Button 3
vbApplicationModal0Forces the user to respond before continuing with Excel
vbSystemModal4096Forces the user to respond before continuing with other applications (dialog box in the foreground)

Values at 0 are the defaults.

The second argument of MsgBox can take multiple values from this table.

For example, for a dialog box with "Yes, No, Cancel" + exclamation icon + default button 2:

MsgBox("Text", vbYesNoCancel + vbExclamation + vbDefaultButton2, "Title")

Preview:

2 dialog boxes

The constants can be replaced with their respective values. These 3 lines display an identical dialog box:

MsgBox("Text", vbYesNoCancel + vbExclamation + vbDefaultButton2, "Title")
MsgBox("Text", 3 + 48 + 256, "Title")
MsgBox("Text", 307, "Title")

Values Returned by MsgBox

ConstantValueButton Corresponding to the Value
vbOK1ok dialog boxes
vbCancel2cancel dialog boxes
vbAbort3abort dialog boxes
vbRetry4retry dialog boxes
vbIgnore5ignore dialog boxes
vbYes6yes dialog boxes
vbNo7no dialog boxes

Here's an example of a MsgBox that appears in a loop until the "Yes" button is clicked:

Sub humor()

    Do
        If MsgBox("Do you like the Excel-Pratique website?", 36, "Survey") = vbYes Then
            Exit Do 'Exit the loop if the response is Yes
        End If
    Loop While True 'Infinite loop

    MsgBox ";-)"

End Sub

New Line in MsgBox

To create a new line, you can insert the corresponding line break character using the Chr function, for example:

MsgBox "Example 1" & Chr(10) & "Example 2" & Chr(10) & Chr(10) & "Example 3"

Preview:

10 dialog boxes

InputBox

The InputBox function asks the user to enter a value in a dialog box. Here's an example:

Sub example()

    Dim result As String
    
    result = InputBox("Text?", "Title") 'The variable receives the value entered in the InputBox
    
    If result <> "" Then 'If the value is not empty, display the result
        MsgBox result
    End If

End Sub

Preview:

input dialog boxes

You can also provide a default value as the third argument:

InputBox("Text?", "Title", "Default Value")

Preview:

input2 dialog boxes