Stop Looping If Cell Equals "X"

[JCruz]

Hello, I've been searching the forum to help me with my problem.

I've managed to put a code together using the tips I have found on the forum. However, if I continue working the message keeps popping up.

How do I make it stop?

Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
'ActiveSheet.Unprotect Password:="PSWRD"
'Application.ScreenUpdating = False

If Range("E19").Value = "x" Then

If MsgBox("You have selected X." & vbNewLine & _
"" & vbNewLine & _
"Do you wish to continue?", vbYesNo, "Select X") = vbNo Then Exit Sub
End If

If Range("E19").Value = "y" Then
If MsgBox("You have selected Y." & vbNewLine & _
"" & vbNewLine & _
"Do you wish to continue?", vbYesNo, "Select Y") = vbNo Then Exit Sub

End If

 

[steve the fish]

Hi. What is supposed to happen if i click yes or no on the message box? As it stands it wouldnt make any difference. To address your other issue use this line at the top:

If Intersect(Target, Range("E19")) Is Nothing Then Exit Sub

 

[JCruz]

Hi Steve, thanks for the quick reply.

If you click on Yes, you can continue using the form. If you click on No, then ideally you cannot progress or another msgbox pops up and says, "Sorry, you are unable to progress."

Hope that makes sense.

 

[steve the fish]

Ok so there is more code. Use that line i gave you and you will only get the prompts if you change cell E19. Another thing to be aware of is there is a difference between Y and y so you solve that potential problem by using:

If LCase(Range("E19").Value) = "y" Then

so now the user can use Y or y and still get the prompt.

 

[JCruz]

Sorry, Steve. It doesn't work for me.

This is what I did.

I obviously am not doing it right.

If Intersect(Target, Range("E19")) Is Nothing Then Exit Sub
    
If LCase(Range("E19").Value) = "x" Then

If MsgBox("You have selected y.", vbYesNo, "Selected Y") = vbNo Then Exit Sub

End If

Ok so there is more code. Use that line i gave you and you will only get the prompts if you change cell E19. Another thing to be aware of is there is a difference between Y and y so you solve that potential problem by using:

If LCase(Range("E19").Value) = "y" Then

so now the user can use Y or y and still get the prompt.

 

[steve the fish]

Try it like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Set rng = Intersect(Target, Range("E19"))
If Not rng Is Nothing Then
    If LCase(rng.Value) = "x" Or LCase(rng.Value) = "y" Then
        If MsgBox("You have selected " & UCase(rng.Value) & "." & vbNewLine _
        & "Do you wish to continue?", vbYesNo, "Select " & UCase(rng.Value)) = vbNo Then
            Exit Sub
        Else
            MsgBox "You pressed yes!"
            'do something if user selects yes
        End If
    End If
End If

End Sub

 

[JCruz]

Yessss!!! It works if the user types in "x" or "y"!

What do I need to do is if the "x" and the "y" are generated by a formula in E19?

Many thanks for your time - really!