Excel VBA MsgBox -11 Examples

Introduction to the  MsgBox Function in Excel VBA

The MsgBox function is used to display a message box in Excel VBA. It is a dialog box that displays a message and a user’s response.

The syntax for the message box function is:

Syntax:

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

Arguments Explanation:

Buttons:

Buttons control the flow of the program based on the user’s response. There are different built-in buttons for the MsgBox function. 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,  use one button from each group only.

Icons:

There are four icons for the MsgBox function. You can use one icon in a message box only.

Button Constants:

There are a total of 7 button constants:

How to Open the VBA Macro Editor in Excel

• Go to the Developer tab.
• Select Visual Basic. Alternatively, you can press Alt+F11.

• The Visual Basic Editor window will be displayed.
• Go to Insert > Module.

• In the module, enter the code and click Run to run the code. You can also press F5.

Example 1 – Create a MsgBox with a Title

• Enter the following code in the VBA Editor and click Run or press F5 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 B2. The message in the box is “Information of Students“.

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

Example 2 – Create a MsgBox with Multiple Lines

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

Sub MsgBox_Title()

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 B4:F4 in separate variables and 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

Example 3 – Showing a Result in a MsgBox Based on a Condition

Create a MsgBox with an IF statement.

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

Sub MsgBox_Title()

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 checks if the ID is even or odd, using the Mod operator. If the ID is even, a MsgBox is displayed stating the ID is an even number. Otherwise, it shows the ID is an odd number.

Example 4 – Show Multiple MsgBoxes with a For Loop

Create a MsgBox with a For loop in Excel VBA to display a series of messages or prompts.

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

Sub MsgBox_Title()

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 C5:C16 to the 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 is displaying stating that the value is an even number. Otherwise, nothing happens.

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

Example 5 – Show Multiple MsgBoxes with While Loop

Create a MsgBox with a while loop to display messages or prompts until a condition is met.

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

Sub MsgBox_Title()

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 D5:D16 to the variable ID. It 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.

Example 6 – Using Buttons in a MsgBox

6.1. Using the vbOKOnly Argument to Create a MsgBox with an OK Button

Use the vbOKOnly button in the MsgBox to display an OK button.

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

Sub MsgBox_Title()

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 B2 in the Name variable 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. Using the vbOKCancel Argument to Create a MsgBox with OK and Cancel Buttons

Use the vbOKCancel button in the MsgBox to prompt the user either to confirm or cancel an operation.

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

Sub MsgBox_Title()

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 the button_option variable. Depending on the value of the variable, if the OK button is selected, the code adds a value of 60 to F7 and shows a MsgBox with the text “The value has been added”. If the Cancel button is selected, nothing happens.

6.3. Using the vbAbortRetryIgnore Argument to Create a MsgBox with the Abort, Retry, and Ignore Buttons

The vbAbortRetryIgnore button gives the user three options: abort , retry, or ignore the operation.

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

Sub MsgBox_Title()

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 the button_option variable. Depending on the value of the variable, if the Ignore button is selected, a MsgBox with the text “IGNORE” will be displayed. If the Retry button is selected, a MsgBox with the text “RETRY” will be displayed. If the Abort button is selected, a MsgBox with the text “ABORT!” will be displayed.

6.4. Using the vbYesNo Argument to Create a MsgBox with Yes and No Buttons

The vbYesNo button in a MsgBox prompts the user to select Yes or No.

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

Sub MsgBox_Title()

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, the code adds a value to 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. Using the vbYesNoCancel Argument to Create a MsgBox with the Yes, No, and Cancel Buttons

The vbYesNoCancel prompts the user to select Yes, No, or Cancel.

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

Sub MsgBox_Title()

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 F7; if No is clicked a message is displayed; and if Cancel is clicked, another message is displayed.

6.6. Using the vbRetryCancel Argument to Create a MsgBox with the Retry and Cancel Buttons

The vbRetryCancel button gives two options to the user: Retry or Cancel.

Enter the following code in the VBA Editor and click Run or press F5 to run the code:

Sub MsgBox_Title()

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

The button_option  variable 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. A MsgBox is displayed with either “RETRY!” or “Cancel it!” depending on the value of button_option.

6.7. Using the vbDefaultButton1 Argument to Create a MsgBox with the First Default Button

The vbDefaultButton1 parameter in a MsgBox is used to specify the default button.

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

  Sub MsgBox_Title()

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 assigns the value of the MsgBox constant to the button_option variable. Depending on the value of the button_option variable, it performs an operation and shows a MsgBox with a corresponding message.

• When the MsgBox is displayed, press Enter to select the default button.

6.8. Using the vbDefaultButton2 Argument to Create a MsgBox with a Second Default Button

The vbDefaultButton2 parameter is used to specify the second default button in a MsgBox.

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

  Sub MsgBox_Title()

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; the second button is the default button. It assigns the value of the MsgBox constant to the button_option variable. Depending on the value of the button_option variable, it performs an operation and shows a MsgBox with a corresponding message.

• When the MsgBox with buttons is displayed, press Enter to select the default button.

6.9. Using the vbDefaultButton3 Argument to Create a MsgBox with a Third Default Button

Use the vbDefaultButton3 parameter in a MsgBox to specify the third default button.

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

  Sub MsgBox_Title()

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; the third button is the default button. It assigns the value of the MsgBox constant to the button_option variable. Depending on the value of the button_option variable, it performs an operation and shows a MsgBox with a corresponding message.

• When the MsgBox with buttons is displayed, press Enter to select the default button.

6.10. Using the vbApplicationModal Argument to Create a MsgBox

Use the vbApplicationModal in the MsgBox to ensure that the user responds to the message before continuing to work with other Excel applications.

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

Sub MsgBox_Title()

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 the Name string variable and assigns it the value in B2. It displays a MsgBox with the value of Name using the Application Modal. The MsgBox must be closed before any other action is performed in the application.

6.11. Using the vbSystemModal Argument to Create a MsgBox

Use vbSystemModal in a MsgBox to ensure that the MsgBox is displayed on top of all other windows.

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

Sub MsgBox_Title()

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 the Name variable and assigns it to the value in B2. It displays a MsgBox with the value of Name and the vbSystemModal option, making the message box system-modal.

6.12. Using the vbMsgBoxHelpButton Argument to Create a MsgBox

Use the vbMsgBoxHelpButton to include a Help button in the MsgBox.

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

Sub MsgBox_Title()

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

The string variable “Name” is declared and assigned the value in B2 in the active worksheet. 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. Using the vbMsgBoxSetForeground Argument to Create a MsgBox

Use the vbMsgBoxSetForeground to ensure that the MsgBox is displayed on top of all other windows and receives the focus, making it the active window.

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

Sub MsgBox_Title()

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 the Name variable which is assigned the value in B2. It shows an Excel VBA MsgBox with the value of the Name variable and sets the MsgBox at the foreground.

6.14. Using the vbMsgBoxRight Argument to Create a MsgBox

Use the vbMsgBoxRight in a MsgBox to align the text to the right.

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

Sub MsgBox_Title()

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. Using the vbMsgBoxRtlReading Argument to Create a MsgBox

Use vbMsgBoxRltReading in a MsgBox to display the text and buttons in a right-to-left reading order.

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

Sub MsgBox_Title()

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.

Example 7 – Using Icons in a MsgBox

The MsgBox icons are used in Excel VBA to provide visual cues regarding the type of information presented.

7.1. Using the vbCritical Argument

Use the vbCritical icon in a MsgBox to indicate a critical or severe error or warning message that requires immediate attention from the user.

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

Sub MsgBox_Title()

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 code declares a range variable and prompts the user to input values into the range. It loops through each cell in the range and displays a MsgBox with a critical icon if a cell is empty.

7.2. Using the vbQuestion Argument

Use the vbQuestion icon in the MsgBox to prompt the user to give a binary answer: yes/no or true/false.

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

Sub MsgBox_Title()

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 F7, and a MsgBox confirming the operation is displayed.

7.3. Using the vbExclamation Argument

Use the vbExclamation icon to warn the user about a potentially harmful or important situation that requires attention or action.

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

Sub MsgBox_Title()

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 the user input as the value of the variable. It shows a MsgBox with an exclamation icon, depending on whether the value of the variable is numeric. 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. Using the vbInformation Argument

Use the vbInformation icon in the MsgBox to provide informative messages or notifications about the state of the program or the system.

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

Sub MsgBox_Title()

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. It checks for empty cells in the range and displays a MsgBox with an information icon if empty cells are found.

Example 8 – Create a MsgBox with Variables

Use the MsgBox to assign the value of the options button to a variable and use the variable to perform different operations.

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

Sub MsgBox_Title()

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 displays a different MsgBox depending on whether the user chooses Yes or No. If the user selects Yes, a MsgBox thanking them for using the application is displayed, otherwise, a MsgBox saying they chose not to exit is displayed.

Example 9 – Create a MsgBox for Error Handling

Create a MsgBox for error handling to inform the user about the occurrence of an error and provide information on how to fix it.

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

Sub MsgBox_Title()

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 adds the two variables and displays the result using a MsgBox. If an error occurs, it displays a MsgBox with an error message. The code ends with an Exit Sub statement.

Example 10 – Create a MsgBox with a Userform

Use a UserForm to create and customize  a MsgBox in Excel VBA.

• Go to Insert > UserForm.

• Create a UserForm. You can add a Label, a RefEdit, and a CommandButton that works just like a MsgBox. You can change the properties of these tools in the Properties Window (press F4).

• Click the CommandButton twice and a new window will  be displayed. Enter the following code:

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 is executed when CommandButton1 is clicked. It declares two variables, myRng, and Addition, assigns a range of cells to myRng based on the user input in the RefEdit1 control. The code loops through each cell in the range, adding the cell value to the variable Addition. It displays a MsgBox showing the total sum of the cell values and a text message.

• Run the UserForm to see the operation.

Example 11 – Show a Two-Dimensional Array in a MsgBox

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

Sub MsgBox_Title()

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 the MsgBox_Array sub procedure. It declares variables: 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 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

• The code concatenates the array elements to the myMsg string using the vbTab and vbCr.
• A MsgBox displays the concatenated string to show the array elements.

Things to Remember

• 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 enter a title.
• It’s good practice to add error handling to the code.

Frequently Asked Questions

• What is the syntax of the MsgBox function in Excel VBA?

The syntax is:

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, set the icon parameter in the MsgBox function.

Download Practice Workbook

Download the practice book.

Related Articles

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