The MsgBox function in Excel VBA is used to display a message box with a specific message and buttons. Here’s a guide on how to use the MsgBox function in your VBA code

Syntax

The syntax for the MsgBox function in Excel VBA is as follows:

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

Parameters

1.-prompt (required): The message you want to display in the message box. It can be a string or a numeric expression.

2.- buttons (optional): Specifies the buttons to display in the message box. It can be one of the following values:

• vbOKOnly (default): Displays only the OK button.
• vbOKCancel: Displays OK and Cancel buttons.
• vbAbortRetryIgnore: Displays Abort, Retry, and Ignore buttons.
• vbYesNoCancel: Displays Yes, No, and Cancel buttons.
• vbYesNo: Displays Yes and No buttons.
• vbRetryCancel: Displays Retry and Cancel buttons.
• vbCritical: Displays a critical message icon.
• vbQuestion: Displays a question mark icon.
• vbExclamation: Displays an exclamation point icon.
• vbInformation: Displays an information icon.

3.- title (optional): The title or caption for the message box. It can be a string.

4.– helpfile (optional): The Help file to be used with the message box. It can be a string representing the name of the Help file.

5.- context (optional): The context number associated with the Help topic. It can be a numeric expression.

The MsgBox function returns a value that represents the button clicked by the user. The commonly used return values are:

• vbOK: OK button was clicked.
• vbCancel: Cancel button was clicked.
• vbYes: Yes button was clicked.
• vbNo: No button was clicked.
• vbRetry: Retry button was clicked.
• vbIgnore: Ignore button was clicked.

Examples

Button vbOKOnly

Here’s an example of using the button vbOKOnly :

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    
    ' Example with vbOKOnly button
    response = MsgBox("This action cannot be undone. Are you sure you want to proceed?", vbOKOnly + vbExclamation, "Confirmation")
    
    If response = vbOK Then
        MsgBox "Action executed."
    End If
End Sub

In this example, a message box is displayed with an exclamation icon, a prompt asking the user if they want to proceed, and only the OK button using the vbOKOnly option.

If the user clicks OK, a separate message box with the text “Action executed” will be displayed.

Button vbOKCancel

Here’s an example of using the button vbOKCancel:

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    response = MsgBox("Hello, this is a message box!", vbOKCancel + vbInformation, "Important Message")
    
    If response = vbOK Then
        ' Perform action if OK is clicked
    ElseIf response = vbCancel Then
        ' Perform action if Cancel is clicked
    End If
End Sub

In this example, a message box is displayed with the message “Hello, this is a message box!” and OK/Cancel buttons. The response from the user is stored in the variable response, and different actions can be performed based on the clicked button.

Button vbAbortRetryIgnore

Here’s an example of using the button vbAbortRetryIgnore :

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    
    ' Example with vbAbortRetryIgnore buttons
    response = MsgBox("An error occurred. Would you like to retry?", vbAbortRetryIgnore + vbCritical, "Error")
    
    If response = vbAbort Then
        MsgBox "Operation aborted."
    ElseIf response = vbRetry Then
        MsgBox "Retrying operation..."
        ' Perform retry operation
    ElseIf response = vbIgnore Then
        MsgBox "Operation ignored."
    End If
End Sub

In this example, a message box is displayed with a critical icon, a prompt asking the user if they want to retry after an error occurred, and three buttons: Abort, Retry, and Ignore, using the vbAbortRetryIgnore option.

Depending on the user’s choice, different actions will be performed. If the user clicks Abort, a separate message box with the text “Operation aborted” will be displayed. If the user clicks Retry, a message box saying “Retrying operation…” will be displayed, and you can perform the retry operation as needed. If the user clicks Ignore, a message box with the text “Operation ignored” will be displayed.

Button vbYesNoCancel

Here’s an example of using the button vbYesNoCancel:

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    
    ' Example with vbYesNoCancel buttons
    response = MsgBox("Do you want to save changes?", vbYesNoCancel + vbQuestion, "Save Changes")
    
    If response = vbYes Then
        MsgBox "Changes saved."
    ElseIf response = vbNo Then
        MsgBox "Changes not saved."
    Else
        MsgBox "Operation canceled."
    End If
End Sub

In this example, a message box is displayed with a question icon, a prompt asking the user if they want to save changes, and three buttons: Yes, No, and Cancel, using the vbYesNoCancel option.

Depending on the user’s choice, different actions will be performed. If the user clicks Yes, a separate message box with the text “Changes saved” will be displayed. If the user clicks No, a message box saying “Changes not saved” will be displayed. If the user clicks Cancel, a message box with the text “Operation canceled” will be displayed.

Button vbYesNo

Here’s an example of using the button vbYesNo:

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    
    ' Example with vbYesNo buttons
    response = MsgBox("Do you want to proceed?", vbYesNo + vbQuestion, "Confirmation")
    
    If response = vbYes Then
        MsgBox "Proceeding with the operation."
        ' Perform operation
    Else
        MsgBox "Operation canceled."
    End If
End Sub

In this example, a message box is displayed with a question icon, a prompt asking the user if they want to proceed, and two buttons: Yes and No, using the vbYesNo option.

If the user clicks Yes, a separate message box with the text “Proceeding with the operation” will be displayed, and you can perform the operation accordingly. If the user clicks No, a message box with the text “Operation canceled” will be displayed.

Button vbRetryCancel

Here’s an example of using the button vbRetryCancel:

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    
    ' Example with vbRetryCancel buttons
    response = MsgBox("Connection failed. Retry?", vbRetryCancel + vbExclamation, "Connection Error")
    
    If response = vbRetry Then
        MsgBox "Retrying connection..."
        ' Perform retry operation
    Else
        MsgBox "Connection canceled."
    End If
End Sub

In this example, a message box is displayed with an exclamation icon, a prompt asking the user if they want to retry after a connection failure, and two buttons: Retry and Cancel, using the vbRetryCancel option.

If the user clicks Retry, a message box saying “Retrying connection…” will be displayed, and you can perform the retry operation as needed. If the user clicks Cancel, a separate message box with the text “Connection canceled” will be displayed.

Button vbCritical

Here’s an example of using the button vbCritical:

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    
    ' Example with vbCritical button
    response = MsgBox("An unexpected error occurred. Please contact support.", vbCritical + vbOKOnly, "Error")
    
    If response = vbOK Then
        MsgBox "Thank you for reporting the error."
    End If
End Sub

In this example, a message box is displayed with a critical icon, a prompt informing the user about an unexpected error, and only the OK button using the vbOKOnly option.

If the user clicks OK, a separate message box with the text “Thank you for reporting the error” will be displayed.

Button vbQuestion

Here’s an example of using the button vbQuestion:

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    
    ' Example with vbQuestion button
    response = MsgBox("Are you sure you want to delete this item?", vbQuestion + vbYesNo, "Confirmation")
    
    If response = vbYes Then
        MsgBox "Item deleted successfully."
        ' Perform deletion operation
    Else
        MsgBox "Deletion canceled."
    End If
End Sub

In this example, a message box is displayed with a question icon, a prompt asking the user if they are sure they want to delete an item, and two buttons: Yes and No, using the vbYesNo option.

If the user clicks Yes, a separate message box with the text “Item deleted successfully” will be displayed, and you can perform the deletion operation accordingly. If the user clicks No, a message box with the text “Deletion canceled” will be displayed.

Button vbExclamation

Here’s an example of using the button vbExclamation:

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    
    ' Example with vbExclamation button
    response = MsgBox("You have unsaved changes. Do you want to save them?", vbExclamation + vbYesNoCancel, "Unsaved Changes")
    
    If response = vbYes Then
        MsgBox "Changes saved."
        ' Perform save operation
    ElseIf response = vbNo Then
        MsgBox "Changes discarded."
        ' Discard changes
    Else
        MsgBox "Operation canceled."
    End If
End Sub

In this example, a message box is displayed with an exclamation icon, a prompt informing the user about unsaved changes, and three buttons: Yes, No, and Cancel, using the vbYesNoCancel option.

If the user clicks Yes, a separate message box with the text “Changes saved” will be displayed, and you can perform the save operation accordingly. If the user clicks No, a message box with the text “Changes discarded” will be displayed, and you can discard the changes. If the user clicks Cancel, a message box with the text “Operation canceled” will be displayed.

Button vbInformation

Here’s an example of using the button vbInformation:

Sub DisplayMessage()
    Dim response As VbMsgBoxResult
    
    ' Example with vbInformation button
    response = MsgBox("Congratulations! Your registration was successful.", vbInformation + vbOKOnly, "Registration")
    
    If response = vbOK Then
        MsgBox "Thank you for registering."
    End If
End Sub

In this example, a message box is displayed with an information icon, a message informing the user about a successful registration, and only the OK button using the vbOKOnly option.

If the user clicks OK, a separate message box with the text “Thank you for registering” will be displayed.

Help us grow the project

Unlock Your Potential

Excel

Access

Power BI

Join our Facebook Group!