VBA MsgBox

VBA MsgBox is the Pop up Box function to display a message in VBA Language. VBA MsgBox  Function helps to display any value to the user with the given prompt. Message Box in VBA is available in all MS Office Application to show the value of a variable or a given string and wait for user to click the buttons. Use the MsgBox to display a Message Box in Excel, Access, Word, PowerPoint (PPT) and Outlook.

Syntax of VBA MsgBox Function

Following is the syntax of the VBA MsgBox Function. Message Box function has one required parameter and four optional parameters.

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

Parameters

MsgBox Function takes the following named arguments. We can pass the values to the MsgBox Function based on our requirement. MsgBox Function will process the given arguments values and shows the pop up box. We can pass the prompt and title as string values, and we can provide the button values from the predefined constants.

• prompt: Message which you wants to show in the message box body.  Prompt is the required parameter,we must pass an argument value to the message box prompt parameter.
• buttons: We can change the default buttons and style of the message box using the button parameters. VBA MsgBox Function has the predefined list of the constants to set the button styles. This is an optional parameter, the default value of the buttons is vbOKOnly. This will show the message box with simple OK button.
• title: We can provide the title of the message box window using Title parameter. This is an optional parameter, the default title of the message box is the name of the Application.
• helpfile: helpfile parameter is useful to set the context-sensitive identifier. This is an optional parameter, context parameter is required when you provide helpfile parameter.
• context: to prove the context number set to the given help topic. This is an optional parameter, helpfile parameter is required when you provide context parameter.

Button Options

We can pass the button following constant to VBA MsgBox function to can change the default buttons. Here are list of predefined button constants and its values. We can provide the VBA MsgBox constants or respective ENUM value.

Syntax:

MsgBox prompt:=”Your Prompt”, Buttons:=vbOKCancel

Icon Styles

Following are predefined constants to add an icon to the message box. We can provide the icons constant along with the button constant (0-5) which are listed above.

MsgBox “Your Prompt”, vbInformation + vbOKCancel

Default Buttons

We can also set the default button of the message box. We can mention focused button to be clicked when uses press the enter button.

Syntax:

MsgBox “Your Prompt”, vbInformation + vbOKCancel + vbDefaultButton2

Modality, Foreground and Right Alignment

Here are the constants to set the advanced options of the message box. These options helps to determine the modality of the message box and change the alignment of the body text.

Syntax:

MsgBox “Your Prompt”, vbInformation + vbOKCancel + vbMsgBoxHelpButton + vbMsgBoxRight

Return Values

We can capture the return value of the message box and check using the following constants.

Syntax:

If MsgBox(“Do you wants to continue”, vbInformation + vbYesNo) = vbYes Then
MsgBox “You pressed Yes button”
End If

Examples:

Here are the list of most frequently used examples on VBA MsgBox Function. You can have a look to understand the concepts with suitable examples.

Example to set VBA Buttons:

The following example shows a message box with OK and Cancel buttons.

Sub VBA_MessageBox_Buttons_Example_1()
MsgBox "Your Prompt", vbOKCancel 'OR, you can provided the values with named argument MsgBox prompt:="Your Prompt", Buttons:=vbOKCancel 'OR, You can just use it's ENUM values MsgBox "Your Prompt", 1
End Sub

Example to Change VBA Icon:

Here is a simple example to show a message box with vbInformation Icon and OK and Cancel buttons.

Sub VBA_MessageBox_Buttons_Example_2()
MsgBox "Your Prompt", vbInformation + vbOKCancel 'OR, you can provided the values with named argument MsgBox prompt:="Your Prompt", Buttons:=vbInformation + vbOKCancel 'OR, You can just use it's ENUM values MsgBox "Your Prompt", 64 + 1
End Sub

Example to Set VBA Default Button:

The below example sets the second button as default button. We can set any of the four button as default button using the given constants or Enumeration values.

Sub VBA_MessageBox_Buttons_Example_3()
MsgBox "Your Prompt", vbInformation + vbOKCancel + vbDefaultButton2 'OR, you can provided the values with named argument MsgBox prompt:="Your Prompt", Buttons:=vbInformation + vbOKCancel + vbDefaultButton2 'OR, You can just use it's ENUM values MsgBox "Your Prompt", 64 + 1 + 256
End Sub

Example to Set VBA Default Button:

The following example will show you the help button in message box. and the text is right-aligned

Sub VBA_MessageBox_Buttons_Example_4()
MsgBox "Your Prompt", vbInformation + vbOKCancel + vbMsgBoxHelpButton + vbMsgBoxRight 'OR, you can provided the values with named argument MsgBox prompt:="Your Prompt", Buttons:=vbInformation + vbOKCancel + vbMsgBoxHelpButton + vbMsgBoxRight 'OR, You can just use it's ENUM values MsgBox "Your Prompt", 64 + 1 + 16384 + 524288
End Sub

Example to Return User Selection:

This example will ask the user to press one of the buttons and show the second message based on the return value of the first message box.

Sub VBA_MessageBox_Buttons_Example_5()
tempVar = MsgBox("Do you wants to continue", vbInformation + vbYesNo) 'The following message box will appear based on user selection If tempVar = vbYes Then MsgBox "You pressed Yes button" ElseIf tempVar = vbNo Then MsgBox "You pressed No button" End If
End Sub

Reference:

VBA MsgBox Function