VBA Msgbox Yes/No

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.

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