VBA Course: Dialog Boxes

MsgBox

Until now, we have only used MsgBox dialog boxes to display information:

Sub delete_B2()
    Range("B2").ClearContents
    MsgBox "The contents of B2 have been deleted !"
End Sub

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


See code below:

excel 1 dialog boxes

We will now create a dialog box that asks us to confirm the deletion before the two instructions are carried out. Here are the three arguments that we will use:

MsgBox([TEXT], [BUTTONS], [TITLE])
Sub delete_B2()
    If MsgBox("Are you sure that you wish to delete the contents of B2 ?", vbYesNo, "Confirm") = vbYes Then
        Range("B2").ClearContents
        MsgBox "The contents of B2 have been deleted !"
    End If
End Sub

The result:

confirmation dialog boxes

vbYesNo indicates that the dialog box buttons are "Yes" and "No", and vbYes represents the "Yes" button:

If MsgBox("Text", vbYesNo, "Title") = vbYes Then 'If the Yes button is clicked ...

The different ways to use the second argument with MsgBox

ConstantNumerical ValueDescription
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 provide an answer before continuing to use Excel
vbSystemModal4096Forces the user to provide an answer before continuing to use any program on the computer (dialog box in foreground)

The default value is 0.

The second argument that we use with MsgBox have up to one value from each of the separate sections of the table immediately above.

For example, for a dialog box that contains "Yes, No, Cancel" + the exclamation symbol + Default Button 2:

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

See result below:

excel 2 dialog boxes

In place of these constants, we can also use their respective numerical values. For example, the following three lines of code are identical in effect:

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

The values returned by MsgBox

ConstantNumerical ValueButton that corresponds to the numerical value
vbOK1ok dialog boxes
vbCancel2cancel dialog boxes
vbAbort3abort dialog boxes
vbRetry4retry dialog boxes
vbIgnore5ignore dialog boxes
vbYes6yes dialog boxes
vbNo7no dialog boxes

Here is an example of a MsgBox that will continue to pop up in a loop until the user clicks Yes:

Sub humor()
    Do
        If MsgBox("Do you like the Excel-Pratique site ?", vbYesNo, "Survey") = vbYes Then
            Exit Do ' => Yes response = Yes we exit the loop
        End If
    Loop While 1 = 1 ' => Infinite loop
    MsgBox ";-)"
End Sub

Line breaks in a MsgBox

For a carriage return, you can insert the "line break" character using the Chr function and the number 10, which represents the line break, for example:

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

See result below:

line breaks dialog boxes

InputBox

InputBox asks the user to enter a value in a dialog box, for example:

Sub example()
    Dim result As String
    
    result = InputBox("Text ?", "Title") 'The variable is assigned the value entered in the InputBox
    
    If result <> "" Then 'If the value anything but "" the result is displayed
        MsgBox result
    End If
End Sub

See result below:

input dialog boxes

The third argument can be used to set a default value:

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

See result below:

input2 dialog boxes