VBA: How to Create Message Box with Yes/No Responses

You can use the following syntax in VBA to create a message box that allows a user to select Yes or No:

Sub MsgBoxYesNo()

    'ask user if they want to multiply two cells
    UserResponse = MsgBox("Do you want to multiply cells A1 and B1?", vbYesNo)

    'perform action based on user response
    If UserResponse = vbYes Then
      Range("C1") = Range("A1") * Range("B1")
    Else
      MsgBox "No Multiplication was Performed"
    End If

End Sub

This particular macro creates a message box that asks the user if they want to multiply cells A1 and B1.

If the user clicks “Yes” then the two cells are multiplied and the result is shown in cell C1.

If the user clicks “No” then a new message box appears that tells the them no multiplication was performed.

Note that the statement vbYesNo is what inserts “Yes” and “No” buttons for the user to click.

The following examples shows how to use this syntax in practice.

Example: Create Message Box with Yes/No Responses

Suppose we have the following two values in cells A1 and B1 in our Excel sheet:

Suppose we would like to create a macro that shows a message box to the user and asks them whether they’d like to multiply the values in cells A1 and B1 or not.

We can create the following macro to do so:

Sub MsgBoxYesNo()

    'ask user if they want to multiply two cells
    UserResponse = MsgBox("Do you want to multiply cells A1 and B1?", vbYesNo)

    'perform action based on user response
    If UserResponse = vbYes Then
      Range("C1") = Range("A1") * Range("B1")
    Else
      MsgBox "No Multiplication was Performed"
    End If

End Sub

When we run this macro, the following message box appears:

If we click Yes, then the macro will multiply the values in cells A1 and B2 and display the result in cell C1:

However, if we click No then a new message box will appear:

The message box tells us that no multiplication was performed since we clicked No in the previous message box.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Add New Line to Message Box
VBA: How to Add New Sheets
VBA: How to Freeze Panes