In VBA, MsgBox displays a dialog with a prompt, optional buttons, an icon, a title, and other settings. Set its button and icon options in the second argument, then check the value it returns—such as vbYes or vbNo—to make the macro respond to the user’s choice.
How the VBA MsgBox Function Works
The basic syntax is MsgBox(prompt, buttons, title, helpfile, context). Only the prompt is required. If you leave out the buttons argument, VBA displays an OK button; if you omit the title, the dialog uses the host application name. The function pauses the running procedure until the user responds, then returns a value identifying the button selected.
The arguments are:
- Prompt: The message text. It can be a string variable or a string assembled from text and values.
- Buttons: Optional button arrangement, icon, default button, and behavior flags. You can combine compatible options.
- Title: Optional text shown in the dialog’s title bar.
- Helpfile and context: Optional help-file name and context identifier for a help topic. These are uncommon in everyday Excel macros.
For example, MsgBox "Import finished.", vbOKOnly Or vbInformation, "Import status" displays an informational message with an OK button. The function is useful for brief interaction, not for building a complete form or collecting several fields.
Button Options
The button constants determine which choices appear. Choose a set that matches the action the macro needs to take; an OK-only dialog is appropriate for a notification, while a question that changes what the macro does should offer meaningful choices.
vbOKOnly: OK. This is the default.vbOKCancel: OK and Cancel.vbAbortRetryIgnore: Abort, Retry, and Ignore.vbYesNoCancel: Yes, No, and Cancel.vbYesNo: Yes and No.vbRetryCancel: Retry and Cancel.
Use the named constants rather than memorizing their numeric values. For example, vbYesNo is clearer to a later reader than the number 4. These constants describe the available choices; they do not decide what the macro should do. Your code must examine the return value and implement the appropriate branch.
Do not use a Yes/No prompt when the user needs a safe way to back out. If stopping is a distinct and important choice, use a button set that includes Cancel, such as vbYesNoCancel, and handle all three results.
Icons, Default Buttons, and Other Flags
An icon can signal whether a dialog is informational, cautionary, or an error. The common icon constants are vbInformation, vbExclamation, vbCritical, and vbQuestion. A question icon does not change the available buttons or the return values; it is a visual cue. Pick an icon that reinforces the prompt rather than treating it as a substitute for clear wording.
The default button is the one initially selected, which may be activated by pressing Enter. Use vbDefaultButton1, vbDefaultButton2, vbDefaultButton3, or vbDefaultButton4 to select a position in the displayed button set. The first button is the default if you do not specify another. For a consequential choice, consider making the less destructive option the default, and spell out the consequence in the prompt.
Other flags include vbApplicationModal, the usual application-modal behavior, and vbSystemModal, which can place the dialog above other windows and interrupt activity beyond Excel. System-modal dialogs are rarely appropriate for a normal workbook prompt. Additional flags can request a Help button or affect alignment and reading direction. Some visual details may differ across Windows and Mac versions of Office, so do not rely on a flag for essential meaning.
Combining Options Correctly
Options in the buttons argument are combined with the VBA Or operator. A button-set constant, an icon constant, and a default-button constant can be combined to describe one dialog. For example, vbYesNo Or vbQuestion Or vbDefaultButton2 requests Yes and No buttons, a question icon, and the second button as the default.
Use named constants and keep the expression readable. Avoid adding unrelated constants or relying on unexplained numbers: the buttons argument is a bit-field made from option values, so a random number can produce an unintended combination. Also make sure the default-button position exists in the selected button set. A third-button default makes no sense for a two-button dialog.
The plus operator is sometimes seen in older examples, but Or communicates that the values are flags being combined. It is the clearer convention for this purpose.
Return Values and Conditional Logic
Although MsgBox looks like a display command, it is a function: its result tells the procedure which button the user clicked. Common return constants include vbOK, vbCancel, vbAbort, vbRetry, vbIgnore, vbYes, and vbNo. Compare the result with the named constant that corresponds to the selected button.
For a two-choice prompt, an If statement is often sufficient. For three or more choices, use Select Case so every result is visible and handled. A Cancel response should not accidentally fall through to the same action as Yes. Writing an explicit branch also makes it easier to review the macro’s behavior.
VBA constants are case-insensitive, but consistent capitalization makes code easier to scan. Declaring the result as VbMsgBoxResult also makes its purpose clear to someone reading the procedure, rather than storing it in an unlabelled general-purpose variable.
Practical Excel Examples
The following macro asks before proceeding. The second button is the default, so pressing Enter does not immediately choose Yes. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and put the procedure in that module. On Mac, the editor shortcut may be Option+F11 or Fn+Option+F11 depending on the keyboard; the VBA editor and menus can vary by Office build.
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, so a backup is useful if you are adapting an example to edit or delete workbook data.
Sub ConfirmImport()
Dim answer As VbMsgBoxResult
' Ask before starting an operation.
answer = MsgBox("Start the import now?", _
vbYesNo Or vbQuestion Or vbDefaultButton2, _
"Confirm import")
If answer = vbYes Then
MsgBox "The import can start.", vbOKOnly Or vbInformation, "Import"
ElseIf answer = vbNo Then
MsgBox "The import was not started.", vbOKOnly, "Import"
End If
End Sub
This example demonstrates a common pattern, but the message saying that an import can start is only a placeholder: put the actual import procedure in the Yes branch if needed. In a vbYesNoCancel prompt, include a separate Cancel branch, even if its only action is to exit the procedure.
For a simple completion notice, keep the dialog shorter and use one button:
Sub ShowCompletion()
Dim message As String
' Build a short status message.
message = "The report is ready." & vbNewLine & _
"Check the output sheet before sharing it."
MsgBox message, vbOKOnly Or vbInformation, "Report status"
End Sub
vbNewLine inserts a line break. A title such as “Report status” helps distinguish the dialog if multiple prompts could appear during a longer process.
Writing Clear Prompts and Avoiding Common Problems
A useful dialog tells the reader what is happening and what each choice means. “Continue?” is vague if the action is not obvious from the surrounding context. A more specific prompt names the action and, when appropriate, the affected item: for example, “Replace the existing report on the Output sheet?” Use Yes and No only when those words have unambiguous consequences.
- Use a title that identifies the task, not a generic title such as “Message.”
- Keep the prompt concise, but include essential consequences or scope.
- Use Cancel for a genuine escape route, and handle it in code.
- Do not show a succession of dialogs when one clear message or a progress indicator would do.
- Use an icon to reinforce meaning, not to communicate information that is missing from the text.
A frequent bug is displaying the right buttons but ignoring the returned value. The dialog then looks interactive, while the macro carries on regardless of the selection. Another is comparing the result to the wrong constant—for instance, testing vbOK after displaying Yes and No buttons. Match each comparison to the button set actually shown.
MsgBox is modal: the user must respond before the procedure continues. That makes it suitable for a confirmation or brief status message, but repeated prompts can interrupt work and make automated processes appear stuck. A dialog also cannot be cancelled by pressing Ctrl+Z in the sense of undoing changes already made by a macro. Design the macro’s safety checks before the action, and provide a clear recovery path where practical.
When to Use an Alternative
Use InputBox when a small amount of text input is enough, such as asking for a short label. Its response is text, and an empty response can be ambiguous because the user may have submitted an empty value or cancelled. Validate the result before using it.
Excel’s Application.InputBox can restrict the type of input and is often more suitable when a macro expects a number, range, or other specific value. For a multi-field entry screen, custom controls, or a workflow with several related decisions, a VBA UserForm is more appropriate. UserForms take more setup but give you control over layout, validation, and interaction. For a message that needs no user response, writing a status to the worksheet or Excel’s status bar may be less disruptive than opening a modal dialog.
Frequently Asked Questions
What is the default VBA MsgBox option?
If you omit the buttons argument, MsgBox uses vbOKOnly: an OK button. The title is also optional; without one, the host application supplies the title.
How do I make a MsgBox show Yes and No?
Pass vbYesNo as the buttons argument, optionally combined with an icon such as vbQuestion. Then test the returned value against vbYes or vbNo.
How do I detect which MsgBox button was clicked?
Assign the function result to a variable, preferably declared as VbMsgBoxResult, and compare it with a return constant such as vbCancel or vbYes.
Can I change the text on MsgBox buttons?
No. The built-in MsgBox uses standard button labels for its selected button set. Use a UserForm if you need custom button text, layout, or more complex interaction.
Can a MsgBox have a Cancel button?
Yes. Choose a set such as vbOKCancel, vbRetryCancel, or vbYesNoCancel, then handle vbCancel explicitly in the procedure.
Related
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA TextBox: Add, Configure, and Use It in FormsAn Excel VBA TextBox is a text-entry control that lets a workbook user type information, such as a…
- VBA Code to Consolidate Excel Files into One WorkbookTo consolidate matching Excel files into one worksheet, place the macro below in the workbook you want to…
- Excel VBA Pivot Table Filter Multiple Values at OnceTo filter a standard Excel PivotTable to several items with VBA, clear the field’s existing filter and set…
- Excel VBA: Delete a Range of Rows by Number or MatchTo delete worksheet rows with Excel VBA, use Rows("3:7").Delete for rows 3 through 7, or qualify the range…
- Excel VBA Block Comments: How to Comment Out Code SafelyExcel VBA has no special multi-line block-comment syntax such as /* … */. To comment out several lines,…
- Google Sheets Macros: How to Create, Run, and Edit ThemYes. You can create macros in Google Sheets using its built-in recorder: in a desktop web browser, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.