VBA MsgBox constants control which buttons and icon appear in a message box, which button is selected by default, and how the box behaves. Pass the relevant constants in the buttons argument of MsgBox, combining compatible options with Or; then use the function’s return value to respond to the user’s choice.
What VBA MsgBox constants do
In VBA, MsgBox displays a message and, when used as a function, returns a number identifying the button the user clicked. Its basic syntax is MsgBox(prompt, buttons, title, helpfile, context). The prompt is required; the other arguments are optional. The buttons argument accepts predefined constants from VBA’s VbMsgBoxStyle enumeration.
These named constants make code easier to read than unexplained numbers. For example, vbYesNo Or vbQuestion says that the dialog has Yes and No buttons and a question icon. The equivalent numeric value is less clear to someone maintaining the macro. Constants are built into VBA, so you do not need to define them or add a reference.
The styles are grouped by purpose: button sets, icons, default-button selection, and behavior or layout options. Many can be combined because their values occupy separate bit positions. Use Or to combine them; constants with a value of zero simply leave the corresponding choice at its default.
Button-set constants
Choose one button-set constant to specify the available responses. These values describe the buttons shown, not the value returned when a button is clicked:
vbOKOnly= 0: show OK.vbOKCancel= 1: show OK and Cancel.vbAbortRetryIgnore= 2: show Abort, Retry, and Ignore.vbYesNoCancel= 3: show Yes, No, and Cancel.vbYesNo= 4: show Yes and No.vbRetryCancel= 5: show Retry and Cancel.
If you omit a button-set constant, the dialog uses vbOKOnly. For example, MsgBox "Import complete" shows an OK button. Add a button set when the macro needs a decision, but choose labels that match what the code can actually do. A Yes/No prompt is not useful if the macro treats both answers identically.
Icon constants
An icon gives visual context; it does not change what the buttons do or determine whether an action is allowed. The common icon constants and their values are:
vbCritical= 16: critical-message icon.vbQuestion= 32: question icon.vbExclamation= 48: exclamation or warning icon.vbInformation= 64: information icon.
Combine one icon with a button set, such as vbOKOnly Or vbInformation for a completion notice. An icon is optional, and a dialog without one is often sufficient for routine status messages. Avoid using a warning icon for a harmless update: the visual emphasis should reflect the consequence of the message. The exact appearance can vary with the operating system and Office environment.
Default buttons and dialog behavior
The default-button constants select which button receives the initial keyboard focus. They are useful when pressing Enter should lead to a deliberate, safe choice. The values are vbDefaultButton1 = 0, vbDefaultButton2 = 256, vbDefaultButton3 = 512, and vbDefaultButton4 = 768. The number refers to the button’s position in the dialog, from left to right as displayed, not to the button’s meaning. A default-button option is relevant only if that position exists.
Two modality constants are vbApplicationModal = 0 and vbSystemModal = 4096. Application-modal is the default: the user must dismiss the message box before continuing to interact with that application. System-modal asks for a more disruptive dialog that takes precedence over other work. It is rarely a good choice for ordinary workbook prompts because it can interrupt users outside Excel; use it only when the situation genuinely warrants that behavior.
Other documented options include vbMsgBoxHelpButton = 16384, vbMsgBoxSetForeground = 65536, vbMsgBoxRight = 524288, and vbMsgBoxRtlReading = 1048576. They add a Help button, request foreground display, align text to the right, or request right-to-left reading, respectively. If you request a Help button, provide the applicable help file and context arguments as well. Windows, Office, and other host environments can affect the visible result, so check layout-dependent prompts on the platforms where the workbook will be used.
Return-value constants: what the user clicked
When you assign the result of MsgBox to a variable, compare it with the response constants. These are different from the style constants used to create buttons:
vbOK= 1vbCancel= 2vbAbort= 3vbRetry= 4vbIgnore= 5vbYes= 6vbNo= 7
For example, vbYesNo configures a pair of buttons, while vbYes represents the result of clicking Yes. A common mistake is to test the return value against vbYesNo; that constant describes the style and is not the response code. Use the response constant that matches the button the user chose.
A practical confirmation example
This example asks whether to continue and makes No the default, so an accidental Enter key does not select Yes. It checks each possible response explicitly. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.
Sub ConfirmImport()
Dim answer As VbMsgBoxResult
' Ask before starting the import.
answer = MsgBox("Start the import?", _
vbYesNo Or vbQuestion Or vbDefaultButton2, _
"Confirm import")
Select Case answer
Case vbYes
MsgBox "The import can start now.", vbOKOnly Or vbInformation
Case vbNo
MsgBox "The import was not started.", vbOKOnly Or vbInformation
End Select
End Sub
The first dialog uses the second button as its default; with Yes and No buttons, that is No. The result is stored as VbMsgBoxResult, which makes the intended type clear, and Select Case compares it with response constants. The sample reports the decision rather than performing an import. Replace that branch with the real operation only after checking what the macro changes and whether the user has the required information.
How to choose a useful combination
Start with the decision the user must make, then select the smallest button set that represents it. Add an icon only if it clarifies the situation, and choose a default that reduces the risk of an unintended action. A short, specific title helps distinguish the dialog from other windows.
- Use
vbOKOnlyfor a notice that requires acknowledgment but no choice. - Use
vbYesNowhen the only meaningful outcomes are proceed or do not proceed. - Use
vbYesNoCancelwhen Cancel has a distinct meaning, such as closing a prompt without making a decision. - Use
vbRetryCancelwhen an operation may be attempted again or abandoned. - Set a non-destructive default for prompts about deleting, overwriting, or submitting data.
Keep the prompt concrete. “Overwrite the existing report?” is clearer than “Continue?” If a choice could cause a significant change, describe the target or consequence. Also avoid asking a MsgBox to collect text: it only offers predefined buttons. Use an InputBox or a UserForm when the user needs to enter data or make a more detailed selection.
Common mistakes and limitations
One frequent error is adding constants that do not belong together, such as multiple button sets or multiple icons. Choose one from each relevant category. Although older examples sometimes add numeric values with +, using Or communicates that the styles are being combined as flags and is easier to maintain.
Another mistake is expecting a message box to pause only one procedure while leaving the same application fully interactive. A standard MsgBox is modal, so the user must dismiss it before continuing in that application. If the workflow needs a persistent panel, several controls, or interaction with the workbook while the interface remains open, a UserForm is generally a better fit. A modeless UserForm can support continued interaction, but it requires more design and code than a MsgBox.
Finally, a message box does not validate the operation or reverse changes. Its button result only tells the macro which response was selected. The macro still needs to handle that response correctly, and any destructive or lengthy operation should have appropriate checks. On Excel for Mac, VBA and MsgBox are available, but platform-specific dialog appearance or focus behavior should not be assumed to match Windows exactly. Test important prompts on the intended platform.
Frequently Asked Questions
What is the constant for an OK and Cancel MsgBox?
Use vbOKCancel in the buttons argument. To include an icon, combine it with one icon constant, for example vbOKCancel Or vbExclamation.
What does vbYesNo return?vbYesNo specifies which buttons to display; it is not the value returned after a click. Check the result against vbYes or vbNo.
How do I make No the default MsgBox button?
Place vbDefaultButton2 in the buttons argument when No is the second displayed button. Verify the button order if you use a different set or host environment.
Can I combine MsgBox constants?
Yes. Combine compatible style options, such as one button set, one icon, and a default-button choice, with Or. Do not combine competing button sets or icons.
Can a VBA MsgBox have a custom button label?
No. MsgBox uses its predefined button labels. For custom text, extra choices, or data entry, build a UserForm or use another suitable interface rather than trying to change the built-in labels.
Related
- Data Validation Multiple Selection in Excel: What Works
- Data Validation Interview Questions and Strong Answers
- Data Validation Greyed Out in Excel? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Recorder Key: Start, Stop and Assign ShortcutsIn Excel for Microsoft 365 on Windows, there is no default keyboard shortcut that starts the macro recorder.…
- Can You Have Macros in Google Sheets? How They WorkYes. Google Sheets supports macros: you can record a sequence of actions and replay it later, or edit…
- Excel VBA Range Using Cells: Syntax, Examples, and TipsIn Excel VBA, use Cells(row, column) to identify a cell by its row and column numbers, and pass…
- Excel VBA: Open Multiple Files in a Folder with a LoopTo open every Excel workbook in a folder, use VBA’s Dir function to find each matching filename, then…
- Loop Through a Named Range in VBA: Practical ExamplesTo loop through every cell in an Excel named range in VBA, assign the range to a Range…
- VBA Sort: How to Sort Excel Data Safely with a VBA MacroVBA does not have a standalone Sort function for worksheet data; in Excel macros, sorting is usually done…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.