VBA MsgBox – Complete Guide with code examples
The MsgBox displays a message with direct interaction from the user. The Msgbox is window that appears on the screen that prompts the user to make a selection, or display information.
Let’s begin by understanding the syntax.
Syntax:
MsgBox(prompt[,buttons][,title][,helpfile,context])
- Prompt − (Required) – A String that is displayed as a message in the dialog box. The maximum length is approximately 1024 characters.
- Buttons − (Optional) A Numeric expression that specifies the type of buttons to display. Refer to the table below.
- Title − (Optional) A String expression displayed in the title bar of the dialog box. If the title is left blank, the application name is placed in the title bar.
- Helpfile − (Optional) A String expression that identifies the Help file to use for providing context-sensitive help for the dialog box.
- Context − (Optional) A Numeric expression that identifies the Help context number assigned by the Help author to the appropriate Help topic. If context is provided, helpfile must also be provided.
Sub YesNoDemo()
' Dim statement is used to Assign the variables
Dim Message, Style, Title, Help, Ctxt, Response, MyString
Message = "Do you like to code?" ' Msgbox Message
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Buttons to be displayed
Title = "Excelbuddy.com MsgBox Demonstration" ' Msgbox Title
Help = "helpfile.txt" ' Define Help file.
Ctxt = 3000 ' Define topic context.
‘ Displaying the actual MsgBox variable inputs from above
Response = MsgBox(Message, Style, Title, Help, Ctxt)
If Response = vbYes Then ‘ Yes as selected answer
MyString = “Yes” ‘ You can add a performed action
Else ‘ No as selected answer
MyString = “No” ‘ You can add a performed action
End If
End Sub
VBA MsgBox Button Parameters
The button parameters are broken up into 5 groups. These constants work as follows:
- Group 1 – Buttons
- Group 2 – Icons
- Group 3 – Select which button is highlighted when the message box appears.
- Group 4 – Set the modal type of the message box.
- Group 5 – General settings
Please note: When using the Msgbox function, you can only select one of each category. This is shown in the code demonstrations below.
- button type
- icon type
- default button
- modal type
Constant | Group | Type | Description |
---|---|---|---|
vbOKOnly | 1 | Buttons | Ok button. |
vbOKCancel | 1 | Buttons | Ok and cancel buttons. |
vbAbortRetryIgnore | 1 | Buttons | Abort, Retry and Ignore buttons. |
vbYesNoCancel | 1 | Buttons | Yes, No and Cancel buttons. |
vbYesNo | 1 | Buttons | Yes and No buttons. |
vbRetryCancel | 1 | Buttons | Retry and Cancel buttons. |
vbCritical | 2 | Icon | Critical Message icon. |
vbQuestion | 2 | Icon | Warning Query icon. |
vbExclamation | 2 | Icon | Warning Message icon. |
vbInformation | 2 | Icon | Information Message icon. |
vbDefaultButton1 | 3 | Default button | Set button 1 to be selected. |
vbDefaultButton2 | 3 | Default button | Set button 2 to be selected. |
vbDefaultButton3 | 3 | Default button | Set button 3 to be selected. |
vbDefaultButton4 | 3 | Default button | Set button 4 to be selected. Note that there will only be four buttons if the help button is included with vbAbortRetryIgnore or vbYesNoCancel. |
vbApplicationModal | 4 | Modal | Cannot access Excel while the button is displayed. Msgbox is only displayed when Excel is the active application. |
vbSystemModal | 4 | Modal | Same as vbApplicationModal but the message box is displayed in front of all applications. |
vbMsgBoxHelpButton | 5 | Other | Adds a help button |
vbMsgBoxSetForeground | 5 | Other | Sets the message box windows to be the foreground window |
vbMsgBoxRight | 5 | Other | Right aligns the text. |
vbMsgBoxRtlReading | 5 | Other | Specifies text should appear as right-to-left reading on Hebrew and Arabic systems. |
VBA MsgBox Return Values
The following are all the return values for the MsgBox function:
vbOk
vbCancel
vbAbort
vbRetry
vbIgnore
vbYes
vbNo
Each of these values represents a button that was clicked.
Msgbox Example
The following code will prompt the user with a statement with “Yes” or “No” as inputs.
In the code below, we will be using the constant vbYesNo from the above chart. We define the input as vbYes. If the the user selects Yes, then the message “Coffee runs the world!” is displayed. If they don’t select Yes, ELSE, the alternative message “How do you operate without coffee?” is displayed.
Sub YesNoDemo()
' Here we display the Return Values
' Make sure the values questions are in parenthesis
If MsgBox("Do you like coffee? ", vbYesNo) = vbYes Then
MsgBox "Coffee runs the world!"
Else
MsgBox "How do you operate without coffee?"
End If
End Sub
Msgbox Message Boxes
Group 2 – Msgbox Icon Demos
Here are examples how to use Group 2 of the Msg Button Paramenters. Group 2 is the Icon’s displayed in the pop-up window. There are four to choose from.
' Yes/No buttons with Critical icon resp = MsgBox("Having fun yet?", vbYesNo + vbCritical) ' Yes/No buttons with Warning Query icon resp = MsgBox("Having fun yet?", vbYesNo + vbQuestion) ' Yes/No buttons with Warning Message icon resp = MsgBox("Having fun yet?", vbYesNo + vbExclamation) ' Yes/No button with Information Message icon resp = MsgBox("Having fun yet?", vbYesNo + vbInformation)
Here are the display messages in Excel.
vbCritical
vbQuestion
vbExclamation
vbInformation
Additional Examples using Button Parameters
' This shows how 4 buttons: Yes, No, Cancel, Help plus it highlights button 2 resp = MsgBox("Example of Button 2 Highlighted", vbYesNoCancel + vbDefaultButton2 + vbMsgBoxHelpButton)
' This shows how 4 buttons: Abort, Retry, Ignore, Help plus it highlights button 3 resp = MsgBox("Example of Button 2 Highlighted", vbAbortRetryIgnore + vbDefaultButton3 + vbMsgBoxHelpButton)