Show MsgBox if contains a certain value within a string and then clear the cell

Question

I have a drop down menu in cell C11 to select currency pairs (Screenshot below). I want to write a macro that generates a MsgBox when the user selects one of the 7 Japanese pairs. Preferably, I want a code that checks if cell contains the value "JPY"s, then a message box should ask:

"You have selected a Japanese Yen cross, do you want to continue?"

Then, I want the user to be able to either:

• click on "Yes" and the message box will close.
• Click on "No" and the message box will close and cell C11 is cleared.

Thank you.

Answer 1

Try dropping the following on the sheet where your drop down is

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ans As VbMsgBoxResult

If Not Intersect(Target, Range("C11")) Is Nothing Then
    If InStr(Target, "JPY") Then
        ans = MsgBox("You have selected a Japanese Yen cross, do you want to continue?", vbYesNo)

        If ans = vbNo Then
            Application.EnableEvents = False
                Target.Clear
            Application.EnableEvents = True
        End If
    End If
End If

End Sub