Yes. In VBA, use the buttons argument of MsgBox to show a built-in group such as Yes/No or Yes/No/Cancel, then check the value the function returns to find out which button was clicked. The button labels and combinations are predefined; for custom labels or a different layout, use a UserForm instead.
How multiple buttons work in MsgBox
MsgBox is a built-in VBA function that displays a message and waits for the user to respond. Its basic syntax is MsgBox(prompt, buttons, title). The prompt is the message, buttons specifies the button group and optional display settings, and title supplies the dialog-box title. The last two arguments are optional, but the prompt is required.
For example, MsgBox("Continue?", vbYesNo, "Confirm") displays a prompt with Yes and No buttons. To make the result useful, assign the function’s return value to a variable and branch on it. Merely displaying a dialog does not automatically make Excel perform an action; your code must decide what to do after the user responds.
The dialog is modal: while it is open, the user must respond to it before continuing with the code that called it. This is useful for a short confirmation or alert, but it is not a substitute for a form when the user needs to enter information or make several choices.
Button groups VBA provides
The buttons argument accepts predefined constants. These are the standard button groups:
vbOKOnly: one OK button. This is the default if you omit the buttons argument.vbOKCancel: OK and Cancel.vbAbortRetryIgnore: Abort, Retry, and Ignore.vbYesNoCancel: Yes, No, and Cancel.vbYesNo: Yes and No.vbRetryCancel: Retry and Cancel.
These choices cover common decisions, but VBA does not provide an argument for replacing a button’s caption. You cannot, for example, use MsgBox to create buttons named “Save,” “Discard,” and “Review.” Nor can you choose an arbitrary number of buttons or arrange them in a custom order. Choose the closest standard group and make the prompt clear about what each response means.
A Yes/No example with multiple buttons
The following Excel VBA procedure asks whether to continue and branches according to the selected button. Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z. On Windows, open the VBA editor with Alt+F11, insert a standard module using Insert > Module, and paste the code there.
Sub AskWhetherToContinue()
Dim answer As VbMsgBoxResult
' Ask the user to choose Yes or No.
answer = MsgBox("Continue with the next step?", _
vbYesNo + vbQuestion, "Confirm")
' Respond to the selected button.
Select Case answer
Case vbYes
MsgBox "Continuing.", vbInformation, "Status"
Case vbNo
MsgBox "The next step was not started.", _
vbInformation, "Status"
End Select
End Sub
Here, vbYesNo selects the buttons and vbQuestion adds a question icon. The plus signs combine those settings in one argument. The first dialog returns either vbYes or vbNo, so the Select Case statement can handle both outcomes. The second dialog is just an informational message; it does not change the original response.
Check the return value, not the button text
A common source of bugs is showing several buttons but not checking which one was clicked. MsgBox returns a numeric value represented by a VBA constant. Use the named constants rather than unexplained numbers; the names make the intent much easier to read.
vbOKreturns 1.vbCancelreturns 2.vbAbortreturns 3.vbRetryreturns 4.vbIgnorereturns 5.vbYesreturns 6, andvbNoreturns 7.
The result is a VbMsgBoxResult, so declaring the receiving variable with that type communicates what it contains. If the chosen button group includes Cancel, include a Case vbCancel branch when cancellation needs distinct handling. Do not assume Cancel means the same thing as No: one may mean “stop and leave everything unchanged,” while the other may be a deliberate answer to a question.
For example, a Yes/No/Cancel dialog has three possible results. A procedure that handles only Yes and No may leave the Cancel outcome unclear. Add an explicit Cancel case, or structure the procedure so that no action occurs unless the user chooses the one response that authorizes it.
Add an icon or choose the default button
The same argument can combine a button group with an icon or a default-button setting. Common icon constants include vbInformation, vbExclamation, vbCritical, and vbQuestion. These affect the dialog’s appearance; they do not change the choices or the return values. Select an icon that fits the message rather than using one as a substitute for clear wording.
Default-button constants let you specify which button is initially selected. vbDefaultButton1 selects the first button, vbDefaultButton2 the second, and so on. For example, with a Yes/No group, the second button is No. Setting No as the default can reduce accidental confirmation for a consequential action. It does not prevent the user from choosing Yes.
Combine constants with + or the VBA Or operator. A typical expression is vbYesNo + vbQuestion + vbDefaultButton2. Keep the expression readable, and ensure the default-button number is valid for the group being displayed. A default-button setting does not add another button.
Choose a dialog that fits the decision
Use MsgBox when the user needs to acknowledge a message or choose among a small set of predefined responses. The standard groups are quick to implement and familiar, and they return a simple value that code can test. They work well for confirmations, warnings, and short status messages.
Use a VBA UserForm when the interaction needs controls or labels that MsgBox cannot provide. A UserForm can offer custom button captions, text fields, check boxes, lists, and a tailored layout. It takes more setup: you create the form and controls in the VBA editor and write code to handle their events. That additional work is worthwhile if the choices are not accurately represented by Yes, No, OK, Cancel, or the other built-in labels.
Do not use an obscure button group just because it provides the desired number of buttons. “Abort, Retry, Ignore” has specific implications and may confuse someone who is simply choosing among unrelated actions. If the standard wording would mislead, build a form with accurate captions instead.
Common problems and practical safeguards
- Using the wrong argument position: the button group belongs in the second argument, after the prompt. Add a title as the third argument if needed.
- Comparing against a number without context: prefer
vbYes,vbNo, orvbCancelto numeric values such as 6 or 7. - Ignoring Cancel: if the selected group offers Cancel, decide explicitly what should happen when it is chosen.
- Assuming captions are customizable: the standard buttons have fixed captions; use a UserForm for custom wording.
- Making a risky choice the default: choose the initial selection carefully, especially if the dialog authorizes deleting, overwriting, or sending data.
- Using a vague prompt: say what will happen after each choice. “Proceed?” is less informative than “Send the report now?”
A dialog is only a prompt, not a security control. If a macro can delete or overwrite important information, use appropriate checks in the code as well; do not rely on a user noticing the message. A macro that changes workbook contents also cannot be rolled back with Excel’s normal Undo command, which is why saving a copy before running unfamiliar VBA is a sensible precaution.
Excel on Windows and Mac
The examples use standard VBA syntax and constants available in Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA and the MsgBox function, although the dialog’s appearance can follow the host operating system. Keep the code focused on the returned constants rather than visual details, and avoid assuming that a dialog will look pixel-for-pixel identical across platforms.
On Windows, Alt+F11 opens the VBA editor. On a Mac, the VBA editor can be opened from Tools > Macro > Visual Basic Editor in Excel; keyboard shortcuts can vary with the Mac keyboard and Excel settings. Macro availability and security settings can also affect whether code runs. The message box itself does not enable macros or bypass workbook security.
Frequently Asked Questions
Can a VBA MsgBox have three buttons?
Yes. Use a built-in three-button group such as vbYesNoCancel or vbAbortRetryIgnore. Capture the return value and handle each possible response in your code.
Can I change the MsgBox button names?
No. The built-in button captions are fixed. Create a UserForm if you need labels such as “Keep,” “Replace,” or “Skip,” or need a custom number of choices.
How do I know which MsgBox button was clicked?
Assign the function result to a variable, then compare it with the matching constant, such as vbYes, vbNo, or vbCancel. A Select Case statement is a clear way to handle several outcomes.
How do I make No the default button?
For a Yes/No group, combine vbDefaultButton2 with vbYesNo, because No is the second button. This changes the initial selection, not the available choices.
Can MsgBox show Yes, No, and Cancel?
Yes. Use vbYesNoCancel as the button-group constant and handle vbYes, vbNo, and vbCancel separately if their outcomes differ.
Can I use MsgBox to collect typed input?
No. MsgBox displays a message and receives a button choice; it does not provide a text-entry field. Use VBA’s InputBox for simple text input or a UserForm for a more controlled interface.
Related
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Count: How to Get the Number of KeysTo get the number of keys in a VBA Dictionary, read its Count property: dict.Count. It returns the…
- Import CSV to Excel Using VBA: A Practical GuideTo import a CSV file into Excel with VBA, use Workbooks.OpenText to parse the file, then copy its…
- VBA Array Count: Count Elements With LBound and UBoundTo count elements in a one-dimensional VBA array, use UBound(arrayName) - LBound(arrayName) + 1. For example, an array…
- Google Sheets Macros: How They Work and How to Use ThemYes. Google Sheets supports macros: you can record a sequence of spreadsheet actions and replay it, or customize…
- Find Unique Values in VBA: Methods and ExamplesTo find distinct values in VBA, read the source cells into a Scripting.Dictionary and add a value only…
- Excel VBA Active Range: What Selection Really MeansExcel VBA does not have a built-in ActiveRange property. To refer to the range currently selected in Excel,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.