VBA MsgBox. A MsgBox is nothing more than a dialog box that can be used to inform your users by displaying a custom message or getting some basic inputs (such as Yes/No or OK/Cancel).
While the MsgBox dialog box is displayed, your VBA code is halted. You need to click any of the buttons in the MsgBox to execute the remaining VBA code.
In this tutorial, the word “message box” and “MsgBox” will be used interchangeably. It’s important you use MsgBox when working with Excel VBA.
Syntax
Msgbox (prompt, [buttons], [title], [helpfile, context])
- Prompt: A Required Parameter. A string that is displayed in the dialog as a message. The maximum length of the prompt is approximately 1024 characters. If the message stretches over more than one line, the lines can be separated between each other with a carriage return character (Chr(13)) or a linefeed character (Chr(10)).
- Buttons: An Optional Parameter. It determines what buttons and icons are displayed in the MsgBox. For example, if I choose vbOKOnly, the OK button will be displayed only, and if I choose vbOKCancel, the OK and Cancel buttons will be shown at the same time.
- Title: An Optional Parameter. In the message dialog box, you can specify the message title you want, which is displayed in the title bar of the MsgBox. If you don’t provide anything, it will display the application’s name.
- Helpfile: An Optional Parameter. This is a String expression that identifies the Help file to use for providing context-sensitive help for the dialog box.
- Context: An Optional Parameter. It is a numeric expression that identifies the Help context number assigned to the appropriate Help topic.
See More: VBA Select Case
Note: All the arguments in square brackets are optional. Only the ‘prompt’ argument is mandatory.
Before we go into the analysis of the VBA code and how the MsgBox looks, here is a table that lists all the different button constants you can use.
Constant |
Value |
Description |
| vbOKOnly | 0 | Shows only the OK button |
| vbOKCancel | 1 | Shows only the OK and Cancel buttons |
| vbAbortRetryIgnore | 2 | Shows the Abort, Retry, and Ignore buttons |
| vbYesNoCancel | 3 | Shows the Yes, No, and Cancel buttons |
| vbYesNo | 4 | Shows the Yes and No buttons |
| vbRetryCancel | 5 | Shows the Retry and Cancel buttons |
| vbCritical | 16 | Shows the critical message icon |
| vbQuestion | 32 | Shows the question icon |
| vbExclamation | 48 | Shows the warning message icon |
| vbInformation | 64 | Shows the information icon |
| vbDefaultButton1 | 0 | The first button is default |
| vbDefaultButton2 | 256 | The second button is default |
| vbDefaultButton3 | 512 | The third button is default |
| vbDefaultButton4 | 768 | The forth button is default |
| vbApplicationModal | 0 | The user must respond to the message box |
| vbSystemModal | 4096 | All applications are suspended until the user responds to the message box |
| vbMsgBoxHelpButton | 16384 | Adds Help button to the message box |
| vbMsgBoxSetForeground | 65536 | Specifies the message box window as the foreground window |
| vbMsgBoxRight | 524288 | Text is eight-aligned |
The above values are logically divided into four groups: The first group (0 to 5) specifies the buttons to be shown in the message box. the second group (16, 32, 48, 64) describes the look of the icon to be displayed; the third group (0, 256, 512, 768) specifies which button should be the default; and the fourth group (0, 4096) defines the message box’s modality.
Return Value
The MsgBox function can return one of the following values which can be used to specify the button the user has clicked in the message box.
Constant |
Value |
Description |
| vboK | 1 | OK |
| vbCancle | 2 | Cancel |
| vbAbort | 3 | Abort |
| vbRetry | 4 | Retry |
| vbIgnore | 5 | Ignore |
| vbYes | 6 | Yes |
| vbNo | 7 | No |
Examples
If we want to display a message box with Yes, No, and Cancel buttons, you can make the coding as shown in the below code.
Sub MsgBox_vbYesNoCancel() Dim strValue As String Dim StrYesNoCancel As String strValue = "Hello World" StrYesNoCancel = MsgBox(strValue, vbYesNoCancel) 'OR' ' MsgBox strValue, 3 MsgBox StrYesNoCancel End Sub
Executes the above function by clicking on the run button on the VBA window. It displays a “Hello World” message box within a message box and an “Yes”,”No”, and “Cancel” button.
After clicking the ‘No’ button, the value of the button (7) is stored as an integer and displayed as a message box to the user, as shown in the following image. This value can be used to determine which button was clicked by the user.
For Example, (vbYes : 6, vbNo: 7, vbCancel : 2)
