TOP

VBA-Lesson 10. Dialog boxes (Dialog boxes)

MsgBox

Until now, we used the MsgBox dialog box to display information on the screen.

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

In this case, MsgBox is called with only one argument. See code below:

Now we will create a dialog box that will ask for confirmation to delete before the two instructions are executed. Then there are three arguments we will use:

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

Text: dialog box text Buttons: selection of buttons (yes, no, cancel, etc.) + other options Title: the title of the dialog box

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

Result:

Private Sub warning(var_text As String)
     MsgBox "Caution : " & var_text & " !"
End Sub

Sub macro_test()
     If Range("A1") = "" Then
         warning "empty cell"
     ElseIf Not IsNumeric(Range("A1")) Then
         warning "non-numeric value"
     End If
End Sub

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

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

Options for using the second argument in 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

Here is an example of a MsgBox that will continue to be displayed each time in the loop until the user presses Yes:

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

Options for using the second argument in MsgBox

To display a new ribbon, you can use the Chr function with an argument of 10, which is responsible for moving the ribbon, for example:

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

We get the following result:

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 is any, but not "", then the result will be displayed
        MsgBox result
     End If
End Sub

We get the following result:

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

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

The result is as follows:

Articles on the topic:

VBA-Lesson 9. Procedures and functions

VBA-Lesson 11.1. Workbook events