In VBA, MsgBox returns a number identifying the button the user clicked: vbOK is 1, vbCancel is 2, vbYes is 6, and vbNo is 7. Capture that result in a variable and compare it with the named VBA constants, rather than relying on numeric values in your code.
What a MsgBox return value means
MsgBox is a function: it displays a dialog and returns a value after the user responds. The return value is a member of the VbMsgBoxResult enumeration. It tells your procedure which response was chosen; it does not return the dialog’s message, title, or button configuration.
The standard return values are:
vbOK= 1vbCancel= 2vbAbort= 3vbRetry= 4vbIgnore= 5vbYes= 6vbNo= 7
Only responses represented by buttons in the particular dialog are normally possible. For example, a message box configured with Yes and No buttons returns either vbYes or vbNo. It does not return vbOK, because there is no OK button. The numeric values are stable VBA constants, but the constant names make the meaning much easier to read.
Button options are not return values
A frequent source of confusion is that the Buttons argument and the function result are two different things. The argument tells VBA which buttons and visual options to display. The result tells your code which response the user selected. Some argument values happen to overlap numerically with result values, but their meanings depend on where they are used.
For example, vbYesNo has the value 4 and configures a Yes/No dialog. If the user clicks Yes, the function returns vbYes, whose value is 6. Comparing the result with vbYesNo is therefore a mistake: that constant describes a button layout, not a possible answer.
The first argument is the prompt. The optional second argument combines button layout, icon, default-button, and modality constants. The title is an optional third argument. You can combine applicable options with the + operator, such as vbYesNo + vbQuestion. Keep the return-value constants separate from those display settings.
Capture and test the response
Declare the response variable as VbMsgBoxResult to express what it contains. A Select Case statement is useful when several responses need different actions, while an If statement works well for a simple Yes/No decision.
This Excel VBA example gives the user three choices. The branches are deliberately left as comments so you can put the appropriate workbook action in each one.
Sub AskBeforeContinuing()
Dim answer As VbMsgBoxResult
' Ask for a decision; Yes is the initial default.
answer = MsgBox("Save changes before continuing?", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, _
"Confirm")
Select Case answer
Case vbYes
' Add the save action here.
Case vbNo
' Continue without saving here.
Case vbCancel
' Stop the operation here.
End Select
End Sub
Before running a macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, so a backup is important if you add code that changes cells, sheets, or files. In the example, choosing Cancel is distinct from choosing No: Cancel can mean “stop this operation,” while No can mean “continue without saving.”
For a basic Yes/No choice, a shorter test is possible:
Sub ConfirmAction()
Dim answer As VbMsgBoxResult
' Continue only when the user chooses Yes.
answer = MsgBox("Run the update?", vbYesNo + vbQuestion, "Confirm")
If answer = vbYes Then
' Add the update action here.
End If
End Sub
Using a named constant makes the intention clear and avoids a “magic number” such as 6. It also makes later edits safer: if you change the dialog from Yes/No to Yes/No/Cancel, the branch comparisons remain understandable.
Choose buttons and a default deliberately
Pick buttons that accurately describe the available choices. Common layouts include vbOKOnly, vbOKCancel, vbYesNo, vbYesNoCancel, and vbRetryCancel. A destructive or irreversible action should not be phrased as though the user has only a vague “OK” choice if the actual decision is whether to proceed.
When a dialog has more than one button, a default-button constant determines which button is initially selected. The available choices include vbDefaultButton1, vbDefaultButton2, vbDefaultButton3, and vbDefaultButton4. For example, vbYesNo + vbDefaultButton2 makes the second button, No, the default. This can reduce accidental confirmation, but it does not replace clear wording or a genuine Cancel path where one is needed.
Other common display constants include vbInformation, vbQuestion, vbExclamation, and vbCritical for the icon, and vbApplicationModal or vbSystemModal for modality. In ordinary VBA use, an application-modal message box prevents interaction with the application until it is dismissed. System-modal dialogs can affect interaction with other applications too, so they are rarely appropriate for routine workbook prompts. An icon is only a visual cue; it does not change the return value.
Cancel, Escape, and closing the dialog
If the user can cancel an operation, include a Cancel choice when it fits the task and handle vbCancel explicitly. Do not treat Cancel as equivalent to No unless that is genuinely the intended behavior. For example, No might mean “continue without saving,” whereas Cancel might mean “return to the worksheet without continuing.”
With a Cancel button available, pressing Escape is a way to choose Cancel in the standard message-box interaction. A response branch should still be based on the returned constant, not on an assumption about which key the user pressed. If your workflow depends on what happens when someone closes a dialog using its window controls, test that behavior with the button layout and Office environment you support, and make sure the action is safe if the operation is stopped.
Also remember that MsgBox pauses the calling procedure while it waits for a response. It is appropriate for a brief prompt or notification, not for collecting several fields of information or presenting a complex workflow. A message box can interrupt the user, so use it when a response is actually needed rather than for every routine update.
Common mistakes and troubleshooting
- Comparing against the layout constant: Test for
vbYes, notvbYesNo, after showing a Yes/No prompt. - Forgetting to store the result: If your code needs to branch on the choice, assign the function result to a variable. Simply displaying a message does not give a later
Ifstatement access to the user’s selection. - Using the wrong variable type:
VbMsgBoxResultcommunicates that the value is a message-box response. A numeric type can hold the value, but it is less descriptive. - Ignoring a possible Cancel response: If Cancel is on the dialog, add a branch for it. Otherwise, the procedure may continue in a way the user did not intend.
- Assuming the icon controls the answer:
vbQuestionorvbExclamationchanges the presentation, not the set of return values.
If a branch never runs, inspect the exact constants passed to MsgBox and the constant used in the comparison. A quick debugging technique is to temporarily display the numeric result in a separate message box, but remove that diagnostic prompt once the issue is resolved. Prefer named constants in final code so the comparison remains self-explanatory.
When to use something other than MsgBox
Use MsgBox when the user needs to acknowledge a message or make a small, immediate choice. If you need text or another input value, VBA’s InputBox function is designed to collect a response, although it is limited compared with a custom form. In Excel, Application.InputBox offers a Type argument that can restrict the kind of input; its Cancel behavior and return type should be handled according to the chosen type.
For multiple fields, validation, dynamic choices, or a workflow that should remain available while the user works, use a UserForm instead. A UserForm lets you create controls and decide exactly how its buttons affect the macro. That requires more setup than a one-line MsgBox, but it is easier to use when a prompt has become a small application interface.
Frequently Asked Questions
What does MsgBox return for Yes?
It returns vbYes, whose numeric value is 6. Compare the result with vbYes rather than writing the number directly.
What is the return value for No?vbNo is the return constant for the No button, and its value is 7. It is available only when the displayed button layout includes No.
What does MsgBox return when I click Cancel?
It returns vbCancel, with a numeric value of 2. Capture and handle it if Cancel is offered in the dialog.
Can I store a MsgBox result in an Integer?
Yes, these results are small integer values, so an Integer can hold them. VbMsgBoxResult is usually clearer because it names the intended kind of value.
Is vbYesNo the same as vbYes?
No. vbYesNo configures the dialog to show Yes and No buttons; vbYes is the result returned when the user chooses Yes.
How do I make No the default button?
For a Yes/No message box, add vbDefaultButton2 to the button options. The second button is No in that layout, and the result is still vbNo if the user selects it.
Related
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA AutoFilter by Color: Filter Fill and Font ColorsYes. In Excel VBA, use the AutoFilter method with Operator:=xlFilterCellColor to filter by a cell’s fill color, or…
- VBA Outlook Create Contact Group from ExcelYes. In classic Outlook for Windows, an Excel VBA macro can read email addresses from a worksheet, create…
- Excel VBA Arrays vs Collections vs DictionariesIn Excel VBA, use an array for a compact, indexed set of values, a Collection for an ordered…
- VBA Assign Range to a Variable: Examples and TipsTo assign an Excel range to a VBA variable, declare the variable as a Range and use Set:…
- Excel VBA COUNTA: Count Nonblank Cells in MacrosIn Excel VBA, count nonblank cells with Application.WorksheetFunction.CountA and pass it a range. For example, Application.WorksheetFunction.CountA(Worksheets("Data").Range("A2:A100")) returns the…
- VBA Sort Cells in Excel: Code, Options, and Common FixesTo sort cells with VBA in Excel, sort the entire data range by a key column—not just one…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.