How to Create Yes No InputBox with Excel VBA

Looking for ways to create Yes No InputBox with Excel VBA? Then, this is the right place for you.

Excel has InputBox and MsgBox for inserting values/ text and displaying any type of information. After customizing the MsgBox, it is possible to show the yes-no button in the MsgBox. Just like that, InputBox can be customized to take input of Yes/ No. In this article, I am going to write about creating the Yes No InputBox.

The above image is an example of how the Application.InputBox takes user input and stores the input to the range of cells. The VBA code has to fill up the Daily To-do List with Yes/ No InputBox.

Download Practice Workbook

You can download the practice workbook here.

Is It Possible to Create a Yes No InputBox in Excel with VBA?

Yes. It is possible to create a custom Yes No Inputbox which will only take Yes/ No input. In this article, a detailed explanation is provided for you to follow.

How to Launch VBA Editor in Excel

There are various methods by which you can open the VBA editor. The most commonly used method is Developer Tab >> Visual Basic and the simplest way to launch the VBA editor is to press ALT+F11.

If you don’t have the Developer tab in your Excel workbook, follow the below steps:

1. First, right-click anywhere on the Tab section. Then, click on Customize the Ribbon…

2. Go to Customize Ribbon >> Developer. Next, select it and finally click OK.

• Now, a blank Visual Basic window will appear. Go to Insert >> Module to create a module space.

Introduction to InputBox Function

As the name implies, we can request responses using the VBA InputBox function. Particularly, this is useful for obtaining a single input from the user.

Objective:

This function returns an InputBox with some kind of criteria.

Syntax:

The syntax of this function is as follows:

InputBox(Prompt, [Title], [Default], [Xpos], [Ypos], [Helpfile], [Context] As String)

Parameters or Arguments:

The arguments are defined as follows.

Prompt: This is the only required argument of this function. This text is displayed in the input box.

Title: It is optional. This text is displayed in the input box’s title bar.

Default: It is also optional. The default value showed in the input field.

Xpos: It is a numeric value that indicates the prompt’s horizontal distance from the left side of the screen (X-axis position); if left empty, the input box will be horizontally centered.

Ypos: It is an optional argument as well. It tells of the Y-axis positioning location of the InputBox.

Helpfile: to provide a user-friendly assistance file. By pressing the help button, the person can open that file.

Context: It is a string argument that indicates the HelpContextId in the ‘Helpfile’ that is being called. It’s an optional parameter. But it becomes required when the ‘helpfile’ argument is given.

How to Create VBA Yes No InputBox

To create an InputBox, you can simply follow the parameters or arguments. One sample VBA InputBox is shown below. The optional parameters can be used according to the need of the program. Here, my Excel version is Microsoft Excel 365.

Type the following VBA code to create a sample InputBox:

Sub input_box()
InputBox "Sample InputBox", "InputBox", "Type Here"
End Sub

The code in Visual Basic will look like this:

Now, if you Run the VBA code, then a pop up window will appear.

Not only that, you can store the value of InputBox and take only specific values from the user.

1. Take Yes/No Input with VBA InputBox Statement

If you need to take user input in as Yes/ No or any other form, you can either store it in a variable or in a cell range. Below examples shows how to store user input in both forms.

Example 1. Return Yes No in MsgBox

To store the value of user input in a variable write the following VBA code:

Sub Store_Value()
Dim value As Variant
value = InputBox("Enter Yes or No")
If StrComp(value, "Yes", vbTextCompare) = 0 Then
    MsgBox "You entered YES"
ElseIf StrComp(value, "No", vbTextCompare) = 0 Then
    MsgBox "You entered NO"
Else
    MsgBox "Invalid input. Please enter either Yes or No."
End If
End Sub

The below image is the code in Visual Basic.

• Now, if you run the code, a pop-up window of InputBox will appear to take Yes/ No as input.
• Next, enter Yes/ No in the specific space and click OK.

The short video demonstration is shown below:

Example 2: Store Yes/No Input in Cell Range

I have a dataset of a daily To-do list, where I need to insert Yes/ No based on the task.

If you want to store the Value of InputBox to a cell, modify the code like below:

Sub Store_Value()
    Dim value As Variant
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row    
    For i = 5 To lastRow
        Do
            value = InputBox("Have you completed task " & Cells(i, "B").value & "? Please enter either Yes or No.")
            If StrComp(value, "Yes", vbTextCompare) = 0 Then
                Cells(i, "D").value = "Yes"
                Exit Do
            ElseIf StrComp(value, "No", vbTextCompare) = 0 Then
                Cells(i, "D").value = "No"
                Exit Do
            Else
                MsgBox "Invalid input. Please enter either Yes or No."
            End If
        Loop
    Next i
End Sub

The below image is the code in Visual Basic.

• Run the code and enter either Yes/ No.

The short video demonstration is shown below:

2. Specify Type of Input Data with Application.InputBox Statement

Using Application.InputBox, the user can restrict the input information to specific criteria like only numbers, or only characters, etc. First, you need to learn about the syntax and arguments of the Application.InputBox.

The arguments of this method are mostly similar to the arguments of the InputBox function. Just there is an extra parameter here which is Type. It’s an optional argument. Actually, it defines the type of returned data. In the absence of this option, the InputBox will just return text as a default. Here is the list of the value of the data types.

So, Application.InputBox has some advantages over the InputBox function.

• We can define the resulting datatype.
• You can identify when someone clicks the Cancel button.
• You may quickly determine whether the return value is False by assigning it to a Variant datatype.

For the same example of the daily To-do list, place the following modified code into Visual Basic:

Sub Application_InputBox()
    Dim value As Variant
    Dim lastRow As Long    
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row    
    For i = 5 To lastRow
        Do
            value = Application.InputBox("Have you completed task " & _
            Cells(i, "B").value & "? Please enter either Yes or No.", "Exceldemy", Type:=2)
            If StrComp(value, "Yes", vbTextCompare) = 0 Then
                Cells(i, "D").value = "Yes"
                Exit Do
            ElseIf StrComp(value, "No", vbTextCompare) = 0 Then
                Cells(i, "D").value = "No"
                Exit Do
            Else
                MsgBox "Invalid input. Please enter either Yes or No."
            End If
        Loop
    Next i
End Sub

The code in Visual Basic will look like this:

This code will work the same as the previous example of storing Yes/No Input in Cell Range.

How to Create VBA Yes No MsgBox in Excel

To create a VBA YesNo MsgBox, one first needs to know about the syntax of the MsgBox.

MsgBox(Prompt, [Buttons As VbMsgBoxStyle = vbOKOnly], [Title], [HelpFile], [context]) As VbMsgBoxResult

Prompt – It is the required argument. It contains the information we want to display, usually a string expression. If we don’t put any information then it shows a blank MsgBox.

[Buttons] – It is optional. By default, MsgBox shows the OK button. There are some built-in buttons, that we can use with customizing the MsgBox by calling the name like vbOKCancel, vbAbortRetryIgnore, vbYesNo, etc.

[Title] – It is at the top part of the MsgBox. By default, MsgBox shows the name of the application which is Microsoft Excel. It can be customized by the user but is optional.

[HelpFile] – It is also optional. This argument helps to attach a help file to the help button, which will address the issue with a correlated line of code. One point to remember is that, when HelpFile is used, the Context argument is to be filled.

[Context] – It is an optional numeric expression. It is the Help context number assigned to a Help topic.

Type the following code to create a simple MsgBox with Yes and No Buttons:

Sub YesNo_Buttons()
    MsgBox "Do you agree with the terms and conditions?", vbYesNo
End Sub

The below image shows the code in Visual Basic.

Output of the above VBA code:

Practical Examples of Using YesNo InputBox in Excel VBA

Example 1: Create an Impression Counter with VBA Yes/No InputBox

In this example, the message box will ask you the question, “Do You Like ExcelDemy?” If your answer is yes, you will click Yes in the message box. And if your answer is No, you will click No.

In the active worksheet, there are 2 cells that contain the number of people who like and dislike ExcelDemy. If you hit yes, the number in the like cell will increase by one. And if you hit no, the number in the dislike cell will increase by one.

Type the below VBA code in your Visual Basic:

Sub Impression_Counter()
Answer = MsgBox("Do You Like ExcelDemy?", vbYesNo)
If Answer = vbYes Then
    Range("C4") = Range("C4") + 1
ElseIf Answer = vbNo Then
    Range("C5") = Range("C5") + 1
End If
End Sub

Your code on Visual Basic will look like this:

Now, if you Run the VBA code, a pop-up window will ask the survey question. Here, I have added a macro button “Give Review” assigning the specific code to easily run the VBA code.

The short video demonstration is shown below:

Example 2: Create an Attendance Tracker with Yes/No Input Box

In this example, the input box will take presents for five students. In this process, the code will ask questions five consecutive times and put the input into the designated cells.

Write the below VBA code into your Visual Basic:

Sub Attendance_Tracker()
Dim Rows As Integer
Rows = 5
For i = 1 To 5
    Answer = MsgBox("Is roll " & i & " present?", vbYesNo)
    If Answer = vbYes Then
        Cells(Rows, 3) = "Yes"
    ElseIf Answer = vbNo Then
        Cells(Rows, 3) = "No"
    End If
    Rows = Rows + 1
Next i
End Sub

Your code on Visual Basic will look like this. Now, if you Run the VBA code, a pop-up window will ask for input. Here, I have added a macro button “Click to Satrt” assigning the specific code to easily run the VBA code.

The short video demonstration is below:

FAQs

1. What are the Options of MsgBox in Excel VBA?

VBA MsgBox displays any kind of text in a message box. The default button for the message box is only OK. There are more button options in the message box. The options of MsgBox in Excel VBA are Ok, Cancel, Abort, Retry, Ignore, Yes, and No.

2. What are the different types of message boxes?

The four types of message boxes are Alert, Confirm, Inquire, and Generic.

3. What is the difference between InputBox() and MsgBox()?

As the name implies, we can request responses using the VBA InputBox function. Particularly, this is useful for obtaining a single input from the user.

We use the MsgBox function to display any kind of text. It can return both default values and custom values.

So, the difference is we use one function to take input and the other one for displaying output or any other type of text.

Key Takeaways from Article

• You learned about the InputBox function with an example.
• You can store the value of InputBox to a variable or cell. Moreover, you can validate the input from the user.
• You can create VBA Yes No MsgBox.
• Practical Examples of Using YesNo InputBox in Excel VBA.

Conclusion

In this article, you learned how to create VBA Yes No InputBox with examples in Excel. Follow our ExcelDemy page for regular tips and tricks regarding Excel. You can suggest your thoughts about this article in the comment section below.