To show three buttons in an Excel VBA message box, use a built-in button-set constant such as vbYesNoCancel in the MsgBox function. For example, MsgBox("Continue?", vbYesNoCancel, "Confirm") displays Yes, No, and Cancel; your VBA code then checks the result to decide what happens next.
Excel’s standard MsgBox can display only predefined button combinations, not three buttons with custom labels. The key is to choose the combination that fits the decision, then handle every possible response explicitly.
How the VBA MsgBox button option works
The basic syntax is MsgBox(prompt, buttons, title, helpfile, context). The prompt is the message, buttons controls the button set and optional display features, and title supplies the text in the dialog’s title bar. The help-file and context arguments are optional and are rarely needed in an ordinary workbook macro.
The buttons argument accepts constants combined with the VBA Or operator. One constant selects the button group; other constants can select an icon or the default button. For example, vbYesNoCancel Or vbQuestion Or vbDefaultButton2 requests Yes, No, and Cancel buttons, a question-mark icon, and No as the initially selected button.
Although many simple calls pass these values directly, MsgBox is also a function: it returns a value representing the button the person clicked. Capture that return value if the response should affect the macro. Showing three buttons without checking the result produces a dialog, but does not by itself make the workbook respond meaningfully.
Which three-button combinations are available?
VBA provides two standard button sets with three choices:
vbYesNoCanceldisplays Yes, No, and Cancel. It suits a question where the user can approve, decline, or leave the operation undecided.vbAbortRetryIgnoredisplays Abort, Retry, and Ignore. It can fit a process that has encountered a problem and offers those three specific actions.
Other common constants have fewer than three buttons: vbOKCancel, vbRetryCancel, and vbYesNo display two, while vbOKOnly displays one. There is no built-in vbYesNoRetry set, for example. If the choices you want do not match a standard set, see the section on alternatives below rather than relying on a nonexistent constant.
Pick labels that accurately describe what will happen. Yes and No are appropriate when the prompt asks a clear yes-or-no question; Cancel should mean that the user can stop or back out. Abort, Retry, and Ignore have distinct meanings and should not be repurposed for unrelated actions simply because they provide three buttons.
What each button returns
A three-button dialog returns a VbMsgBoxResult value. For vbYesNoCancel, the choices correspond to vbYes, vbNo, and vbCancel. For vbAbortRetryIgnore, they correspond to vbAbort, vbRetry, and vbIgnore. Compare the result with these named constants instead of typing their numeric values; named constants make code easier to understand and less error-prone.
Use a Select Case statement when each button has its own action. Include a case for every possible result. For instance, if Cancel should leave the workbook unchanged, say so in the code’s logic rather than allowing the macro to continue into the same branch as No. The meaning of each response should be clear both in the prompt and in the actions that follow it.
Example: Yes, No, or Cancel
This example asks whether to create a report. Yes continues with the report action, No exits without creating it, and Cancel also exits while remaining a distinct response. Replace the example comments with the actual work your macro needs to perform. Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.
Sub ConfirmReport()
Dim answer As VbMsgBoxResult
' Ask for a decision; No is the default selection.
answer = MsgBox("Create the report now?", _
vbYesNoCancel Or vbQuestion Or vbDefaultButton2, _
"Confirm report")
Select Case answer
Case vbYes
' Put the report-generation steps here.
MsgBox "The report action was selected.", vbInformation, "Report"
Case vbNo
' No means do not create the report.
MsgBox "No report will be created.", vbInformation, "Report"
Case vbCancel
' Cancel means leave the operation without proceeding.
Exit Sub
End Select
End Sub
To try it in Excel for Microsoft 365 on Windows, save a backup, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and place the procedure in the module. Run it from the editor with F5, or return to Excel and use Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Excel for the web cannot run VBA macros; open the workbook in the desktop application. Excel for Mac also supports VBA, though keyboard shortcuts can depend on the keyboard and system settings; use the Developer tab to open Visual Basic if the Windows shortcut does not apply.
Choosing the default button and icon
The default button is the one initially selected when the dialog opens. For a three-button set, vbDefaultButton1, vbDefaultButton2, and vbDefaultButton3 select the first, second, and third buttons, respectively. A sensible default reduces accidental choices: for a consequential action, defaulting to No or Cancel is often safer than defaulting to Yes. The default changes the initial selection, not the set of responses available.
You can combine a button-set constant with an icon constant. The standard icon options include vbQuestion, vbExclamation, vbInformation, and vbCritical. Choose an icon that reinforces the message instead of using one as decoration. For example, a confirmation question can use vbQuestion, while a serious error can use vbCritical. A message box is not a substitute for explaining the consequence in the prompt.
Use Or to combine the constants, as in vbYesNoCancel Or vbQuestion Or vbDefaultButton2. Avoid adding unrelated constants or relying on raw numbers. Named options communicate the intent and make later edits safer.
Can the buttons have custom labels?
No. The standard VBA MsgBox does not let you rename its buttons, change their order, or provide a custom three-button combination. A prompt such as “Save changes?” can use Yes, No, and Cancel, but it cannot turn those captions into Save, Discard, and Keep Editing through a MsgBox argument.
If exact wording matters, create a UserForm with three CommandButton controls. Set each button’s caption to the action it performs, then write event procedures for the clicks. A UserForm takes more setup than a message box, but it supports custom labels and more control over layout and validation. For a reusable workbook tool, that extra work can make the choices clearer; for a brief, conventional confirmation, a built-in message box is usually simpler.
Do not imitate custom buttons by showing multiple message boxes in sequence. That makes the decision slower and can confuse users about which answer applies. If the choices are not represented accurately by one of the standard sets, present them together in a UserForm or another suitable interface.
Common mistakes and troubleshooting
- Using the wrong constant:
vbYesNocreates two buttons, not three. UsevbYesNoCancelorvbAbortRetryIgnorefor the standard three-choice sets. - Ignoring the return value: If the code calls
MsgBoxwithout assigning its result, it cannot branch on the user’s choice. Store the result in a variable declared asVbMsgBoxResult. - Leaving a result unhandled: Add a
Casefor each response. A Cancel response should not accidentally continue into a destructive or time-consuming operation. - Using unclear wording: Ask one direct question and make the consequences of Yes and No understandable. If Cancel means something different from No, explain that distinction when it matters.
- Expecting the dialog in Excel for the web: VBA macros do not run in the browser version of Excel. Open the file in desktop Excel to use VBA.
- Macros are blocked: A dialog will not appear if the procedure never runs. Check that the file is macro-enabled when needed, that macros are permitted by the organization’s security settings, and that you started the correct procedure.
Another common misconception is that pressing Esc is a separate fourth answer. The dialog has only the buttons in its selected set; keyboard behavior can dismiss or activate a dialog choice according to the interface and context, but it does not create a new return constant. Write the macro around the documented button results, not an assumed extra option.
Safety and practical design
A confirmation box helps prevent mistakes only if the message describes the operation and the code respects the answer. Before a macro deletes rows, overwrites files, or changes many cells, make the prompt specific—for example, name the worksheet or explain the scope—and make Cancel exit before the change begins. Do not treat a message box as a substitute for a backup, a review step, or careful testing.
Also avoid asking for confirmation at every minor step. Repeated dialogs interrupt work and can train people to click through without reading. One well-timed question before a consequential action is generally more useful than several vague warnings. For macros shared with others, use clear wording and a safe default, and explain what will happen after each choice.
Frequently Asked Questions
How do I make a VBA MsgBox show Yes, No, and Cancel?
Pass vbYesNoCancel as the button option, such as MsgBox("Continue?", vbYesNoCancel, "Confirm"). Assign the return value to a variable if the macro needs to respond differently to each button.
What is the VBA constant for three buttons?
There are two standard three-button constants: vbYesNoCancel and vbAbortRetryIgnore. Choose the one whose labels accurately describe the decision you are asking the user to make.
How do I check which MsgBox button was clicked?
Store the function result in a variable declared as VbMsgBoxResult, then compare it in a Select Case statement with vbYes, vbNo, and vbCancel, or the corresponding Abort, Retry, and Ignore constants.
Can I change Yes, No, and Cancel to custom button names?
Not with the built-in MsgBox. Use a VBA UserForm with CommandButton controls if the buttons need captions such as Save, Discard, or Keep Editing.
Can a MsgBox macro run in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel for Windows or Mac and run the macro there.
Related
- Excel Table Data Sorting: Steps, Options, and Fixes
- Excel Spreadsheet Not Responding? Causes and Fixes
- Excel Suddenly Not Responding? 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 VBA: Find the Row Number of a Matching ValueIn Excel VBA, use the Range.Find method to locate a value, then read the matching cell’s .Row property.…
- Excel VBA Label Not Defined: Fix the Compile ErrorExcel VBA’s Label not defined compile error means a procedure refers to a jump target that VBA cannot…
- Excel VBA: How to Break Out of an Endless LoopTo stop a running Excel VBA loop on Windows, press Ctrl+Break (or Ctrl+Pause on keyboards with that key),…
- Excel VBA: Find the Last Used Row in Any WorksheetTo find the last row containing content anywhere on a worksheet in Excel VBA, use Range.Find with SearchOrder:=xlByRows…
- VBA For Loop Negative: How Step -1 WorksYes. A VBA For loop can count downward by using a negative Step, such as For i =…
- VBA Get Character From String: Mid, Loops & TipsTo get one character from a VBA string by position, use Mid$(text, position, 1). For example, Mid$("Excel VBA",…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.