Excel VBA: Develop and Use a Yes No Message Box

In this article, I’ll show you how you can develop and use a Yes No message box in VBA in Excel.


Develop and Use a Yes No Message Box with Excel VBA (Quick View)

Sub Yes_No_Message_Box()

Answer = MsgBox("Do You Like ExcelDemy?", vbYesNo)

If Answer = vbYes Then
    Range("C3") = Range("C3") + 1
ElseIf Answer = vbNo Then
    Range("C4") = Range("C4") + 1
End If

End Sub

VBA Code to Develop a Yes No Message Box in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


An Overview of the VBA Code to Develop and Use a Yes No Message Box (Step-by-Step Analysis)

Let’s learn the use of a yes-no message box with a simple example. The message box will ask you a 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.

Now, what will happen after you click yes or no in the message box? 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.

So, how to accomplish this whole task with a VBA code? Easy. There are 2 major steps in the whole process.

  • Developing the Yes-No Message Box
  • Using the Output of the Message Box

I am showing the detail of each step for your learning.

⧪ Step 1: Developing the Yes-No Message Box

First of all, you have to create the yes-no message box in VBA. This is easy. Use the same procedure as the ordinary message box, with the question as to the argument, along with a new argument vbYesNo.

Here the question is, “Do You Like ExcelDemy?”

Answer = MsgBox("Do You Like ExcelDemy?", vbYesNo)

Developing Message Box to Develop and Use a Yes No Message Box in Excel

⧪ Step 2: Using the Message Box Output

Next, we’ll accomplish a task using the message box output. Here, cell C3 contains the number of people who like ExcelDemy, and cell C4 contains the number of people who don’t like ExcelDemy.

So, if the answer is Yes, cell C3 will increase by one. And if it’s No, cell C4 will increase by one.

We’ll use an If-block to execute this.

If Answer = vbYes Then
    Range("C3") = Range("C3") + 1
ElseIf Answer = vbNo Then
    Range("C4") = Range("C4") + 1
End If

Using If-block to Develop and Use a Yes No Message Box in Excel

So the complete VBA code will be:

VBA Code:

Sub Yes_No_Message_Box()

Answer = MsgBox("Do You Like ExcelDemy?", vbYesNo)

If Answer = vbYes Then
    Range("C3") = Range("C3") + 1
ElseIf Answer = vbNo Then
    Range("C4") = Range("C4") + 1
End If

End Sub

VBA Code to Develop a Yes No Message Box in Excel


Creating the Macro to Develop and Use a Yes No Message Box in Excel

We’ve seen the step-by-step analysis of the code to develop and use a Yes-No message box. Now, let’s see how we can build a Macro to run the code.

⧪ Step 1: Opening the VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

Opening the VBA Window to Develop and Use a Yes No Message Box in Excel

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.

⧪ Step 3: Putting the VBA Code

This is the most important step. Insert the given VBA code in the module.

⧪ Step 4: Running the Code

Click on the Run Sub / UserForm tool from the toolbar above.

Running the Code to Develop and Use a Yes No Message Box in Excel

The code will run. A message box will ask you whether you like ExcelDemy or not, with a Yes and a No option.

If you choose Yes, the number in cell C3 will increase by one. And if you choose No, the number in cell C4 will increase by one.

Here, I’ve selected yes, so the number of people who like ExcelDemy has increased by one.


Things to Remember

  • A message box in VBA contains a total of 4 parameters called Prompt, Button, Title, and Helpfile. Here I’ve shown only 2 parameters, Prompt and Button. But if you want to discover the VBA message box in more detail, you can check this link.

Conclusion

Therefore, this is the process to develop and use a Yes No message box in VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo