MsgBox is a remarkably effective method or tool to make Excel operations more accessible and user-friendly. MsgBox can show distinct types of outputs and show you what is going on with your code based on the MsgBox you are using. Moreover, you can also use MsgBox to input data and choose an option. To get the best out of the article, you need to understand your requirements and then choose the desired type of Message Box accordingly. Below we presented Excel VBA MsgBox function using which not only you can show messages, but you can also take directions.
For example, the below message box asks whether the user wants to continue, If the user presses Yes, then it will execute a certain code. Or otherwise, it will execute another code.
If the user press Yes, the code proceeds to show the below image, Otherwise, it will be No.
So, we can see that using this type of message box is quite useful and can be used to execute certain tasks.
Download Practice Workbook
Download the following workbook to practice by yourself.
Introduction to MsgBox Function in Excel
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. |
How to Launch VBA Editor in Excel
You can easily launch the VBA editor by pressing Alt+F11 or using the Visual Basic command from the Developer tab. As shown in the video below,
- After entering the Developer tab, tango to Insert > Module, and you will see an editor where you can put the necessary code.
Note:
In any case, if you cannot find the Developer tab in the ribbon, then you need to enable it from the options. You can easily enable the developer tab by following this article
Various Types of VBA MsgBox Function in Excel: Their Usage and Return Values
All the above-mentioned MsgBox types and their uses are defined with examples below.
1. Use of vbOKOnly Type MsgBox to Have Option in Only One Way in Excel
In our first example, we will use the Prompt argument only. As we are 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 VBA msgbox function in Excel, You will see the defined statement and OK button only.
To display the following message box with the default button settings, you must 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
Read More: How to Return a Value in VBA Function (Both Array and Non-Array Values)
2. Implementation of vbOKCancel Type of MsgBox to Have the Option to Cancel Procedure in Excel
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 are 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. Using vbAbortRetryCancel MsgBox Function to Have Three Way Option Altogether in Excel
To show the Abort, Retry and Ignore buttons in the message box, then you must go for the following codes:
Sub MsgBox_vbAbortRetryIgnore()
MsgBox "What do you want to do?", vbAbortRetryIgnore
End Sub
Read More: How to Use VBA Function Procedure with Arguments in Excel
4. Using of vbYesNo Type of Message Box to Give User Two Separate Options in Excel
We can also use vbYesNo code for button arguments to display Yes and No buttons only. The required codes are:
Sub MsgBox_vbYesNo()
MsgBox "Do you want to continue?", vbYesNo
End Sub
5. Utilize vbYesNoCancel MsgBox Function to Give User Two Option and Quit Altogether in Excel
If you want to add one more button-’Cancel’ to the Yes and No buttons, then you must input the vbYesNoCancel code in the second argument.
Sub MsgBox_vbYesNoCancel()
MsgBox "Do you want to retry?", vbYesNoCancel
End Sub
Similar Readings
- How to Use VBA DateDiff Function in Excel (9 Examples)
- Random Number Excel Formula (5 Examples)
- How to Use VBA Str Function in Excel (4 Examples)
- Use VBA ChDir Function in Excel (4 Suitable Examples)
- How to Use VBA IsError Function (6 Examples)
6. Use of vbCritical Type Message Box for Showing Any Error in the Procedure in Excel
vbCritical icon code is 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
7. Using vbQuestion Type Message Box Function to Indicate Question for the User in Excel
To add a question icon before the statement, you can use the following codes in your VBA module:
Sub MsgBox_vbCritical()
MsgBox "An error has occurred", vbCritical
End Sub
Read More: Create Custom VBA functions and Use them in the Worksheet
8. Use of vbExclamation Type Message Box Show 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
9 Using vbDefaultButton MsgBox Function to Set the Default Button in Excel
Using the default button, we can specify which button will be pressed by default when we open
9.1 vbDefaultButton1
To set the first button as default, we can use the below code in the code editor.
Sub DisplayMessageWithDefault1Button ()
MsgBox "Please select a color:", vbQuestion + vbYesNoCancel + vbDefaultButton3, "Select Color"
End Sub
- The option selection is set to the First option. If the user presses Enter, then the Yes will be pressed immediately.
9.2 vbDefaultButton2
To set the second button as default, we can use the below code in the code editor.
Sub DisplayMessageWithDefault2Button()
MsgBox "Please select a color:", vbQuestion + vbYesNoCancel + vbDefaultButton3, "Select Color"
End Sub
- The option selection is set to the second option. If the user presses Enter, then the NO will be pressed immediately.
9.3 vbDefaultButton3
To set the third button as default, we can use the below code in the code editor.
Sub DisplayMessageWithDefault3Button()
MsgBox "Please select a color:", vbQuestion + vbYesNoCancel + vbDefaultButton3, "Select Color"
End Sub
- The option selection is set to the third option. If the user presses Enter, then the Cancel will be pressed immediately.
10. Utilizing vbApplicationModal MsgBox Function to Allow User to Active in Other Tabs in Excel
When vbApplicationModal is set to False, it means that the user form being displayed will allow the user to interact with other parts of the Excel application while the user form is open. This means that the user can switch to another workbook or click on other parts of the Excel interface while the user form is open.
For this, we present the below code and enter the following code into the code window.
Sub DisplayApplicationModalMessage()
MsgBox "This is an application modal message box.", vbOKOnly + vbInformation + vbApplicationModal, "Application Modal Message Box"
MsgBox "This message box is displayed after the first message box is closed.", vbOKOnly + vbInformation, "Non-Modal Message Box"
End Sub
You will see that the user will be able to interact with other parts of the Excel sheet while the worksheet is open.
- After you close the previous message box, the next box will open.
Similar Readings
- How to Use VBA While Wend Statement in Excel (4 Examples)
- Use VBA UCASE function in Excel (4 Examples)
- How to Use InStr Function in VBA (3 Easy Examples)
- Use VBA LCase Function in Excel (3 Easy Examples)
11. Use of vbSystemModal to Restrict User in the Current Tab Only in Excel
On the other hand, means that the user form being displayed will remain in focus and the user will not be able to interact with any other part of Windows’s operating system until the user form is closed. This means that the user cannot switch to any other application, window or dialog box while the message box is open.
Sub DisplaySystemModalMessage()
MsgBox "This is a system modal message box.", vbOKOnly + vbInformation + vbSystemModal, "System Modal Message Box"
MsgBox "This message box is displayed after the first message box is closed.", vbOKOnly + vbInformation, "Non-Modal Message Box"
End Sub
- We will notice that the user is not allowed to interact with other parts of the application without closing the message box by pressing it.
12. Employing vbMsgBoxHelpButton MsgBox Function to Show Help Info to the User in Excel
To add a Help button beside other buttons, you must 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
13. Utilizing VbMsgBoxSetForeground MsgBox Function to Show the Message Box in Foreground In Excel
VbMsgBoxSetForeground is a constant that can be used in VBA (Visual Basic for Applications) to specify that a message box should be displayed in the foreground. By default, message boxes are displayed in the foreground, but in certain cases, they may appear behind other windows, which can be confusing for the user.
Sub DisplayForegroundMessage()
MsgBox "This is a foreground message box.", vbOKOnly + vbInformation + vbMsgBoxSetForeground, "Foreground Message Box"
End Sub
14. Use of vbMsgBoxRight In Excel to Align Message Boxes to Right
This code will align the message box text content to the right. The code is given below.
Sub DisplayRightAlignedMessage()
MsgBox "This is a right-aligned message box.", vbOKOnly + vbInformation + vbMsgBoxRight, "Right-Aligned Message Box"
End Sub
15. Using vbInformation Icon for MsgBox Function in Excel to Denote Information
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 in Excel
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
5 Separate Examples of Using MsgBox in Excel
Below we presented Two separate examples of Using a VBA MsgBox function in Excel. To avoid any compatibility issues, try to use the Excel 365 edition.
1. Use of MsgBox Function to Output Information in Excel
Below we are going to demonstrate how we can use a VBA msgbox function to show the output in another message box in Excel.
Steps
- We can see the dataset below with the product name and price and their stocks.
- Then we open the VBA editor from the developer tab and enter the following code in the code window,
Sub SearchInfo()
Dim name As String
Dim age As Integer
Dim gender As String
Dim email As String
Dim found As Boolean
name = InputBox("Enter the name:")
For i = 5 To Range("B" & Rows.Count).End(xlUp).Row
If Range("B" & i).Value = name Then
Price = Range("C" & i).Value
stock = Range("D" & i).Value
found = True
Exit For
End If
Next i
If found = True Then
MsgBox "Name: " & name & vbCrLf & _
"Price: " & Price & vbCrLf & _
"Stock: " & stock
Else
MsgBox "Product not found."
End If
End Sub
- Then we can see there is an input box asking for the search input, we entered “Motherboard”.
- Click OK after this.
- Right after clicking OK, we are going to see that the message box showing the Input Name, Price of that product, and the Stock value
2. Implementing vbYesNo MsgBox Function to Delete Specific Row in Excel
We can use this type of message box to delete specific rows in the worksheet using an Excel VBA msgbox function. Users just must Enter the row serial that they want to delete, and it will delete accordingly.
Steps
- We can see the dataset below with the product name and price and their stocks.
- Then we open the VBA editor from the developer tab and enter the following code in the,
Sub DeleteRow()
Dim rowToDelete As Integer
Dim answer As Integer
rowToDelete = InputBox("Enter the row number to delete:")
answer = MsgBox("Are you sure you want to delete row " & rowToDelete & "?", vbYesNo)
If answer = vbYes Then
Rows(rowToDelete).Delete
MsgBox "Row " & rowToDelete & " has been deleted."
Else
MsgBox "Row " & rowToDelete & " was not deleted."
End If
End Sub
- Then click on the Run icon as shown in the image.
- After running the code, you will see an input box asking for the row serial no of which you are going to delete, we entered seven.
- Press OK after this.
- Then after this, another message box asks whether you really want to delete the mentioned row, confirm it by pressing Yes And you can neglect it by pressing No.
- After pressing Yes, you will notice that row seven has been deleted.
- Press OK after this.
3. Create MsgBox with Variables
We can use the variable VBA MsgBox function to assign the value of the options button to a variable in Excel. Then we can use the variable to perform different operations.
Steps
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub MsgBox_Variable()
'variable declaration
Dim button_option As Integer
'(1) create a MsgBox with buttons and Question icon
'(2) assign the options of the MsgBox to the variable
button_option = MsgBox _
("Do you want to exit Excel", vbYesNo + vbQuestion)
'show a MsgBox depending on the value of the variable
If button_option = vbYes Then
MsgBox "Thank you for using this application"
Else
MsgBox "You chose not to exit"
End If
End Sub
This VBA code creates a MsgBox with a question icon and Yes/No buttons and assigns the user’s selection to the variable button_option. The code then displays a different MsgBox depending on whether the user chose Yes or No. If the user selected Yes, a MsgBox thanking them for using the application is displayed, otherwise, a MsgBox saying they chose not to exit is displayed.
4. Deploy MsgBox for Error Handling Purposes
We created a MsgBox for error handling to inform the user about the occurrence of an error and provide information on how to fix it. This VBA MsgBox function can also help the user to understand the nature and severity of the error and assist in troubleshooting in Excel.
Steps
You can enter the following code in the editor window.
Sub Error_Handling()
On Error GoTo Error_Text
Dim int_1, int_2 As Range
Dim Addition As Integer
Set int_1 = Application.InputBox _
("Please insert the first number", Type:=8)
Set int_2 = Application.InputBox _
("Please insert the second number", Type:=8)
Addition = int_1 + int_2
MsgBox "The sum of the numbers is " & Addition
Exit Sub
Error_Text:
MsgBox "You did not insert valid numbers", vbCritical
End Sub
- After you enter the code and run it, you are going to see there is an input box asking for the first number. Press OK after this.
VBA Code Breakdown
Sub Error_Handling(): This line declares a VBA subroutine named “Error_Handling”.
On Error GoTo Error_Text:
- This line sets up error handling for the subsequent lines of code. If an error occurs, the code will jump to the “Error_Text” section of the subroutine.
Dim int_1, int_2 As Range:
- This line declares two variables named “int_1” and “int_2” as Range objects. However, note that “int_1” is declared as a Variant data type and not as Range. To explicitly declare it as a Range, the line should be written as follows:
Dim Addition As Integer:
- This line declares a variable named “Addition” as an integer data type.
Set int_1 = Application.InputBox(“Please insert the first number”, Type:=8):
- This line prompts the user to input the first number by displaying an InputBox. The value that the user inputs is assigned to the “int_1” variable. The Type argument specifies the input type as a number.
Set int_2 = Application.InputBox(“Please insert the second number”, Type:=8):
- This line prompts the user to input the second number by displaying another InputBox. The value that the user inputs is assigned to the “int_2” variable.
Addition = int_1 + int_2:
- This line calculates the sum of the two numbers entered by the user and assigns the result to the “Addition” variable.
MsgBox “The sum of the numbers is ” & Addition:
- This line displays a message box with the text “The sum of the numbers is [Addition]” where [Addition] is replaced with the value of the “Addition” variable.
Exit Sub:
- This line is executed if there are no errors and exits the subroutine.
Error_Text:
- This line labels the beginning of the error handling section.
MsgBox “You did not insert valid numbers”, vbCritical:
- This line displays a message box with the text “You did not insert valid numbers”. The “vbCritical” argument specifies the type of message box as critical.
- Right after entering the first number, select the second number from the worksheet, and select cell B5 for this.
- Press OK after this.
- Right after pressing OK, you will notice that there is a warning sign, showing that we might make some error while entering the data.
- The error we made is in the previous step, where we selected Cell D5, which contains a String-type But in Excel, mixing data types can lead to an error, and that is exactly what happened here.
- Press OK after this.
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
This VBA Excel code has an error handling declaration to manage any errors that may occur. It declares two variables, int_1 and int_2, and assigns user inputs to them. It then adds the two variables and displays the result using a MsgBox. If an error occurs, it displays a MsgBox with an error message. Finally, the code ends with an Exit Sub statement.
5. Create MsgBox with a Userform
We can use a UserForm to use a MsgBox function in Excel VBA because it allows us to customize the MsgBox according to our wishes. Moreover, a UserForm can be reused and easily modified, making it a more flexible solution for displaying custom message boxes in VBA.
Steps
For reference, you can look at the userform created below. As you can see, our primary target is to calculate the Summation of the stocks of a company, and the stocks of the company need to be selected manually using the range selector. After inputting the range of the stocks, we can press the submit button and after that we can see a small message box showing the summation of the stocks that we have selected just now.
- Then we double-click on the Submit Button to enter the coding section. And enter the following code in that code editor window.
Private Sub CommandButton1_Click()
Dim myRng As Range
Dim Addition As Integer
Set myRng = Range(RefEdit1.Text)
Addition = 0
For i = 1 To myRng.Cells.Count
Addition = Addition + myRng.Cells(i)
Next i
MsgBox "The total number of the stock is " & Addition
End Sub
VBA Code Breakdown
Private Sub CommandButton1_Click():
- This line declares a VBA subroutine that will be executed when the user clicks on a command button in the worksheet.
Dim myRng As Range:
- This line declares a variable named “myRng” as a Range object. The Range object represents a cell or a range of cells in a worksheet.
Dim Addition As Integer:
- This line declares a variable named “Addition” as an integer data type.
Set myRng = Range(RefEdit1.Text):
- This line sets the “myRng” variable to the range of cells that is specified in the RefEdit1 control on the worksheet. The RefEdit1 control is a type of input control that allows the user to select a range of cells in the worksheet.
Addition = 0:
- This line initializes the “Addition” variable to zero.
For i = 1 To myRng.Cells.Count:
- This line starts a For loop that will iterate from one to the number of cells in the “myRng” range.
Addition = Addition + myRng.Cells(i):
- This line adds the value of the i-th cell in the “myRng” range to the “Addition” variable.
Next i:
- This line tells the For loop to move on to the next iteration.
MsgBox “The total number of the stock is ” & Addition:
- This line displays a message box with the text “The total number of the stock is [Addition]” where [Addition] is replaced with the value of the “Addition” variable.
- After entering the code, launch the UserForm.
- Then select the range of cell D5:D8 as the input range for the Summation calculation.
- After the range of cells is fixed, click on the Submit
- After pressing the Submit button, you can see that there is a new message box showing the number of stocks on the selected range of cells.
Frequently Asked Questions
1. What is the use of MsgBox?
1. Displaying messages: You can use MsgBox to show messages to users, such as informative messages, warnings, or error messages, during the execution of your VBA code.
2. Prompting for user input: MsgBox can be used to prompt users for input by displaying a dialog box with buttons, input fields, or other controls. Users can input values, make selections, or respond to prompts using MsgBox.
3. Displaying results: MsgBox can be used to display the results of calculations, processing, or other operations in a message box for users to view.
4. Controlling program flow: You can use MsgBox to pause the execution of your VBA code and wait for user input before proceeding to the next step, allowing users to confirm or cancel operations.
5. Debugging and testing: MsgBox can be used for debugging and testing purposes, by displaying the values of variables, checking the state of objects, or verifying the progress of code execution.
2. How do I answer a message box in VBA?
We can use the two-way message box function which we can give to the message box and take decisions accordingly. Look for the following code in the VBA Userform
Sub AnswerMsgBox()
Dim result As Integer
result = MsgBox("Do you want to proceed?", vbYesNo)
If result = vbYes Then
MsgBox "You clicked Yes."
ElseIf result = vbNo Then
MsgBox "You clicked No."
Else
MsgBox "You closed the message box."
End If
End Sub
- Right after running the code.
- After pressing the Yes button.
3. How Do I Write Multiple Lines in MsgBox VBA?
In VBA, you can display multiple lines in a MsgBox by using the vbCrLf constant to represent a newline character, or by using the vbNewLine constant, which is equivalent.
Here is an example of how you can write multiple lines in a MsgBox in VBA using vbNewLine:
Sub MultipleLinesMsgBox()
MsgBox "This is line 1" & vbNewLine & "This is line 2" & vbNewLine & "This is line 3"
End Sub
Things to Remember
- Other than using the assistance button as a fourth button, adding four buttons to a message box is not possible.
- Avoid overusing message boxes and be mindful of interrupting the user’s workflow with excessive or unnecessary message boxes.
Conclusion
Here we can see that the article shows how we can use the MsgBox function in various cases and situations. You need to understand what type of message box you need and then use the message box type accordingly. By using them you can make some decisions, do some error handling, and create a customized version of them to make the project more user-friendly. If you need any more assistance regarding Excel-related problems, then you should really check out the Exceldemy site.
Related Articles
- How to Use VBA Forecast Function in Excel (3 Ideal Examples)
- Call a Sub in VBA in Excel (4 Examples)
- How to Create a Body Mass Index Calculator in Excel Using VBA
- Use VBA Asc Function (5 Practical Examples)
- How to Use VBA Large Function in Excel (4 Suitable Examples)
- Use VBA StrComp in Excel (5 Common Examples)
- How to Use IsNull Function in Excel VBA (5 Examples)