Excel VBA MessageBox

Message Box

The MsgBox in Excel VBA is a dialog box used to inform the users of your program by showing a custom message or get some necessary inputs such as Yes/No or OK/Cancel. MsgBox is a VBA function and has a similar syntax as other VBA functions.

When the MsgBox dialog box is displayed, the VBA code is halted for that point of time. One needs to select any of the buttons in the MsgBox or need to click on the close icon to run the remaining VBA code.

Parts of VBA

Title: This is used to display what the message box is about. If you don’t specify anything in the title section, it displays the default Excel application name.

Prompt: It displays the message that the programmer wants to enter. You can use this space to write a couple of lines or even present tables/data here.

Button(s): One can customize the buttons to show buttons such as Yes/No, Yes/No/Cancel, Retry/Ignore, etc. The OK is the default Msgbox button.

Close Icon: This icon in Msgbox is used to close the message box. This is the same, unlike Microsoft's word, excel, etc.. Hence the user can close by clicking on the close icon.

Syntax of VBA

MsgBox( prompt [, buttons ] [, title ] [, helpfile, context ] )

Parameter

Prompt (required) – This parameter displays the message that you see in the MsgBox. One can use up to 1024 characters in the prompt parameter and can also use it to display the various values of variables.

Buttons (optional) – It determines what buttons and icons are displayed in the MsgBox. The buttons are logically divided into four groups. The first group is between 0 to 5, specifies the buttons to be displayed in the message box. The second group comprising of values 16, 32, 48, 64 defines the style of the icon to be displayed, the third group comprising of 0, 256, 512, 768 specifies the default button, and the fourth group (0, 4096) determines the modality of the message box. The available buttons are as follows: 

• 0 vbOKOnly - Displays OK button only.
• 1 vbOKCancel – This button format displays OK and Cancel buttons.
• 2 vbAbortRetryIgnore – It is used to display Abort, Retry, and Ignore buttons.
• 3 vbYesNoCancel - Displays Yes, No, and Cancel buttons.
• 4 vbYesNo – It displays two buttons i.e., Yes and No.
• 5 vbRetryCancel – It is used to displays Retry and Cancel buttons.
• 16 vbCritical – It displays the Critical Message icon.
• 32 vbQuestion – It is used to display the Warning Query icon.
• 48 vbExclamation – This button displays the Warning Message icon.
• 64 vbInformation – This button displays the information Message icon.
• 0 vbDefaultButton1 – This format is used to make the first button is the default.
• 256 vbDefaultButton2 - This format is used to make the second button is the default.
• 512 vbDefaultButton3 - This format is used to make the third button is the default.
• 768 vbDefaultButton4 - This format is used to make the fourth button is the default.

Title (optional) –This parameter is used to specify the caption you want to use in the message dialog box. The title is displayed at the top (title bar) of the MsgBox. If you don’t specify anything, by default it will show the name of the application.

Helpfile (optional)– This parameter is used to specify a help file that can be accessed whenever a user clicks on the Help button. The help button would appear only when the developer will use the button code for it. If the developer is using a help file, he also needs to specify the context argument.

Context (optional)– This parameter represents a numeric expression that is the Help context number assigned to the appropriate Help topic. These are rarely used in VBA.

Return

The MsgBox function returns integer values, which are used to identify the button the user has clicked in the message box. MsgBox function can return one of the following values:

• 1 – This integer value is returned when the “vbOK” button (OK was clicked) is passed.
• 2 - This integer value is returned when the “vbCancel” button (Cancel was clicked) is passed.
• 3 - This integer value is returned when the “vbAbort” button (Abort was clicked) is passed.
• 4 - This integer value is returned when the “vbRetry” button (Retry was clicked) is passed.
• 5 - This integer value is returned when the “vbIgnore” button (Ignore was clicked) is passed.
• 6 - This integer value is returned when the “vbYes” button (Yes was clicked) is passed
• 7 - This integer value is returned when the “vbNo” button (No was clicked) is passed.

Example 1

Sub MsgBox_Exercise()
 MB = MsgBox("Do you like Excel?", vbYesNo)
     If MB = vbYes Then
         MsgBox "Wow! I also like working on Excel!"
     Else
         MsgBox "Ohh! Try once and you will surely like it."
     End If
 End Sub 

Output

If the user clicks on yes

If the user clicks on No

Example 2

Sub MsgBox_Excercise2()
 'button to implement cancel
 MsgBox "Welcome to VBA!", vbRetryCancel
 'button to implement retry and help options
 MsgBox "Welcome to VBA!", vbRetryCancel + vbMsgBoxHelpButton
 'button to implement yes and no options
 MsgBox "What do you want to do next?", vbYesNoCancel + vbDefaultButton2
 End Sub 

Output

Click any of the two options.

Click any of the three options.

Click any of the three options.