Dialogue Boxes

MsgBox

Up to now, we have only used MsgBox dialog boxes to display information :
Sub msg()
    dim txt as String
      txt = “This is test message!!!”
    MsgBox txt
End Sub
In this case, MsgBox is called with only one argument.


See result 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_A1()
    
If MsgBox("Are you sure that you wish to delete the contents of A1 ?", vbYesNo, "Confirm") = vbYes Then
        Range(
"A1").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
Description: ok - dialog boxes
vbOKCancel
1
Description: ok - dialog boxesDescription: cancel - dialog boxes
vbAbortRetryIgnore
2
Description: abort - dialog boxesDescription: retry - dialog boxesDescription: ignore - dialog boxes
vbYesNoCancel
3
Description: yes - dialog boxesDescription: no - dialog boxesDescription: cancel - dialog boxes
vbYesNo
4
Description: yes - dialog boxesDescription: no - dialog boxes
vbRetryCancel
5
Description: retry - dialog boxesDescription: cancel - dialog boxes

vbCritical
16
Description: critical - dialog boxes
vbQuestion
32
Description: question - dialog boxes
vbExclamation
48
Description: exclamation - dialog boxes
vbInformation
64
Description: information - dialog boxes

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





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
Description: ok - dialog boxes
vbCancel
2
Description: cancel - dialog boxes
vbAbort
3
Description: abort - dialog boxes
vbRetry
4
Description: retry - dialog boxes
vbIgnore
5
Description: ignore - dialog boxes
vbYes
6
Description: yes - dialog boxes
vbNo
7
Description: no - 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"



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






The third argument can be used to set a default value :
InputBox("Text ?", "Title", "Default value")