Excel’s Visual Basic for Applications (VBA) provides a dialogue box called MsgBox that is useful for providing helpful information to your program’s end users. The user is presented with a pop-up message box and asked to confirm or cancel their action by clicking one of many buttons. Users can engage with a workbook in this manner. It may only be an alert to the users, or it may be more complex and require them to take some action.
Syntax of the VBA MsgBox Function
MsgBox (prompt, [buttons], [title], [helpfile], [context])
prompt – It refers to the text that appears in the dialogue box. Approximately 1024 characters are the limit for a message length in a public forum. If the character goes outside the allowed range, the message will be split using the carriage return (Chr(13)) or linefeed (Chr(10)) character.
[buttons] – It controls which options and icons appear in the MsgBox. If you use the vbOkOnly directive, only the OK button will appear; if you use the vbOKCancel directive, both the OK and Cancel buttons will be displayed.
[title] – You can choose the caption for the message box here. The MsgBox’s title will reflect this. In the absence of any further instructions, the application’s name will be shown.
[helpfile] – When users select the Help menu item, they will be taken to the help file specified in [helpfile]. The only way to see the help button is to utilise the button code. When accessing a help file, providing the argument for context is essential.
[context] – It is a numeric expression representing the Help context number associated with the relevant Help page.
Read the List Below to Learn About Different Button Constants you can use
- vbOKOnly- Displays the OK button
- vbOKCancel- Displays the OK and Cancel buttons
- vbAbortRetryIgnore- Displays the Abort, Retry, and Ignore buttons
- vbYesNo- Displays the Yes and No buttons
- vbYesNoCancel- Displays the Yes, No, and Cancel buttons
- vbRetryCancel- Displays the Retry and Cancel buttons
- vbMsgBoxHelpButton- Displays the Help button. Use the MsgBox function’s help and context arguments to accomplish this.
- vbDefaultButton1- Sets the first button default. A different number can change the default button. For instance, vbDefaultButton2 sets the second button as the default.
Return Values
| Constant | Value | Description |
| vbOK | 1 | OK |
| vbCancel | 2 | Cancel |
| vbAbort | 3 | Abort |
| vbRetry | 4 | Retry |
| vbIgnore | 5 | Ignore |
| vbYes | 6 | Yes |
| vbNo | 7 | No |
Examples
The following code illustrates how to generate a message box with Yes, No, and Cancel options:
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
Press the run button in the VBA window. The “OK” button and the “Hello World” message box are displayed.
When you select OK, a new dialogue box appears asking, “Do you like to click on “Yes”, “No”, and “Cancel” options.
Following activating any button, such as the yes button, the corresponding number representing that button’s value is saved. In addition, it presented the user with a message box in the form illustrated below. With this number’s help, we can determine which button the user clicked on.
