VBA-MsgBox

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.

Constant Value Description
vbOKOnly 0 This is the default value of the button parameter. You can provide vbOKOnly constant to display OK button only.
vbOKCancel 1 You can provide vbOKCancel constant to display OK and Cancel buttons.
vbAbortRetryIgnore 2 You can provide vbAbortRetryIgnore constant to display Abort, Retry, and Ignore buttons.
vbYesNoCancel 3 You can provide vbYesNoCancel constant to display Yes, No, and Cancel buttons.
vbYesNo 4 You can provide vbYesNo constant to display Yes and No buttons.
vbRetryCancel 5 You can provide vbRetryCancel constant to display Retry and Cancel buttons.

VBA-MsgBox-Button-Options

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.

Constant Value Description
vbCritical 16 vbCritical constants uses to display Critical Message icon.
vbQuestion 32 vbQuestion constants uses to display Warning Message icon.
vbExclamation 48 vbExclamation constants uses to display Warning Message icon.
vbInformation 64 vbInformation constants uses to display Information Message icon.

VBA-MsgBox-Icon-Styles

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.

Constant Value Description
vbDefaultButton1 0 vbDefaultButton1 will set the First button is default.
vbDefaultButton2 256 vbDefaultButton2 will set the Second button is default.
vbDefaultButton3 512 vbDefaultButton3 will set the Third button is default.
vbDefaultButton4 768 vbDefaultButton4 will set the Fourth button is default.

VBA-MsgBox-Default-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.

Constant Value Description
vbApplicationModal 0 When you set vbApplicationModal option, you must respond to the message box before continuing work in the current application.
vbSystemModal 4096 When you set vbSystemModal option, all of your applications are suspended until you responds to the message box.
vbMsgBoxHelpButton 16384 vbMsgBoxHelpButton uses to add Help button to the message box.
vbMsgBoxSetForeground 65536 We can set the message box window as the foreground window using vbMsgBoxSetForeground.
vbMsgBoxRight 524288 Using vbMsgBoxRight, You can make the text of message box right-aligned.
vbMsgBoxRtlReading 1048576 vbMsgBoxRtlReading helps to set text appearance as right-to-left reading on Hebrew and Arabic system users.

VBA-MsgBox-Modal-Help-and-Right-Alignment
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.

Constant Value Description
vbOK 1 Returns vbOK when users press the OK Button
vbCancel 2 Returns vbCancel when users press the Cancel Button
vbAbort 3 Returns vbAbort when users press the Abort Button
vbRetry 4 Returns  vbRetry when users press the Retry Button
vbIgnore 5 Returns vbIgnore when users press the Ignore Button
vbYes 6 Returns vbYes when users press the Yes Button
vbNo 7 Returns vbNo when users press the No Button

VBA-MsgBox-Return-Values

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

Leave a Reply