Excel VBA MsgBox Examples (11 Suitable MsgBoxes)

VBA (Visual Basic Application) is a powerful programming language that allows you to automate different tasks in Excel. One of the most useful functions of VBA is the MsgBox function. In this article, we will discuss in detail what is Excel VBA MsgBox with examples, how to use it, and illustrate the use of MsgBox function with multiple examples.

Here in the above overview video, we have the mark distribution of students. The dataset contains the names of the students, their student IDs, and how much they have scored in subjects: physics, chemistry, and math. However, some scores are missing from the table.

Introduction to MsgBox Function in Excel VBA

The MsgBox function is used to display a message box in Excel VBA. A message box is a dialog box that displays a message and waits for the user to respond to it. The MsgBox function can display different types of message boxes, depending on the parameters that are passed to it. The most common types of message boxes are the information, warning, and error message boxes. The syntax for the message box function is as follows:

Syntax:

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

Arguments Explanation:

The MsgBox function is a powerful tool in Excel VBA that allows you to display message boxes with different types of messages and buttons. This is very helpful for communicating with the user and getting input from them. By understanding the syntax and parameters of the MsgBox function, you can use it to create custom message boxes that meet your specific needs.

Buttons:

By using buttons, we can control the flow of the program based on the user’s response, allowing us to take different actions depending on the user’s choice. There are different built-in buttons for the MsgBox function. Here, the first group with values (0-5) refers to different types of buttons displayed in the dialog box. The second group (0, 256, 512, 768) refers to the default button of the message box. The third group (0, 4096) determines the modality of the message box. When combining numbers to create a value for the button arguments, you should use only one button from each group.

Icons:

The MsgBox icons are a useful tool in Excel VBA for communicating with users in a clear and efficient way. There are four icons for the MsgBox function. You can use only one icon in a particular message box.

Button Constants:

There are a total of 7 button constants for the buttons, as shown in the table below:

How to Open VBA Macro Editor in Excel

You need to follow the steps below to get into the VBA Macro Editor in your worksheet:

• You will see the Developer tab on top of the worksheet. Click on the Developer tab and select Visual Basic. Alternatively, you can press Alt+F11.

• A new window will appear. It is the Visual Basic Editor To write a new code, go to Insert > Module.

• In the module, write the code and click on the Run button to run the code. You can also press the F5 key to run the code.

Excel VBA MsgBox Examples: 11 Suitable MsgBoxes

We can use the MsgBox function in many ways, depending on the purpose of the user. To use the MsgBox function in Excel VBA, you need to provide a message text and specify the button options, icon type, and default button. The user’s response can then be stored in a variable and used to perform various actions. We will show some useful examples demonstrating the use of MsgBox function in Excel VBA.

1. Create MsgBox with a Title

Our first example is to create Excel VBA MsgBox with a title to give the user a clear indication of what the message is about and what action they need to take, making it easier for them to understand the purpose of the message and respond accordingly.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub MsgBox_Title()
    'variable declaration
    Dim Name As String
    Name = Range("B2")
    'create MsgBox with a title argument
    MsgBox "Information of Students", Title:=Name
End Sub

This VBA code creates a MsgBox with the title argument set to the value of cell B2. The message in the box reads “Information of Students“.

Read More: How to Show Range Address with MsgBox in Excel VBA

2. Create a MsgBox with Multiple Lines

We can use multiple lines in the MsgBox to display longer messages or to provide detailed information to the user. This can improve the clarity and readability of the message and make it easier for the user to understand the information being presented.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Multiple_Lines()
    'variable declaration
    Dim Str1, Str2, Str3, Str4, Str5 As String
    Str1 = Range("B4")
    Str2 = Range("C4")
    Str3 = Range("D4")
    Str4 = Range("E4")
    Str5 = Range("F4")
    'create MsgBox with a new line
    MsgBox "The following information has been given" _
    & vbNewLine & Str1 & "," & Str2 & "," _
    & Str3 & "," & Str4 & "," & Str5
End Sub

This VBA code stores data from cells B4 to F4 in separate variables and then displays a MsgBox showing the values of those variables, separated by commas, in the second line. The message starts with “The following information has been given” and uses the vbNewLine function to create a new line in the MsgBox.

Read More: Excel VBA: Create New Line in MsgBox

3. Showing Result in a MsgBox Based on Condition

We can create a MsgBox with an IF statement in Excel VBA to conditionally display a message based on certain criteria or user input, allowing us to tailor the message to specific situations or inputs and provide a more customized user experience.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub IF_MsgBox()
    'variable declaration
    Dim ID As Integer
    'take the variable as an input
    ID = InputBox("Please write the Student ID")
    'if the variable is divisible by 2, _
    show a message in the MsgBox
    If ID Mod 2 = 0 Then
        MsgBox ID & " is an even number"
    Else
        MsgBox ID & " is an odd number"
    End If
End Sub

This VBA code prompts the user to enter a student ID using an input box, and then checks if the ID is even or odd by using the Mod operator. If the ID is even, a MsgBox appears with the statement that the entered ID is an even number. Otherwise, it shows the ID is an odd number.

4. Show Multiple MsgBox with For Loop

We can create a MsgBox with a For loop in Excel VBA to display a series of messages or prompts to the user based on certain conditions or inputs. The For loop allows us to repeat the MsgBox code block a specified number of times or for a specified range of values.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub For_Loop_MsgBox()
    'variable declaration
    Dim ID As Range
    'assign the value of the variable
    Set ID = Range("C5:C16")  
    'if the variable is divisible by 2 for any value, _
    show a message in the MsgBox
    For i = 1 To ID.Cells.count
        If ID.Cells(i) Mod 2 = 0 Then
            MsgBox ID.Cells(i) & " is an even number"
        End If
    Next i
End Sub

This VBA code assigns the range of cells C5 to C16 to a variable, ID, and uses a for loop to iterate over each cell in the range. If a cell value is divisible by 2, a MsgBox appears with the statement that the value is an even number. Otherwise, nothing happens.

Read More: Excel VBA to Display Message Box for 5 Seconds

5. Show Multiple MsgBox with While Loop

We can create a MsgBox with a while loop in Excel VBA to display messages or prompts until a certain condition is met. The While Loop allows us to repeat the MsgBox code block as long as a particular condition is true.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub While_Loop_MsgBox()
    'variable declaration
    Dim i, count As Integer
    Dim ID As Range
    Set ID = Range("D5:D16")
    count = 0
    i = 0
    'show the first three values of the variable in a MsgBox, _
    if they are greater than 80
    While count < 3
        i = i + 1
        If ID.Cells(i, 1) > 80 Then
            count = count + 1
            MsgBox ID.Cells(i, -1)
        End If
    Wend
End Sub

This VBA code initializes two integer variables, i and count, and assigns the range of cells D5 to D16 to a variable ID. It then uses a while loop to iterate over the cells in the range and display the first three cell values that are greater than 80 in a MsgBox. The count variable is used to track the number of cells displayed.

6. Use of Buttons in MsgBox

We use buttons in the MsgBox to give the user options for how to respond to a message. By presenting the user with multiple buttons, we can prompt the user to take specific actions, such as confirming, canceling, or retrying an action. The type of button we use can also influence the user’s behavior, such as using an OK button to encourage the user to proceed with an action, or a Cancel button to allow the user to opt out of an action. Buttons can improve the user experience by making it easier for the user to interact with the message, and they can reduce errors or confusion by providing clear choices for the users to make.

6.1. Use of vbOKOnly Argument to Create MsgBox with OK Button

We can use the vbOKOnly button in the MsgBox when we only need the user to acknowledge the message, without any other options or choices. This button displays only an OK button for the user to click, which confirms that the user has seen the message and allows the program to continue.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbOKOnly_Button()
    'variable declaration
    Dim Name As String
    Name = Range("B2")
    'show a MsgBox with OK button
    MsgBox "This is the " & Name, vbOKOnly
End Sub

This VBA code stores the value of cell B2 in a variable named Name and displays a MsgBox with the concatenated string “This is the ” and the value of the Name variable. The MsgBox has an OK button.

Read More: Excel VBA Code to Click OK on Message Box Automatically

6.2. Use of vbOKCancel Argument to Create MsgBox with OK and Cancel Buttons

We use the vbOKCancel button in the MsgBox when we want to prompt the user either to confirm or cancel an operation. This button displays both OK and Cancel options for the user to choose from, allowing the user to indicate whether to proceed with an operation or cancel it.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbOKCancel_Button()
    'variable declaration
    Dim button_option As Integer
    '(1) create a MsgBox with OK and Cancel buttons
    '(2) assign the value of the MsgBox constant to the variable
    button_option = MsgBox("Do you want to add a value?", vbOKCancel)
    'do an operation and show a MsgBox _
    depending on the value of the variable
    If button_option = 1 Then
        Range("F7") = 60
        MsgBox "The value has been added"
    End If
End Sub

This VBA code creates a MsgBox with OK and Cancel buttons and assigns the value of the selected button to a variable named button_option. Depending on the value of the variable, if the OK button is selected, the code adds a value of 60 to cell F7 and shows a MsgBox with the text “The value has been added”. If the Cancel button is selected, nothing happens.

6.3. Use of vbAbortRetryIgnore Argument to Create MsgBox with Abort, Retry, and Ignore Buttons

The vbAbortRetryIgnore button gives the user three options. He can abort the operation by clicking the Abort button. He can give it another try by clicking the Retry button. Otherwise, he can ignore the operation by clicking the Ignore button.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbAbortRetryIgnore_Button()
    'variable declaration
    Dim button_option As Integer
    '(1) create a MsgBox with Abort, Retry, and Ignore buttons
    '(2) assign the value of the MsgBox constant to the variable
    button_option = MsgBox _
    ("Some data are missing. Do you want to continue?", _
    vbAbortRetryIgnore)
    'show a MsgBox depending on the value of the variable
    If button_option = 3 Then
        MsgBox "ABORT!"
    ElseIf button_option = 4 Then
        MsgBox "RETRY"
    Else
        MsgBox "IGNORE"
    End If
End Sub

This VBA code creates a MsgBox with Abort, Retry, and Ignore buttons and assigns the value of the selected button to a variable named button_option. Depending on the value of the variable, if the Ignore button is selected, a MsgBox with the text “IGNORE” will appear. If the Retry button is selected, a MsgBox with the text “RETRY” will appear. If the Abort button is selected, a MsgBox with the text “ABORT!” will appear.

6.4. Use of vbYesNo Argument to Create MsgBox with Yes and No Buttons

The vbYesNo button in a MsgBox is used to prompt the user to select between two options: Yes or No. This allows the VBA code to execute different actions based on the user’s response.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbYesNo_Button()
    'variable declaration
    Dim button_option As Integer
    '(1) create a MsgBox with Yes and No buttons
    '(2) assign the value of the MsgBox constant to the variable
    button_option = MsgBox("Do you want to add a value?", vbYesNo)
    'do an operation and show a MsgBox _
    depending on the value of the variable
    If button_option = 6 Then
        Range("F7") = 60
        MsgBox "The value has been added"
    End If
End Sub

This VBA Excel code creates a MsgBox with Yes and No buttons and assigns the value of the MsgBox constant to the variable. If the user clicks Yes, then the code adds a value to cell F7 and shows a message indicating that the value has been added.

Read More: Excel VBA: Develop and Use a Yes No Message Box

6.5. Use of vbYesNoCancel Argument to Create MsgBox with Yes, No, and Cancel Buttons

We use the vbYesNoCancel button to prompt the user to select between three options: Yes, No, or Cancel. This allows the VBA code to perform different actions based on the user’s response, including canceling the current operation.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbYesNoCancel_Button()
    'variable declaration
    Dim button_option As Integer
    '(1) create a MsgBox with Yes, No and Cancel buttons
    '(2) assign the value of the MsgBox constant to the variable
    button_option = MsgBox _
    ("Do you want to add a value?", vbYesNoCancel)
    'do an operation and show a MsgBox _
    depending on the value of the variable
    If button_option = 6 Then
        Range("F7") = 60
        MsgBox "The value has been added"
    ElseIf button_option = 7 Then
        MsgBox "Don't add"
    Else
        MsgBox "Cancel it!"
    End If
End Sub

The code creates a MsgBox with Yes, No, and Cancel buttons. The user’s response to the MsgBox is stored in the button_option variable. The code performs an operation based on the user’s response and displays a MsgBox accordingly. If the user clicks Yes, a value is added to Range F7; if they click No, a message is displayed; and if they click Cancel, another message is displayed.

6.6. Use of vbRetryCancel Argument to Create MsgBox with Retry and Cancel Buttons

The vbRetryCancel button gives two options to the user: Retry or Cancel. This allows the VBA code to handle errors or issues that may occur during execution and allow the user to retry the operation or cancel it altogether.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbRetryCancel_Button()
    'variable declaration
    Dim button_option As Integer
    '(1) create a MsgBox with Retry and Cancel buttons
    '(2) assign the value of the MsgBox constant to the variable
    button_option = MsgBox _
    ("The file was not saved. Try again?", vbRetryCancel)
    'show a MsgBox depending on the value of the variable
    If button_option = 4 Then
        MsgBox "RETRY!"
    Else
        MsgBox "Cancel it!"
    End If
End Sub

A variable named button_option is declared as an integer. A MsgBox is displayed with a Retry and Cancel button option using the MsgBox function and the returned value is stored in button_option. Then, a MsgBox is displayed with either “RETRY!” or “Cancel it!” depending on the value of button_option.

6.7. Use of vbDefaultButton1 Argument to Create MsgBox with First Default Button

The vbDefaultButton1 parameter in a MsgBox is used to specify the default button. In most cases, this is set to the first button (Usually, the OK button), so if the user simply presses Enter or Return, the default action is taken.

• Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbDefaultButton1_VBA()
    'variable declaration
    Dim button_option As Integer
    '(1) create a MsgBox where the first button is default
    '(2) assign the value of the MsgBox constant to the variable
    button_option = MsgBox _
    ("Do you want to add a value?", vbYesNoCancel + vbDefaultButton1)
    'do an operation and show a MsgBox _
    depending on the value of the variable
    If button_option = 6 Then
        Range("F7") = 60
        MsgBox "The value has been added"
    ElseIf button_option = 7 Then
        MsgBox "Don't add"
    Else
        MsgBox "Cancel it!"
    End If
End Sub

This VBA Excel code creates a MsgBox with three buttons, the first button is the default. It then assigns the value of the MsgBox constant to a variable called button_option. Depending on the value of the button_option variable, it does an operation and shows a MsgBox with a corresponding message.

• Once the MsgBox with buttons pops up, press Enter to select the default button.

6.8. Use of vbDefaultButton2 Argument to Create MsgBox with Second Default Button

The vbDefaultButton2 parameter is used to specify the second default button in a MsgBox. This allows for a secondary action to be taken if the user presses Enter or Return without explicitly selecting a button, such as confirming a potentially destructive action.

• Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbDefaultButton2_VBA()
    'variable declaration
    Dim button_option As Integer
    '(1) create a MsgBox where the second button is default
    '(2) assign the value of the MsgBox constant to the variable
    button_option = MsgBox _
    ("Do you want to add a value?", vbYesNoCancel + vbDefaultButton2)
    'do an operation and show a MsgBox _
    depending on the value of the variable
    If button_option = 6 Then
        Range("F7") = 60
        MsgBox "The value has been added"
    ElseIf button_option = 7 Then
        MsgBox "Don't add"
    Else
        MsgBox "Cancel it!"
    End If
End Sub

This VBA Excel code creates a MsgBox with three buttons, where the second button is the default. It then assigns the value of the MsgBox constant to a variable called button_option. Depending on the value of the button_option variable, it does an operation and shows a MsgBox with a corresponding message.

• Once the MsgBox with buttons pops up, press Enter to select the default button.

6.9. Use of vbDefaultButton3 Argument to Create MsgBox with Third Default Button

We use the vbDefaultButton3 parameter in a MsgBox to specify the third default button. This allows for a tertiary action to be taken if the user presses Enter or Return.

• Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbDefaultButton3_VBA()
    'variable declaration
    Dim button_option As Integer
    '(1) create a MsgBox where the third button is default
    '(2) assign the value of the MsgBox constant to the variable
    button_option = MsgBox _
    ("Do you want to add a value?", vbYesNoCancel + vbDefaultButton3)
    'do an operation and show a MsgBox _
    depending on the value of the variable
    If button_option = 6 Then
        Range("F7") = 60
        MsgBox "The value has been added"
    ElseIf button_option = 7 Then
        MsgBox "Don't add"
    Else
        MsgBox "Cancel it!"
    End If
End Sub

This VBA Excel code creates a MsgBox with three buttons, where the third button is the default. It then assigns the value of the MsgBox constant to a variable called button_option. Depending on the value of the button_option variable, it does an operation and shows a MsgBox with a corresponding message.

• Once the MsgBox with buttons pops up, press Enter to select the default button.

6.10. Use of vbApplicationModal Argument to Create MsgBox

We use vbApplicationModal in the MsgBox to ensure that the user must respond to the message before continuing to work with other Excel applications, preventing them from accessing other parts of the application until they have addressed the MsgBox.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbApplicationModal_Button()
    'variable declaration
    Dim Name As String
    Name = Range("B2")
    'show a MsgBox with Application Modal
    MsgBox "This is the " & Name, vbApplicationModal
End Sub

This VBA code declares a string variable Name and assigns it the value of cell B2, and then displays a MsgBox with the value of Name using Application Modal. This means that the MsgBox must be closed before any other action can be performed in the application.

6.11. Use of vbSystemModal Argument to Create MsgBox

We use vbSystemModal in a MsgBox to ensure that the MsgBox is displayed on top of all other windows, including windows from other applications. The user must address it before interacting with any other windows or applications.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbSystemModal_Button()
    'variable declaration
    Dim Name As String
    Name = Range("B2")
    'show a MsgBox with System Modal
    MsgBox "This is the " & Name, vbSystemModal
End Sub

This VBA code declares a variable named Name and assigns the value of cell B2. Then it displays a MsgBox with the value of Name and the vbSystemModal option, making the message box system-modal.

6.12. Use of vbMsgBoxHelpButton Argument to Create MsgBox

We use vbMsgBoxHelpButton to include a Help button in the MsgBox that, when clicked, displays additional information or guidance to the user regarding the message or action they should take.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbMsgBoxHelpButton_VBA()
    'variable declaration
    Dim Name As String
    Name = Range("B2")
    'show a MsgBox with a Help button
    MsgBox "Some Values of the  " & Name & " are Missing!", _
    vbMsgBoxHelpButton
End Sub

A string variable named “Name” is declared and assigned the value of cell B2 in the active worksheet. Then a MsgBox is displayed with a message containing “Name” concatenated with the text “Some Values of the” and a Help button, using the MsgBox function with the vbMsgBoxHelpButton constant.

6.13. Use of vbMsgBoxSetForeground Argument to Create MsgBox

We use vbMsgBoxSetForeground to ensure that the MsgBox is displayed on top of all other windows and receives the focus, making it the active window that the user interacts with, even if other windows or applications are currently open or active.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbMsgBoxSetForeground_Button()
    'variable declaration
    Dim Name As String
    Name = Range("B2")
    'show a MsgBox which is set at the foreground
    MsgBox "This is the " & Name, vbMsgBoxSetForeground
End Sub

This VBA code declares a variable named Name which is assigned the value in cell B2. It then shows an Excel VBA MsgBox with the value of the Name variable and sets the MsgBox at the foreground.

6.14. Use of vbMsgBoxRight Argument to Create MsgBox

We use vbMsgBoxRight in a MsgBox to align the text to the right side of the box, making it easier to read and understand for users who speak languages that read from right to left, such as Arabic or Hebrew.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbMsgBoxRight_VBA()
    'show a MsgBox with vbMsgBoxRight argument
    MsgBox "Some of the values are missing!", vbMsgBoxRight
End Sub

This VBA Excel code displays a MsgBox with right-aligned text.

6.15. Use of vbMsgBoxRtlReading Argument to Create MsgBox

We can use vbMsgBoxRltReading in a MsgBox to display the text and buttons in a right-to-left reading order, to ensure that the users who speak languages that read from right to left, such as Arabic or Hebrew, can read and understand the button labels correctly.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbMsgRtlReading_Button()
    'variable declaration
    Dim Name As String
    Name = Range("B2")
    'show a MsgBox with vbMsgBoxRtlReading argument
    MsgBox "Some Values of the  " & _
    Name & " are Missing", vbMsgBoxRtlReading
End Sub

This VBA Excel code creates a MsgBox that shows the message “Some Values of the [Name] are Missing” and is displayed in the right-to-left reading order. The vbMsgBoxRtlReading argument is used to specify the right-to-left reading order.

7. Use of Icons in MsgBox

The MsgBox icons are used in Excel VBA to provide visual cues to the user regarding the type of information being presented. Icons can help to quickly convey the severity of a message or the nature of the response required from the user. Commonly used icons include an exclamation point for warning messages, a question mark for prompting the user for input, and an information icon for displaying general information. Using icons in message boxes can improve the user experience by making it easier to understand the purpose of the message and respond accordingly.

7.1. Use of vbCritical Argument

We use the vbCritical icon in a MsgBox to indicate a critical or severe error or warning message that requires immediate attention from the user. This icon is typically used for messages that could cause data loss or other serious issues if not addressed promptly.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbCritical_Icon()
    'variable declaration
    Dim myRng As Range
    'take the user input as the variable
    Set myRng = Application.InputBox _
    ("Please insert the numbers", Type:=8)
    'show a MsgBox with Critical icon _
    depending on the value of the variable
    For i = 1 To myRng.Cells.count
        If myRng.Cells(i) = "" Then
            MsgBox "value missing!", vbCritical
            Exit For
        End If
    Next i
End Sub

This VBA Excel code declares a range variable and prompts the user to input values into the range. It then loops through each cell in the range and displays a MsgBox with a critical icon if a cell is empty.

7.2. Use of vbQuestion Argument

We use the vbQuestion icon in the MsgBox to prompt the user for a yes/no or true/false response, indicating the question being asked requires a binary answer.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbQuestion_Icon()
    'variable declaration
    Dim button_option As Integer
    '(1) create a MsgBox with a Question icon
    '(2) assign the value of the MsgBox constant to the variable
    button_option = MsgBox _
    ("Do you want to add a value?", vbYesNo + vbQuestion)
    ''do an operation and show a MsgBox _
    depending on the value of the variable
    If button_option = 6 Then
        Range("F7") = 60
        MsgBox "The value has been added"
    End If
End Sub

This VBA code creates a MsgBox with a question icon and the options Yes and No. The user’s response is saved to the variable button_option. If the user selects Yes (button_option=6), the value 60 is added to cell F7, and a MsgBox confirming the operation is displayed.

7.3. Use of vbExclamation Argument

We can use the vbExclamation icon to warn the user about a potentially harmful or important situation that requires their attention or action, indicating that the message is of high importance and urgency.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbExclamation_Icon()
    'variable declaration
    Dim myRng As Range    
    'take the user input as the variable
    Set myRng = Application.InputBox _
    ("Please insert an integer number", Type:=8)
    'show a MsgBox with an Exclamation icon _
    depending on the value of the variable
    If IsNumeric(myRng.Value) Then
        MsgBox "Your input value is an integer"
    Else
        MsgBox "The input value is not an integer", vbExclamation
    End If
End Sub

This VBA Excel code declares a variable and takes user input as the value of the variable. It then shows a MsgBox with an exclamation icon, depending on whether the value of the variable is numeric or not. If the value is numeric, the MsgBox displays a message confirming that the value is an integer, otherwise, it shows a message with an exclamation icon indicating that the value is not an integer.

7.4. Use of vbInformation Argument

We can use the vbInformation icon in the MsgBox to provide the user with informative messages or notifications about the state of the program or the system, referring to the fact that the message is for conveying information rather than asking for user input or indicating an error.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub vbInformation_Icon()
    'variable declaration
    Dim myRng As Range
    'take the user input as the variable
    Set myRng = Application.InputBox _
    ("Insert the numbers here", Type:=8)
    'show a MsgBox with an Information icon _
    depending on the value of the variable
    For i = 1 To myRng.Cells.count
        If myRng.Cells(i) = "" Then
            MsgBox "There are empty cells here. Please have a look.", _
            vbInformation
            Exit For
        End If
    Next i
End Sub

This VBA code prompts the user to input numbers and stores them in a variable. Then, it checks for empty cells in the range and displays a MsgBox with an information icon if any empty cells are found.

8. Create MsgBox with Variables

We can use the MsgBox to assign the value of the options button to a variable. Then we can use the variable to perform different operations.

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.

9. Create MsgBox for Error Handling

We create a MsgBox for error handling to inform the user about the occurrence of an error and provide information on how to fix it. The MsgBox can also help the user to understand the nature and severity of the error and assist in troubleshooting.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Error_Handling()
    'error handling declaration
    On Error GoTo Error_Text
    'variable declaration
    Dim int_1, int_2 As Range
    Dim Addition As Integer    
    'take the user inputs as the variables
    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
    'show a MsgBox depending on the value of a variable
    MsgBox "The sum of the numbers is " & Addition
    Exit Sub
    'show this MsgBox when an error occurs
Error_Text:
    MsgBox "You did not insert valid numbers", vbCritical       
End Sub

This VBA Excel code has an error handling declaration to handle 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.

10. Create MsgBox with a Userform

We can use a UserForm to create a MsgBox 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.

• Go to Insert > UserForm.

• Create a UserForm. You can take a Label to write anything, you can take a RefEdit to take user input, and you can take a CommandButton that works just like a MsgBox You can change the properties of these tools from the Properties Window (press F4 to view the window).

• Click on the CommandButton twice and a new window will appear. Put the following code in the window:

Private Sub CommandButton1_Click()
    'variable declaration
    Dim myRng As Range
    Dim Addition As Integer
    'take the variable as an input
    Set myRng = Range(RefEdit1.Text)
    Addition = 0
    'sum up different values of the variable
    For i = 1 To myRng.Cells.count
        Addition = Addition + myRng.Cells(i)
    Next i
    'show a MsgBox
    MsgBox "The total number of the student is " & _
    Addition & " out of 300"
End Sub

This VBA Excel code defines a subroutine that executes when the CommandButton1 is clicked. It first declares two variables, myRng, and Addition, then assigns a range of cells to myRng based on the user input in the RefEdit1 control. The code then loops through each cell in the range, adding the cell value to the variable Addition. Finally, it displays a MsgBox showing the total sum of the cell values and a text message.

• Now, run the UserForm to see the operation.

11. Show a Two-Dimensional Array in MsgBox

We can show an array in MsgBox if we use the following VBA code.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub MsgBox_Array()
    'variable declaration
    Dim selectedRng As Range
    Dim Row_num As Integer
    Dim myArr() As Variant
    Dim myMsg As String    
    Set selectedRng = Application.InputBox _
    ("Select two columns from the table", Type:=8)    
    Row_num = selectedRng.Rows.count
    'resize the array with a proper dimension
    ReDim myArr(1 To Row_num, 1 To 2)
    'put elements in array
    For i = 1 To Row_num
        For j = 1 To 2
            myArr(i, j) = selectedRng.Cells(i, j)
        Next j
    Next i   
    'show the array elements in a MsgBox

    For i = LBound(myArr, 1) To UBound(myArr, 1)
        For j = LBound(myArr, 2) To UBound(myArr, 2)
                  myMsg = myMsg & myArr(i, j) & vbTab
        Next j
        myMsg = myMsg & vbCr
    Next i
    MsgBox myMsg
End Sub
VBA BreakDown
Sub MsgBox_Array()
Dim selectedRng As Range
Dim Row_num As Integer
Dim myArr() As Variant
Dim myMsg As String
Set selectedRng = Application.InputBox("Select two columns from the table", Type:=8)
Row_num = selectedRng.Rows.count

• This Excel VBA code starts with a sub procedure named MsgBox_Array. It declares variables such as selectedRng as Range, Row_num as Integer, myArr as a Variant array, and myMsg as a String. It prompts the user to select two columns from the table using the InputBox function.

ReDim myArr(1 To Row_num, 1 To 2)
For i = 1 To Row_num
    For j = 1 To 2
        myArr(i, j) = selectedRng.Cells(i, j)
    Next j
Next i

• The code resizes the array dimension to fit the selected data. The For Loop then iterates through the selected range and assigns the values to the array.

For i = LBound(myArr, 1) To UBound(myArr, 1)
        For j = LBound(myArr, 2) To UBound(myArr, 2)
                  myMsg = myMsg & myArr(i, j) & vbTab
        Next j
        myMsg = myMsg & vbCr
Next i
MsgBox myMsg
End Sub

• After this, the code concatenates the array elements to the myMsg string using the vbTab and vbCr Finally, a MsgBox displays the concatenated string to show the array elements.

Things to Remember

Here are some things to keep in mind while using the MsgBox function in Excel VBA:

• The message text should be concise and easy to understand.
• In the second argument of the MsgBox function, you can use the values directly instead of using the button and icon codes or constants.
• The default button is vbOKOnly, if you don’t insert a button code.
• The default title of the MsgBox is “Microsoft Excel” if you don’t insert a title manually.
• It’s a good practice to add error handling to your code to handle unexpected user responses or errors that may occur during the execution of the code.

Frequently Asked Questions

• What is the syntax for using MsgBox function in Excel VBA?

The syntax for using the MsgBox function is as follows:

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

The first parameter, “prompt”, is required and represents the message you want to display in the MsgBox. The rest of the parameters are optional and can be used to customize the MsgBox in different ways.

• How can I change the icon displayed in the MsgBox?

To change the icon displayed in the MsgBox, you can set the icon parameter in the MsgBox function. There are possible values for this parameter (vbCritical, vbQuestion, vbExclamation, and vbInformation). Please see Example 7 to understand the procedure in detail.

• Can I customize the MsgBox to include additional options or fields?

Yes, you can customize the MsgBox additional options or fields by creating a UserForm in Excel VBA. A UserForm in a customizable dialog box that can include text boxes, buttons, checkboxes, and other controls. You can use the UserForm when needed and to capture the user’s input. Please see Example 10 to get accustomed to the procedure.

Download Practice Workbook

You can download the practice book here while going through this article.

Conclusion

In this article, we have discussed in detail how we can use Excel VBA MsgBox with examples for communicating messages to users. It requires parameters such as message text, icon type, button type, and title. The function returns a value that represents the user’s response to the MsgBox. Complex message boxes can be created using userforms. The MsgBox function can improve the user experience and functionality of Excel VBA programs.

This article will allow users to use Excel more efficiently and effectively.  If you have any questions regarding this essay, feel free to let us know in the comments.

Related Articles

• Pop Up Excel VBA MsgBox When Cell Meets Criteria
• VBA MsgBox That Will Automatically Disappear in Excel