MsgBox Example with Excel VBA

This example will demonstrate how to use MsgBox in various scenarios, including handling different button choices and customizing the appearance.

Excel VBA Code Example for MsgBox

Sub MsgBoxExample()
    ' Declare a variable to store the user's response
    Dim response As VbMsgBoxResult
    ' Show a simple message box
    MsgBox "Hello, welcome to Excel VBA!", vbInformation, "Greetings"
    ' Show a message box with Yes and No buttons, and capture the user's choice
    response = MsgBox("Do you want to continue?", vbYesNo + vbQuestion, "Confirmation")
    ' Check if the user clicked Yes or No
    If response = vbYes Then
        MsgBox "You chose to continue.", vbInformation, "Action Taken"
    ElseIf response = vbNo Then
        MsgBox "You chose not to continue.", vbInformation, "Action Cancelled"
    End If
    ' Show a message box with different buttons and capture the response
    response = MsgBox("Are you sure you want to delete the file?", vbYesNoCancel + vbExclamation, "Delete Confirmation")
    ' Evaluate the user's response
    Select Case response
        Case vbYes
            MsgBox "File will be deleted.", vbInformation, "Deleted"
        Case vbNo
            MsgBox "File deletion cancelled.", vbInformation, "Cancelled"
        Case vbCancel
            MsgBox "Action cancelled.", vbInformation, "Cancelled"
    End Select
    ' Show a message box with a timeout (using Application.OnTime) after a specific time period
    ' The MsgBox will be automatically closed after 5 seconds
    MsgBox "This message will close in 5 seconds.", vbInformation, "Timeout Example"
    Application.OnTime Now + TimeValue("00:00:05"), "CloseMsgBox" 
End Sub

Sub CloseMsgBox()
    ' This subroutine will close the message box by simulating a button press
    Application.SendKeys "{ENTER}"
End Sub

Explanation of the Code

1. Simple MessageBox (Basic Information)

MsgBox « Hello, welcome to Excel VBA! », vbInformation, « Greetings »

• Message: « Hello, welcome to Excel VBA! » is the message displayed in the message box.
• Type: vbInformation is a predefined constant that specifies the type of message box to be shown. This adds an « Information » icon.
• Title: « Greetings » is the title of the message box.

This line simply displays a message box with an informational icon and a title of « Greetings. »

2. Yes/No MessageBox with User Response

response = MsgBox(« Do you want to continue? », vbYesNo + vbQuestion, « Confirmation »)

• Message: « Do you want to continue? » is the text shown in the message box.
• Type: vbYesNo + vbQuestion indicates that the message box will have two buttons (Yes and No) and will display a question mark icon.
• Title: « Confirmation » is the title of the message box.

The response from the user is stored in the response variable. The code following the message box checks whether the user clicked « Yes » or « No » and takes appropriate action.

If response = vbYes Then

    MsgBox « You chose to continue. », vbInformation, « Action Taken »

ElseIf response = vbNo Then

    MsgBox « You chose not to continue. », vbInformation, « Action Cancelled »

End If

• If the user clicks « Yes, » a message box is displayed with the message « You chose to continue. »
• If the user clicks « No, » a different message box is displayed with « You chose not to continue. »

3. Yes/No/Cancel MessageBox with Handling Multiple Choices

response = MsgBox(« Are you sure you want to delete the file? », vbYesNoCancel + vbExclamation, « Delete Confirmation »)

• Message: « Are you sure you want to delete the file? » asks the user for confirmation to delete something.
• Type: vbYesNoCancel + vbExclamation adds Yes, No, and Cancel buttons along with an « Exclamation » icon, indicating a warning or important action.
• Title: « Delete Confirmation » provides a title for the message box.

The response is checked using a Select Case statement to evaluate what action the user takes:

Select Case response

    Case vbYes

        MsgBox « File will be deleted. », vbInformation, « Deleted »

    Case vbNo

        MsgBox « File deletion cancelled. », vbInformation, « Cancelled »

    Case vbCancel

        MsgBox « Action cancelled. », vbInformation, « Cancelled »

End Select

• If the user clicks « Yes, » the code confirms that the file will be deleted.
• If the user clicks « No, » it cancels the deletion.
• If the user clicks « Cancel, » the action is also cancelled.

4. MessageBox with Timeout (Automatic Closing)

MsgBox « This message will close in 5 seconds. », vbInformation, « Timeout Example »

Application.OnTime Now + TimeValue(« 00:00:05 »), « CloseMsgBox »

• The message box will be displayed for 5 seconds with the message « This message will close in 5 seconds. »
• The Application.OnTime method is used to schedule a task to run 5 seconds later. This task calls the CloseMsgBox subroutine, which will simulate pressing the Enter key, causing the message box to close automatically.

Sub CloseMsgBox()

    Application.SendKeys « {ENTER} »

End Sub

• The SendKeys method is used to simulate pressing the Enter key, which closes the message box.

Key Points to Remember:

• MsgBox Syntax: MsgBox(prompt, [buttons], [title])
  • prompt: The message displayed in the box.
  • buttons: A combination of constants that specify the buttons and icon for the message box.
  • title: The text displayed in the title bar of the message box.
• Button Constants:
  • vbYesNo: Displays Yes and No buttons.
  • vbYesNoCancel: Displays Yes, No, and Cancel buttons.
  • vbInformation, vbExclamation, vbQuestion: Specifies the icon shown in the message box.
• Return Value: MsgBox returns a value based on which button the user clicked. This can be checked using vbYes, vbNo, or vbCancel.

Conclusion:

The MsgBox function in VBA is useful for displaying simple messages, confirmations, warnings, or informational alerts. It can handle different button choices and icons, and you can even capture and process the user’s response to take action in your VBA code.