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
- Reading the Output of the MsgBox Function
- Functional Programming With MsgBox
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
If you want
vbYesNo in the
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 spreadsheets with our free VBA Developer's Guide 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 Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
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 Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
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
vbYesNoCancel to the
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
By capturing the output of the
MsgBox function like we did in the example above, you can use
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 (
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
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.