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.

---

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

• 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:

• Return Parameter:

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

• Button Constants:

• Icon Constants:

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

---

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

---

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

---

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

---

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

---

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

---

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

---

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

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.

---

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

---

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

---

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

---

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

---

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 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:

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.

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

---

💡 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.

---

You May Also Like to Explore

How to Use InStr Function in VBA (3 Examples)

How to Use LCase Function in VBA in Excel (With 4 Examples)

How to use VBA SPLIT function in Excel (5 Examples)

The post How to Use MsgBox Function in Excel VBA (A Complete Guideline) appeared first on ExcelDemy.