VBA MsgBox Yes No Options

Binary yes and no responses are often give you all the information you need from your users. The built-in VBA MsgBox with Yes or No options is designed for this exact scenario. There is one caveat with binary choices, though, which we will cover in this tutorial, too.

• VBA MsgBox
  • vbYesNo or vbYesNoCancel
• Reading the Output of the MsgBox Function
  • The Value of Yes, No and Cancel
• Functional Programming With MsgBox

VBA MsgBox

VBA’s built-in MsgBox function provides a method for user input in a predefined dialog box. You can create your own userforms for this purpose, and if you are designing a professional-grade branded dashboard, I’d recommend doing so. Once you’ve designed a custom userform, you can control when to show the userform in your program.

Most applications don’t need fully customized userforms for basic yes/no questions. If you just need simple, quick solution, the built-in VBA MsgBox dialogs are the better option. They give the popup a natural feel since the MsgBox is a native VBA function, and you know the visuals will work on the first try.

VBA message boxes take 5 parameters, but all except the first one are optional:

is the text which appears on the popup box. Since this tutorial focuses on yes or no questions, we’ll assume your prompt is going to take on a structure that requests a yes or no response.

• Is the entered name correct?
• There are 87,000 records; do you want to proceed?

You can put any string in the Prompt field. Often you’ll use concatenation with a variable, like we demonstrated with our VBA Concatenate article.

If you want Yes or No to be the choices for your users, you must also pass the Buttons parameter. Enter vbYesNo in the Buttons parameter to display Yes and No buttons.

Let’s try it with this code:

MsgBox "This process will take about 15 minutes. Do you want to proceed?", vbYesNo

Tip: You can use the VBA immediate window (shortcut Ctrl+G from the VBE) to quickly run this macro.

Now the user can decide whether to proceed right away or delay their response. Notice, there are only two possible choices. The “X” in the upper right of the window is disabled so the user can’t click it.

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

vbYesNo or vbYesNoCancel

When you ran the code above, you probably noticed that there is no way to “X” out of the dialog box. The user must answer Yes or No, and they have no alternative. The only other choice is to delay the process by not choosing Yes or No, but eventually an answer must be chosen. They could also kill the Excel instance, then complain that Yes and No are not adequate for all scenarios…

So, if you want to force your users to make a binary Yes No decision, then you can use vbYesNo as your Buttons parameter. Most of the time, this is acceptable. If you are verifying the content of an inputbox this way, the user may answer No to the prompt and the code could fetch the input again until the answer is Yes. Alternatively, a No response may stop execution entirely and exit the subroutine.

Passing vbYesNoCancel to the Buttons parameter provides a third option for the user. It adds a Cancel button and enables an “X” in the upper-right of the dialog box. Clicking the “X” does the same thing as choosing the Cancel button. Sometimes users feel more comfortable pressing “X” than “Cancel” so both options are available.

MsgBox "This process will take about 15 minutes. Do you want to proceed?", vbYesNoCancel

Reading the Output of the MsgBox Function

In VBA, subroutines don’t have an output value but functions do. Because MsgBox is a function, it also has an output value. Intuitively this makes sense: if there were no output values, how would the program know which choice was made?

The output values weren’t captured in the codes mentioned above. VBA received the value chosen (Yes or No (or Cancel)), stored it in memory for a brief moment, then ended the subroutine and threw the value away.

To store the value, assign the function to a variable. The code (enforcing Integer typing) will look like this:

Sub vbYesNoDemo() 
Dim userResponse As Integer
userResponse = MsgBox("This process will take about 15 minutes. Do you want to proceed?", vbYesNo)
End Sub

Make sure to use the parentheses to encapsulate the MsgBox function parameters on the right side of the assignment.

The Value of Yes, No and Cancel

The output from the native MsgBox function is neither a string nor a binary value, as one may suspect, but an integer. This makes it easier to process programmatically, and it is simpler to remember just three values than an exact string (is it “Yes” or “yes” or “y” or “YES”?). It also allows MsgBox to have multiple outputs other than just Yes and No (for which a Boolean value would suffice).

The values of the three buttons here are:

• Yes = 6 = vbYes
• No = 7 = vbNo
• Cancel = 2 = vbCancel

Using the “X” on the vbYesNoCancel version also produces 2. Technically, the vb Buttons parameter itself also represents a numeric value, but it’s much easier to use the proper vb Names (vbYesNo and vbYesNoCancel.

By capturing the output of the MsgBox function like we did in the example above, you can use userResponse as you would any other integer. You can compare its value in if statements, use it in Select Case blocks, or even add numbers together if you want.

Functional Programming With MsgBox

Instead of storing the value in a specific variable, it’s perfectly acceptable to use the output value as a temporary memory value, which is thrown away as soon as a specific line of code is finished being evaluated. This is called functional programming, because what is normally stored as a variable becomes the input to another function, like a series of nested functions. It is not always the clearest code, but it is certainly compact and reduces the need to name and store variables.

For instance, instead of using this code block:

Sub vbYesNoDemo2() 
Dim userResponse As Integer
userResponse = MsgBox("This process will take about 15 minutes. Do you want to proceed?", vbYesNo)

If userResponse = 6 Then
    'proceed
Else
    'retry
End If
End Sub

You could shorten it a bit like this:

Sub vbYesNoDemo3() 
If MsgBox("This process will take about 15 minutes. Do you want to proceed?", vbYesNo) = 6 Then
    'proceed
Else
    'retry
End If

You wouldn’t ever need to think about how to type the output value (Integer, Boolean, etc.).

Conveniently, if you are writing the condensed form and have Intellisense enabled, you’ll even get the proper vb names of the potential outputs of the MsgBox function.

You can use these vb names in your conditional statements instead of remembering the integers, if you prefer. Here’s an example:

Sub vbYesNoDemo()
Dim userResponse As Integer
userResponse = MsgBox("This process will take about 15 minutes. Do you want to proceed?", vbYesNoCancel)

If userResponse = vbYes Then
    'Yes button clicked
ElseIf userResponse = vbNo Then
    'No button clicked
Else
    'Cancel button clicked
End If
End Sub

Binary responses from users are very commonly needed by programs that take user input. For example, they can verify previously input information and they can allow users to stop long processes before they start.

The VBA MsgBox Yes No examples in this tutorial provide quick dialog boxes for scenarios like this without the need to build your own userforms from scratch.

If you haven’t already done so, consider subscribing using the form below to get more VBA tips like this sent to your inbox.