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:

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])

• Text: the dialog box text
• Buttons: choice of buttons (yes, no, cancel, etc.) + other options
• Title: dialog box 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:

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

Constant	Numerical Value	Description
vbOKOnly	0
vbOKCancel	1
vbAbortRetryIgnore	2
vbYesNoCancel	3
vbYesNo	4
vbRetryCancel	5
vbCritical	16
vbQuestion	32
vbExclamation	48
vbInformation	64
vbDefaultButton1	0	Default button: Button 1
vbDefaultButton2	256	Default button: Button 2
vbDefaultButton3	512	Default button: Button 3
vbApplicationModal	0	Forces the user to provide an answer before continuing to use Excel
vbSystemModal	4096	Forces 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:

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

Constant	Numerical Value	Button that corresponds to the numerical value
vbOK	1
vbCancel	2
vbAbort	3
vbRetry	4
vbIgnore	5
vbYes	6
vbNo	7

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:

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:

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

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

See result below: