In VBA, MsgBox displays a dialog and can return the button the user clicked. Its arguments are Prompt, optional Buttons, optional Title, and optional HelpFile and Context; the buttons argument controls the available choices, icon, default button, and dialog behavior.
What the MsgBox arguments do
The general syntax is MsgBox(prompt, [buttons], [title], [helpfile, context]). The square brackets mean an argument is optional; they are not typed into the VBA code. The first argument, Prompt, is the text the dialog displays. It is required and can be a string expression, such as a literal message or text assembled from variables.
Prompt: Required message text. UsevbCrLfto put part of a message on a new line.Buttons: Optional numeric value or combination of constants that sets the buttons, icon, default button, and modality.Title: Optional text displayed in the dialog’s title bar. If omitted, VBA uses the application name, such as Microsoft Excel.HelpFile: Optional path or name of a help file associated with the dialog.Context: Optional numeric help context identifier for the relevant topic in that help file.
Arguments are positional. If you want to supply a title but leave Buttons at its default, include the second argument explicitly: MsgBox "Saved.", vbOKOnly, "Workbook status". You cannot skip the middle position by writing a title as the second argument, because VBA would treat it as the buttons value.
Choosing buttons and icons
The Buttons argument is usually a combination of named VBA constants. With no buttons argument, the dialog uses vbOKOnly. The main button-layout constants are:
vbOKOnly: OK.vbOKCancel: OK and Cancel.vbAbortRetryIgnore: Abort, Retry, and Ignore.vbYesNoCancel: Yes, No, and Cancel.vbYesNo: Yes and No.vbRetryCancel: Retry and Cancel.vbCancelTryContinue: Cancel, Try Again, and Continue.
Icon constants include vbCritical, vbQuestion, vbExclamation, and vbInformation. Choose an icon that fits the message rather than using one as decoration: an information icon suits a completion notice, while a critical icon signals a serious problem. For example, vbYesNo + vbQuestion combines a Yes/No layout with a question icon.
Constants in this argument represent separate options encoded in a number, so they can be combined with + or the VBA bitwise operator Or. Named constants are easier to understand and maintain than unexplained numeric values. The dialog’s buttons and icon do not change what your macro does; your code must inspect the returned value and take the appropriate action.
Default buttons, modality, and less common options
You can select which button is initially focused with vbDefaultButton1, vbDefaultButton2, vbDefaultButton3, or vbDefaultButton4. For example, a Yes/No dialog can make No the default by adding vbDefaultButton2. This is useful when the safer or less destructive choice should be selected if a user presses Enter without checking the options carefully. The requested default must correspond to a button that actually exists in the chosen layout.
By default, a MsgBox is application-modal: the user must respond to it before continuing to work in that application. vbApplicationModal expresses that default, while vbSystemModal requests a system-modal dialog that blocks interaction with other applications too. System-modal dialogs can be disruptive and are rarely appropriate for ordinary workbook messages.
Other options include vbMsgBoxHelpButton for a Help button, vbMsgBoxSetForeground to bring the dialog to the foreground, and vbMsgBoxRight or vbMsgBoxRtlReading for text alignment and right-to-left reading. Help-file support uses the final arguments to link the dialog to help content; Context identifies a topic within the specified help file. These options are generally unnecessary for a simple confirmation or status message.
Reading the value MsgBox returns
MsgBox is a function, so it can return a VbMsgBoxResult value indicating which button the user chose. Common results are vbOK, vbCancel, vbYes, vbNo, vbRetry, vbAbort, and vbIgnore. The Try Again/Continue layout can return vbTryAgain or vbContinue. These are return-value constants, distinct from layout constants such as vbYesNo.
Store the result when the choice affects what happens next. A message with Yes and No returns vbYes or vbNo; it does not return the text of the button. Comparing against the named constant makes the code readable and avoids relying on the underlying numeric values.
Example: ask before continuing
Before running a macro that changes workbook data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a confirmation dialog is not a substitute for a backup. This example uses a question icon, a Yes/No layout, and No as the default response:
Sub ConfirmUpdate()
Dim answer As VbMsgBoxResult
' Ask before making changes.
answer = MsgBox("Continue with the update?", _
vbYesNo + vbQuestion + vbDefaultButton2, _
"Confirm update")
If answer = vbYes Then
MsgBox "The update can continue.", vbInformation, "Status"
Else
MsgBox "No update was started.", vbInformation, "Status"
End If
End Sub
The first call’s return value is saved in a variable declared as VbMsgBoxResult. The If statement then branches on the choice. In this example, every response other than Yes follows the safe exit path. For a Yes/No dialog, that means No; if you use a layout with Cancel, handle Cancel deliberately as a separate result.
Example: handle Cancel separately
When a Cancel button has a distinct meaning, test for it explicitly. This example uses the OK/Cancel layout and exits the procedure if the user cancels:
Sub CheckBeforeExport()
Dim answer As VbMsgBoxResult
' Ask whether to proceed with the export.
answer = MsgBox("Export the selected records?", _
vbOKCancel + vbQuestion, _
"Confirm export")
If answer = vbCancel Then
Exit Sub
End If
MsgBox "The export step can now run.", vbInformation, "Ready"
End Sub
The example indicates where export logic would go; displaying the confirmation does not itself export anything. Keep the prompt specific about the action and its consequences, especially if the macro will overwrite, delete, or send data.
Formatting useful messages
A prompt can contain more than one line. For instance, use vbCrLf between a summary and a question: "This will replace the current report." & vbCrLf & "Continue?". You can also build a prompt from a variable, such as a selected worksheet name. Keep messages concise; a MsgBox is best for a short notice or a decision with a small number of choices, not a long explanation or a large data preview.
Choose labels that make the consequences clear. A generic Yes/No question such as “Are you sure?” gives little context. “Delete the 12 selected rows?” makes the action more understandable. If the user needs to compare information, edit a value, or make a complex choice, a worksheet interface or UserForm is usually more suitable than several chained MsgBox dialogs.
Common problems and misconceptions
- Putting the title in the wrong position: The order is prompt, buttons, title, help file, context. Use
vbOKOnlyas the second argument if you need to set a title without customizing the buttons. - Confusing the button layout with the result:
vbYesNoconfigures the available choices;vbYesandvbNoidentify the user’s response. - Showing a choice but ignoring it: If you call
MsgBoxwithout assigning its result, the dialog still appears, but your code cannot branch on the response later. - Assuming the dialog can be undone: Closing the dialog does not reverse macro actions already performed. Ctrl+Z generally cannot undo VBA changes.
- Expecting it to work as a worksheet formula:
MsgBoxis a VBA function, not a formula for a worksheet cell. It runs from VBA code, such as a macro, and cannot be used as a normal cell formula.
Excel for the web does not run VBA macros. In the desktop edition of Excel for Microsoft 365 on Windows, VBA code is commonly opened in the Visual Basic Editor. The core MsgBox syntax and constants are VBA features, but dialog appearance can vary with the operating system and Office environment. A MsgBox is also modal, so code pauses at the call until the user responds.
When to use MsgBox instead of other options
Use MsgBox for a brief confirmation, a simple warning, or a status message that needs no input beyond a button click. It is quick to add and does not require designing a separate form. For a persistent notice, a worksheet cell or status bar may be less interruptive. For several fields, validation, or a richer workflow, a VBA UserForm provides more control over layout and interaction.
For messages that only report an error during development, VBA’s Debug.Print can send text to the Immediate window in the Visual Basic Editor without interrupting the user. It is not a replacement for a user-facing warning in a finished workbook, but it can be useful when diagnosing code.
Frequently Asked Questions
What is the required argument for VBA MsgBox?
The required argument is Prompt, the message text. Buttons, title, help file, and help context are optional.
How do I add a title to a VBA MsgBox?
Put the title third: MsgBox "Saved.", vbOKOnly, "Workbook status". The second position is the buttons argument, even when you use its default layout.
How do I know whether the user clicked Yes or No?
Assign the function result to a variable, then compare it with vbYes or vbNo. For example, a vbYesNo dialog returns one of those two result constants.
Can I combine an icon and buttons in one argument?
Yes. Combine the relevant constants, such as vbYesNo + vbQuestion. You can add a default-button constant as well, provided that button exists in the selected layout.
Can MsgBox show multiple lines?
Yes. Concatenate text with vbCrLf to insert a line break. Keep the prompt short enough to read comfortably in a dialog.
Can I use MsgBox in an Excel cell?
No. It is a VBA function for code, not a worksheet function. A macro can display it while running, but Excel for the web does not execute VBA macros.
Related
- Excel Data Validation Rules: How They Work and When to Use Them
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Show a UserForm When the Workbook OpensTo show an Excel VBA UserForm when a workbook opens, put a Workbook_Open event procedure in the ThisWorkbook…
- Excel VBA Resize: Change a Range’s Size with VBA CodeIn Excel VBA, use the Resize property to refer to a range with a different number of rows…
- VBA Loop Without Do: Choosing For, While, or For EachYes. In VBA, you can loop without using Do: use For...Next for a known number of repetitions, For…
- Macro Recorder for Mac: Options and How to ChooseYes—Mac users can record and replay repetitive actions, but macOS does not provide one universal recorder that captures…
- Named Range in VBA: Create, Read, and Manage Excel NamesIn Excel VBA, a named range is a defined name that refers to a cell, a range of…
- Access VBA: Open an Excel File and Work With Its DataYes. Access VBA can open an Excel workbook by controlling the Excel application, or it can import worksheet…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.