VBA MsgBox Yes/No Run Macro: How to Code It
To run a macro based on a Yes/No MsgBox in VBA, use the MsgBox function with the vbYesNo button constant and then check the returned value (vbYes or vbNo) in an If statement. For example: If MsgBox("Proceed?", vbYesNo) = vbYes Then Call MyMacro. This guide explains the syntax, how to customize the buttons and icons, common mistakes, and answers frequent questions.
Background: Why Use a Yes/No Message Box?
In Excel and other Office applications, a message box (MsgBox) is the simplest way to ask the user for a decision before running a potentially destructive or time-consuming macro. The Yes/No prompt prevents accidental execution and gives the user control. VBA’s built-in MsgBox function is versatile: it can display a message, choose which buttons appear (Yes, No, OK, Cancel, etc.), and return an integer that tells your code which button the user clicked.
This pattern is used in countless macros, from confirming a data deletion to asking whether to save a workbook before closing. Understanding how to capture the button choice is a fundamental VBA skill that applies to almost any Office automation project.
How the MsgBox Function Works
The MsgBox function has two main syntaxes: one that just displays a message (statement) and one that returns a result (function). For a Yes/No decision, you always use the function form, which requires parentheses around the arguments.
The basic syntax is:
MsgBox(prompt, [buttons], [title], [helpfile, context])
- prompt (required): The text displayed in the message box.
- buttons (optional): A numeric expression that specifies which buttons and icons to show. Use built-in constants like
vbYesNo,vbQuestion, etc. - title (optional): The text in the title bar of the message box.
- helpfile, context: Rarely used; for custom help.
The function returns an integer that corresponds to the button clicked. The most common return values are:
vbYes= 6vbNo= 7vbCancel= 2vbOK= 1
You can compare the returned value directly to these constants, which makes the code readable.
Basic Code to Run a Macro on Yes
The simplest pattern is to call another macro when the user clicks Yes, and optionally do nothing or something else when they click No.
Sub AskUser()
If MsgBox("Do you want to run the report?", vbYesNo, "Confirm") = vbYes Then
Call GenerateReport
Else
MsgBox "Action cancelled."
End If
End Sub
Sub GenerateReport()
' Your macro code here
End Sub
In this example, vbYesNo displays only Yes and No buttons. The If statement checks the return value. If it equals vbYes, the GenerateReport macro runs. If not (i.e., No), a simple message appears.
You can also run the code directly inside the If block instead of calling a separate macro:
If MsgBox("Delete all rows?", vbYesNo + vbExclamation) = vbYes Then
Rows("1:100").Delete
End If
Choosing the Right Button Constants
VBA provides several constants that you can combine using the + operator to control the buttons and icons. For a Yes/No prompt, you usually start with vbYesNo and then add an icon constant to convey the tone.
- vbYesNo (4): Shows Yes and No buttons.
- vbYesNoCancel (3): Shows Yes, No, and Cancel buttons.
- vbQuestion (32): Displays a question mark icon.
- vbExclamation (48): Displays an exclamation point icon.
- vbInformation (64): Displays an information icon.
- vbCritical (16): Displays a red X icon.
For example, vbYesNo + vbQuestion shows a question mark with Yes/No buttons. vbYesNoCancel + vbExclamation shows an exclamation with three buttons. You can also set a default button using vbDefaultButton2 (makes No the default) or vbDefaultButton3 (makes Cancel the default). This is useful to prevent accidental Enter presses from running a destructive action.
Common Problems and How to Avoid Them
Several pitfalls trip up developers new to MsgBox:
- Forgetting parentheses: When using the function form (to capture the return value), you must enclose arguments in parentheses.
MsgBox "Text", vbYesNois a statement and returns nothing — you cannot assign it to a variable. - Using
vbYesinstead ofvbYesNo:vbYesis a return value, not a button constant. To show Yes/No buttons, usevbYesNo. - Not handling Cancel: If you use
vbYesNoCancel, the user can click Cancel. Ignoring this can lead to unintended actions. Always check forvbCancelseparately. - Comparing to the wrong integer: Some developers hardcode numbers (e.g.,
= 6). This works but is less readable and error-prone. Use the named constants. - Using
MsgBoxin a loop: If the user clicks No, the code might loop back and show the box again. Ensure your logic exits the loop or changes the prompt.
Comparing MsgBox with InputBox and UserForms
For a simple Yes/No decision, MsgBox is the lightest and fastest method. However, there are alternatives:
- InputBox: Asks for text input, not a binary choice. Useful if you need a value, not just a confirmation.
- UserForm: A custom dialog with buttons, checkboxes, and text boxes. More flexible but requires more code and design work. Use when you need multiple options or a more polished interface.
- Application.DisplayAlerts: Suppresses Excel’s built-in prompts (e.g., before deleting a sheet). This is not a replacement for a custom Yes/No but can be used to avoid interruptions when you already know the user’s intent.
For most confirmation scenarios, MsgBox is the standard choice because it is built-in, simple, and requires no additional UI design.
Safety and Best Practices
When using a Yes/No prompt to gate a macro, consider these safety measures:
- Always set a default button that is the safest option (e.g.,
vbDefaultButton2to make No the default if the action is destructive). - If the macro modifies data irreversibly, consider adding an extra confirmation step (e.g., type “YES” to confirm) or use
Application.Undowhere possible. - Test the macro with both Yes and No responses, and also test what happens if the user presses Escape (which is equivalent to No in a Yes/No box).
- Use meaningful prompts that clearly state what will happen. For example, “Delete all rows in Sheet1? This cannot be undone.”
Remember that MsgBox is synchronous: the code pauses until the user responds. This is fine for a single prompt, but if you have multiple prompts in a loop, it can be annoying. In that case, consider a UserForm or a single prompt that asks for a batch action.
Frequently Asked Questions
How do I run a macro only if the user clicks Yes?
Use the If MsgBox(...) = vbYes Then pattern. The macro inside the If block runs only when the user clicks Yes. For example: If MsgBox("Run?", vbYesNo) = vbYes Then Call MyMacro.
What does vbYesNo do?
vbYesNo is a VBA constant with a value of 4. It tells the MsgBox function to display only Yes and No buttons. The user cannot click Cancel or X to close the box; they must choose one of the two buttons.
Can I make the No button the default so pressing Enter doesn’t run the macro?
Yes. Add vbDefaultButton2 to the button constant. For example: MsgBox "Delete?", vbYesNo + vbDefaultButton2. This makes No the default button, so pressing Enter selects No, which is safer for destructive actions.
How do I detect if the user pressed the X (close) button?
In a Yes/No box, pressing the X button is equivalent to clicking No (returns vbNo). If you need to distinguish, use vbYesNoCancel and check for vbCancel. The X button returns vbCancel in that case.
Why does my MsgBox code give a syntax error?
Most likely because you used the statement form without parentheses when trying to capture the return value. Use parentheses: result = MsgBox("Text", vbYesNo). Also ensure you are not using vbYes as the button constant; it should be vbYesNo.
Can I use a Yes/No MsgBox to stop a macro from running entirely?
Yes. Put the MsgBox at the top of the macro and use Exit Sub if the answer is No. For example: If MsgBox("Continue?", vbYesNo) <> vbYes Then Exit Sub.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.