How to Use MsgBox Function in Excel VBA (A Complete Guideline)

In Excel VBA, the MsgBox function is generally used to create and display a message box containing an iconic statement as well as a few command buttons. In this article, you’ll get to learn how you can use this MsgBox function efficiently in Excel with appropriate illustrations.

msgbox-function-overview-in-excel-vba


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Introduction to the MsgBox Function in Excel VBA

msgbox-function-syntax

  • Function Objective:

The MsgBox function is used to create a message box which is also known as a dialogue box.

  • Syntax:

MsgBox(Prompt, [Button As VbMsgBoxStyle = vbOkOnly], [Title], [HelpFile], [Context]) As VbMsgBoxResult

  • Arguments Explanation:
Argument Required/Optional Explanation
Prompt Required A statement that will be shown in the message box.
[Buttons] Optional Button and icon codes to display buttons and icons in the message box.
[Title] Optional Title or name of the message box.
[HelpFile] Optional Index or link which is assigned to the Help button in the message box.
[Context] Optional Index or specific topic number of the Help file.
  • Return Parameter:

The function returns a statement along with the defined buttons in the message box.

  • Button Constants:
Button Codes Values Description
vbOKOnly 0 Shows the Ok button only (Default).
vbOKCancel 1 Shows OK and Cancel buttons.
vbAbortRetryIgnore 2 Shows Abort, Retry and Ignore buttons.
vbYesNo 3 Shows Yes and No buttons.
vbYesNoCancel 4 Shows Yes, No and Cancel buttons.
vbRetryCancel 5 Shows Retry and Cancel buttons.
vbMsgBoxHelpButton 16384 Shows Help Button.
vbDefaultButton1 0 Defines the first button default.
vbDefaultButton2 256 Defines the second button default.
vbDefaultButton3 512 Defines the third button default.
vbDefaultButton4 768 Defines the fourth button default.
vbMsgBoxRight 524288 The alignment of the text is right.
vbMsgBoxRtlReading 1048576 Text reading from right to left like Arabic and Hebrew languages.
  • Icon Constants:
Icon Codes Values Description
vbCritical 16 Displays the critical message icon- Thin white cross inside a red-filled circle.
vbQuestion 32 Displays the question message icon- White question mark inside a blue-filled circle.
vbExclamation 48 Displays the warning message icon- Black exclamatory symbol inside a yellow-filled triangle.
vbInformation 64 Displays information message icon- The letter ‘i’ in white inside a blue-filled circle.

Examples of Using MsgBox Buttons in Excel VBA

1. Use of vbOKOnly (Default) Button for MsgBox Function

In our first example, we’ll use the Prompt argument only. As we’re not assigning any button manually in the second argument, the function will return an output by using the default MsgBox button and that is vbOKOnly. It means, in the message box, You’ll see the defined statement and OK button only.

To display the following message box with the default button settings, you have to run the following code in a new module in the VBA window.

Sub MsgBox_vbOKOnly()

MsgBox "This is an example of default button setting"

End Sub

vbokonly button for msgbox function in excel vba


2. Use of vbOKCancel Button for MsgBox Function in Excel VBA

If you want to add a Cancel button in the message box, then copy the codes below and paste them into your VBA module. We’re using the button code- vbOKCancel in the second argument after specifying the first argument with a statement.

Sub MsgBox_vbOKCancel()

MsgBox "Do you want to continue?", vbOKCancel

End Sub

vbokcancel button for msgbox function in excel vba


3. Use of vbAbortRetryIgnore Button for MsgBox Function

To show the Abort, Retry and Ignore buttons in the message box, then you have to go for the following codes:

Sub MsgBox_vbAbortRetryIgnore()

MsgBox "What do you want to do?", vbAbortRetryIgnore

End Sub

vb abort retry ignore button for msgbox function in excel vba


4. Use of vbYesNo Button for MsgBox Function in Excel

We can also use vbYesNo code for button argument to display Yes and No buttons only. The required codes are:

Sub MsgBox_vbYesNo()

MsgBox "Do you want to continue?", vbYesNo

End Sub

vb yes no button for msgbox function in excel vba


5. Use of vbYesNoCancel Button for MsgBox Function in VBA

If you want to add one more button-’Cancel’ to the Yes and No buttons, then you have to input the vbYesNoCancel code in the second argument.

Sub MsgBox_vbYesNoCancel()

MsgBox "Do you want to retry?", vbYesNoCancel

End Sub

vb yes no cancel button for msgbox function in excel vba


6. Use of vbRetryCancel Button for MsgBox Function in Excel

We can assign vbRetryCancel code in the button argument to display Retry and Cancel buttons.

Sub MsgBox_vbRetryCancel()

MsgBox "What do you want to do?", vbRetryCancel

End Sub

vb retry cancel button for msgbox function in excel vba


7. Use of Help Button for MsgBox Function in Excel VBA

To add a Help button beside other buttons, you have to use the Plus(+) sign to add vbMsgBoxHelpButton with another assigned code in the second argument of the MsgBox function. The following codes will display the Yes, No, and Help buttons together in the message box.

Sub MsgBox_Help_Button()

MsgBox "Do you want to continue?", vbYesNo + vbMsgBoxHelpButton

End Sub

vb help button for msgbox function in excel vba


8. Assigning a Default Button for MsgBox Function in Excel

Now if we assign a default insert button, the message box will return the output with the default button option after we press Enter only. For example, in the message box containing Yes and No buttons, if we assign No as the default button and then we run the code, we can press only Enter to select the No button. The following codes will show the message box with Yes and No buttons where the No button will be marked as the default button-press.

Sub MsgBox_DeafultButton()

MsgBox "Do you want to exit?", vbYesNo + vbDefaultButton2

End Sub

assigning default button for msgbox function in excel vba

If you want to select Yes as the default button-press, then you have to assign the vbDefaultButton1 code along with the vbYesNo code in the second argument. If you have three buttons in the message box, then you can use vbDefaultButton3 to assign the third option in the message box as the default button-press.


Similar Readings


Examples of Using MsgBox Icons in Excel VBA

In the MsgBox function, icon codes are used to display the type of statement written in the message box. There are four types are icon codes for the MsgBox function in Excel VBA- vbCritical, vbQuestion, vbExcalamation, and vbInformation.

1. Use of vbCritical Icon for MsgBox Function

vbCritical icon code is generally used to show an error message. The following codes will display an error message as in the screenshot.

Sub MsgBox_vbCritical()

MsgBox "An error has occurred", vbCritical

End Sub

vbcritical icon for msgbox function in excel vba


2. Use of vbQuestion Icon for MsgBox Function in Excel

To add a question icon before the statement, you can use the following codes in your VBA module:

Sub MsgBox_vbQuestion()

MsgBox "Do you want to continue?", vbYesNo + vbQuestion

End Sub

vbquestion icon for msgbox function in excel vba


3. Use of vbExclamation Icon for MsgBox Function in Excel

vbExclamation icon code is used to show a minor input error in the message box. The exclamatory sign(!) lies inside a yellow-filled triangle. The codes below will define the message box with this specific icon.

Sub MsgBox_vbExclamation()

MsgBox "An error occurred", vbExclamation

End Sub

vbexclamation icon for msgbox function in excel vba


4. Use of vbInformation Icon for MsgBox Function in Excel

The vbInformation icon code will return a symbol or an icon with the letter ‘i’ inside a blue-filled circle. The codes to show this icon before a statement should be:

Sub MsgBox_vbInformation()

MsgBox "This is an information box.", vbInformation

End Sub

vbinformation icon for msgbox function in excel vba


Example of Using Title Argument in MsgBox Function

The default title of the message box is Microsoft Excel. But you can also define a title yourself by inputting a title name within the Double-Quotes (“ “) in the third argument of the MsgBox function.

For example, we want to make the title name- ”Choose an Option” for a message box containing Yes and No buttons only. So, the required formula should be as follows:

Sub MsgBox_Title()

MsgBox "Do you want to retry?", vbYesNo + vbInformation, "Choose an Option"

End Sub

assigning title for msgbox function in excel vba


Assigning the Output Messages with MsgBox Function in Excel

In this section, we’ll learn how to return an output message after pressing a button assigned in the message box. The output message will appear based on the return codes or button constants. There are a total of 7 button constants which are shown in the table below.

Return Index:

Button Button Code Constant Value
OK vbOK 1
Cancel vbCancel 2
Abort vbAbort 3
Retry vbRetry 4
Ignore vbIgnore 5
Yes vbYes 6
No vbNo 7

For example, a message box containing the statement- ”Are you sure to exit?” has Yes and No buttons. If we press Yes, an output message box will appear. Similarly, another output message will open up for pressing No. To execute the mentioned criteria, we have to run the following codes in the VBA module:

Sub MsgBox_Output()

Dim Result

Result = MsgBox("Are you sure to exit?", vbYesNo + vbQuestion)

If Result = vbYes Then

MsgBox "Thank you for using the application."

Else: MsgBox "You chose No"

End If

End Sub

You can input 6 instead of vbYes in the third line under the subroutine function.

assigning output message or return values with msgbox function in excel vba

And when you’ll press Yes, another message box will be displayed as in the picture below.

assigning output message or return values with msgbox function in excel vba


💡 Things to Keep in Mind

🔺 In the second argument of the MsgBox function, you can use the values directly instead of the button and icon constants or codes.

🔺 The default button is assigned to vbOKOnly if you don’t input a button argument manually.

🔺 The default title of the message box is “Microsoft Excel” if you don’t assign a title.


Concluding Words

I hope all of the examples mentioned above on the uses of the MsgBox function in VBA editor will now inspire you to utilize them properly in your Excel spreadsheets. If you have any questions or feedback, please let us know through comments. Or you can check out our other articles related to Excel functions on this website.


Related Articles

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo