Stop Looping If Cell Equals "X"

JCruz

New Member
Joined
Aug 25, 2015
Messages
4
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
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:

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

JCruz

New Member
Joined
Aug 25, 2015
Messages
4
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
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:

Code:
If LCase(Range("E19").Value) = "y" Then
so now the user can use Y or y and still get the prompt.
 

JCruz

New Member
Joined
Aug 25, 2015
Messages
4
Sorry, Steve. It doesn't work for me.

This is what I did.

I obviously am not doing it right.

Code:
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:

Code:
If LCase(Range("E19").Value) = "y" Then
so now the user can use Y or y and still get the prompt.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
Try it like this:

Code:
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

New Member
Joined
Aug 25, 2015
Messages
4
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!
 

Forum statistics

Threads
1,081,708
Messages
5,360,777
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top