Do You Wish to Continue Yes or Yes

Excel VBA Msgbox Yes/No

In this article, we will see an outline on Excel VBA Msgbox Yes/No. Message box in VBA is used to see the output and any message related to the process which we do. It is easy to insert the message box in any VBA Code. But what is tricky is to get a message box with YES and NO button. We all have seen such boxes when we try to close any file without saving it. In that case, windows give us the alert, asking "Do you want to save this file?" and with 2 buttons YES and NO or OK and CANCEL. This type of boxes we can create in VBA. The good thing about these message boxes is we will always be getting an alert when we are at certain steps where we need to do that task or consume the display values.

How to Work with Message Box Yes/No Response in Excel VBA?

We will learn how to work with Message Box Yes/No Response in Excel by using the VBA Code.

You can download this VBA Msgbox Yes/No Excel Template here – VBA Msgbox Yes/No Excel Template

VBA Msgbox Yes/No – Example #1

We will start by seeing how simple message box works. Good thing is, we don't even need any data for this. Let us follow the below steps.

Step 1:Open a module from the Insert menu as shown below.

Step 2:Write the subprocedure in the name of the performed operation.

Code:

            Sub            VBA_MsgBox()            End Sub          

Step 3:Now choose MsgBox function and write any message in inverted quotes in it.

Code:

            Sub            VBA_MsgBox()  MsgBox "VBA Message Box"            End Sub          

Step 4:Once done, Run the code by pressing the F5 key or by clicking on the Play Button. We will get a message box with the message "VBA Message Box". And if click on Ok, then we will be exited from it.

Step 5:Now we want to add a message box with YES and NO buttons. For this create a new subprocedure again or we can use the same. And use MsgBox function again with a new message. You can choose any other message.

Code:

            Sub            VBA_MsgBox1()            End Sub          

Step 6:Suppose we save an unsaved file so we would expect this kind of message as shown below.

Code:

            Sub            VBA_MsgBox1()  MsgBox "Do Want Save The File?"            End Sub          

Step 7:Now to insert a customized message in VBA, we need to use the word "vb" followed by what type of buttons we want.

Code:

            Sub            VBA_MsgBox1()  MsgBox "Do Want Save The File?", vbYesNo            End Sub          

Step 8:Now we expect to get YES and NO button in the message box. Run the code by pressing the F5 key or by clicking on the Play Button. Here we can see the message box with YES and NO buttons.

Clicking on any option will exit from the code.

VBA Msgbox Yes/No – Example #2

Let's see another type of Message box YES NO. This type of message we have seen. Suppose if we are downloading a file. Due to some error file couldn't be downloaded completely. Here we can create this kind of message box. Let us follow the below steps.

Step 1:Write the subprocedure for the VBA message box.

Code:

            Sub            VBA_MsgBox2()            End Sub          

Step 2:Let's consider the same type of message which we have discussed above.

Code:

            Sub            VBA_MsgBox2()  MsgBox "Unable To Complete Current Download?",            End Sub          

Step 3:In such cases either we can Abort the download, Ignore the message or Retry downloading the file again. So write such type of message button names followed by vb.

Code:

            Sub            VBA_MsgBox2()  MsgBox "Unable To Complete Current Download?", vbAbortRetryIgnore            End Sub          

Step 4:Now, Run the code by pressing the F5 key or by clicking on the Play Button. This is how our message box will look like.

And again if we click on any of the buttons, it will put us out of the code.

VBA Msgbox Yes/No – Example #3

Let's see another type of Message box. This type of message comes when we open some certain files or links which might be dangerous. In that case, windows alert us if we want to continue. Let us follow the below steps.

Step 1:Write the subprocedure of the VBA message box.

Code:

            Sub            VBA_MsgBox3()            End Sub          

Step 2:Use MsgBox function and choose the message which we discussed. And followed by vbOKCancel.

Code:

            Sub            VBA_MsgBox3()  MsgBox "Do you want to continue?", vbOKCancel            End Sub          

Step 3:And we run this code we will get the message box asking "Do you want to continue?" with option OK and CANCEL.

VBA Msgbox Yes/No – Example #4

In this example, we will see how to create a message box with a question mark and when we proceed with clicking on the buttons we will see how to get another message,= if required. Let us follow the below steps.

Step 1:Open a module. In that declare a variable as Integer.

Code:

            Sub            VBA_MsgBox4()            Dim            Result              As Integer            End Sub          

Step 2:In the defined variable, we will use the message box. As we saw in other examples we will use the message and the vbYESNO for buttons. And to get the question mark, use a similar way of using VB.

Code:

            Sub            VBA_MsgBox4()            Dim            Result              As Integer            Result = MsgBox("Still Want To Continue?", vbQuestion + vbYesNo)            End Sub          

Step 3:To get another message box, use the If-Else loop. Write the condition, we click on YES then we would get the message as Okay.

Code:

            Sub            VBA_MsgBox4()            Dim            Result              As Integer            Result = MsgBox("Still Want To Continue?", vbQuestion + vbYesNo)            If            Result = vbYes            Then            MsgBox "Okay"            End Sub          

Step 4:Or Else, if we will get a message as CLOSE.

Code:

            Sub            VBA_MsgBox4()            Dim            Result              As Integer            Result = MsgBox("Still Want To Continue?", vbQuestion + vbYesNo)            If            Result = vbYes            Then            MsgBox "Okay"            Else            MsgBox "Close"            End If            End Sub          

Step 5:Now we can compile the code. And then run it. As we can see, we got the message box which we have set in the code.

Step 6:Now we will click on YES first. We will get the message Okay.

And if we again run the code and click on NO, we will get the message as Close as shown below.

Pros of VBA MsgBox Yes/No

• We can create any type of message box.
• Loop message boxes with multiple conditions are easy to create.
• This is quite useful when we are working on huge code and big data is also involved in it.

Things to Remember

• We can insert multiple buttons as per our needs. Although most of the realistic message boxes have 2 or maximum 3 buttons in the message box.
• We can also insert question mark, alert mark, exclamation mark, etc. in the message box itself.
• Message boxes can have any message but the message should be short and precise enough to see the actual message.
• Messages should be understandable to all the viewers.
• Once done with creating the code, save the file in a macro-enabled excel format. This allows us to retain the code within the file.

Recommended Articles

This is a guide to VBA Msgbox Yes/No. Here we discuss how to work with Message Box Yes/No Response in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA DateDiff
2. VBA Square Root
3. VBA SendKeys
4. VBA Name Worksheet

dixonheighte.blogspot.com

Source: https://www.educba.com/vba-msgbox-yes-no/

Share this post