Use VBA’s MsgBox function to display buttons, store the button the user clicks, and branch on that result with Select Case. For example, a Yes/No/Cancel message returns vbYes, vbNo, or vbCancel, which you can handle in separate cases.
What MsgBox and Select Case do
MsgBox displays a dialog and returns a value that represents the user’s response. The buttons shown are controlled by its second argument; the returned value is what your VBA code can inspect afterward. Select Case is a control structure that compares one expression against possible values and runs the matching block.
These are two distinct steps, not a special combined feature: first call MsgBox and capture its return value, then use Select Case to respond. The pattern is useful when a prompt has several outcomes, especially when the user can choose Yes, No, or Cancel. It also makes the possible actions visible in one place, rather than nesting multiple If statements.
The constants such as vbYes and vbCancel are built into VBA. Use them instead of comparing the result with numbers. Named constants are easier to read and help prevent confusing a button configuration value with a response value.
The basic pattern
Declare a variable to hold the result, assign the return value of MsgBox to it, and use that variable in Select Case. The following example shows the structure:
Sub AskWhatToDo()
Dim answer As VbMsgBoxResult
answer = MsgBox("Continue with this task?", vbYesNo + vbQuestion, "Confirm")
Select Case answer
Case vbYes
MsgBox "The task will continue."
Case vbNo
MsgBox "The task will stop."
End Select
End Sub
VbMsgBoxResult is the VBA enumeration type for the result. You can also declare the variable as an Integer, but the named type makes its purpose clearer. The first argument is the prompt text; the second combines button and icon options; the third supplies the dialog title.
In this example, vbYesNo determines which buttons appear and vbQuestion selects the icon. After the user clicks a button, execution resumes at the assignment’s next line, and the matching Case runs. Since the dialog offers only Yes and No, those are the only expected results.
Handling Yes, No, and Cancel
For a three-way decision, use vbYesNoCancel and include a case for each response. Here is a complete example that illustrates the branching without unexpectedly saving or closing a workbook:
Sub AskBeforeClosing()
Dim answer As VbMsgBoxResult
answer = MsgBox("Save changes before closing?", _
vbYesNoCancel + vbQuestion + vbDefaultButton2, _
"Close workbook")
Select Case answer
Case vbYes
MsgBox "Save the workbook before closing.", vbInformation, "Next step"
Case vbNo
MsgBox "Continue without saving, if that is intended.", vbInformation, "Next step"
Case vbCancel
MsgBox "The close operation was canceled.", vbInformation, "No changes made"
Case Else
MsgBox "No recognized response was returned.", vbExclamation, "Check response"
End Select
End Sub
The example only displays a follow-up message; it does not perform the save or close action. In a real macro, put the relevant action in the appropriate case. For instance, the vbYes branch might call ThisWorkbook.Save, while the cancel branch might exit the procedure. Think through what each answer means before adding actions that change workbook data.
vbDefaultButton2 makes the second button the default choice when the dialog opens. In this case that is No. A default button is selected initially, but it does not mean VBA has received that answer: the user still has to activate a button. Choose a default that reduces the chance of an unintended or destructive action.
Choosing buttons, icons, and defaults
The second argument of MsgBox accepts a button style and optional settings such as an icon or default button. Common button styles include:
vbOKOnlydisplays OK.vbOKCanceldisplays OK and Cancel.vbYesNodisplays Yes and No.vbYesNoCanceldisplays Yes, No, and Cancel.vbRetryCanceldisplays Retry and Cancel.
Icon constants include vbInformation, vbQuestion, vbExclamation, and vbCritical. The default-button constants include vbDefaultButton1, vbDefaultButton2, and, for dialogs with enough buttons, vbDefaultButton3 and vbDefaultButton4. Combine compatible options with the + operator, as in vbYesNoCancel + vbQuestion + vbDefaultButton2.
Choose a button set that matches the decision. A Yes/No prompt is appropriate when the user must make a binary choice. Include Cancel when stopping the operation is a meaningful third option. Do not use a question icon as a substitute for clear wording: state what the choice will do, especially if one response deletes, overwrites, or changes data.
How Select Case matches the response
Each Case names one or more values that should run the same block. For example, Case vbYes, vbRetry can group responses if both should trigger the same action. Use a separate case when the responses mean different things. Case Else is optional, but it can provide a fallback if the code receives a value you did not anticipate.
Match cases to the buttons actually displayed. If the message has vbYesNoCancel, a Case vbOK branch will not handle any of those three normal clicks. Conversely, showing an OK button does not create a Yes result. The returned response constants describe which button was clicked; the style constants describe which buttons were displayed.
It is possible to put the MsgBox call directly in the Select Case expression, but assigning the result to a variable is usually clearer when you have multiple branches. The variable also gives you a convenient place to inspect the response while debugging. Avoid displaying the same prompt multiple times just to test different conditions.
Common mistakes and troubleshooting
- Comparing against the wrong constants: Use response constants such as
vbYesin the cases, not button-style constants such asvbYesNo. They serve different purposes. - Using numbers instead of names: Although the results have underlying numeric values, hard-coded numbers make code harder to understand and easier to break. Compare with
vbYes,vbNo, or the relevant named constant. - Leaving out Cancel: If the dialog offers Cancel, include
Case vbCanceland decide what cancellation means. Otherwise, the macro may simply continue after theSelect Casewithout doing the intended cleanup or exit. - Expecting a button to run an action by itself: Clicking Yes only returns a result. The macro must contain code in the matching case to save, continue, delete, or perform another action.
- Misreading the default button:
vbDefaultButton2changes which button is initially selected; it does not change the return value or automatically answer the prompt.
If the editor highlights a line, check that each Case is inside the correct Select Case block and that the block ends with End Select. A broken line continuation can also cause a compile error: in VBA, the space and underscore at the end of a continued line must be present. Run the macro from a test copy if you are unsure what its branches do.
When to use If…Then or another approach
Select Case is a natural fit when one value has several distinct possible responses. For a simple two-way decision, If answer = vbYes Then followed by an Else may be shorter. If the prompt has three or more meaningful outcomes, Select Case tends to make the branches easier to scan.
For longer explanations, richer choices, or a form that needs several fields, consider a UserForm instead of a basic message box. A UserForm can present custom controls and layout, while MsgBox is best for brief prompts with standard buttons. A message box is modal, so the user must respond before continuing in the same procedure.
Do not treat MsgBox as a reliable way to collect arbitrary text or as a replacement for validation. It returns a button response, not the contents of a text field. If you need typed input, use an appropriate input method, validate that input, and explain errors in a way the user can act on.
Adding and running the macro safely
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure into the module, and run it with F5 while the cursor is inside the procedure. You can also return to Excel and use the Macros dialog, opened with Alt+F8, to select and run a public macro.
On a Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the keyboard shortcut can depend on the keyboard and function-key settings, and may be Option+F11 or Fn+Option+F11. Excel for the web does not run VBA macros; open the workbook in the desktop Excel app. To retain VBA code, save the workbook in a macro-enabled format such as .xlsm.
Before running a macro that changes a workbook, save a separate copy. VBA actions cannot be undone with Ctrl+Z, and running a macro may clear Excel’s undo history. A short message-box demonstration is low risk, but the same branching pattern can later control actions with real consequences, so test each response path on a copy first.
Frequently Asked Questions
Can I use Select Case with a VBA MsgBox?
Yes. Store the value returned by MsgBox in a variable, then use Select Case to match responses such as vbYes, vbNo, and vbCancel.
What does MsgBox return when the user clicks Yes?
It returns the named response constant vbYes. Compare against that constant in your code rather than relying on its underlying numeric value.
How do I add a Cancel option to a MsgBox?
Include vbCancel in the button style by using vbYesNoCancel, for example. Add a Case vbCancel branch so your macro defines what should happen when the user cancels.
Why is my Case vbYes branch not running?
Check that the dialog actually displays a Yes button, that the result variable receives the MsgBox return value, and that the case uses vbYes rather than the button-style constant vbYesNo.
Can MsgBox Select Case work 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 to run VBA, and use an .xlsm workbook when you need to preserve the macro.
Related
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Delete First Row: Code and Safe OptionsTo delete row 1 of a worksheet with VBA, use ws.Rows(1).Delete, where ws is the worksheet you intend…
- Add Worksheet VBA: Excel Methods and ExamplesTo add a worksheet with VBA in Excel, use Worksheets.Add, then assign the new sheet a name. For…
- Excel VBA Sleep: Pause a Macro SafelyTo pause an Excel VBA macro on Windows, declare the Windows API function Sleep in a standard VBA…
- VBA Find a Character in a StringIn VBA, use the InStr function to find a character in a string: it returns the character’s position,…
- Excel VBA Active Worksheet: How It Works and When to Use ItIn Excel VBA, ActiveSheet refers to the sheet currently selected in the active workbook. It is not a…
- Gorilla Copy and Paste VBA: Excel Methods and ExamplesThere is no standard Excel VBA command or feature called Gorilla; if you mean copying and pasting cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.