Yes/No MsgBox looping if No selected

First, I hope I am posting this correctly as this is my first post.


I'm trying to set this condition for various ranges of my worksheet to bring up a warning when anyone has entered data into a section they should not be altering. The warning should give them the option accept their changes or cancel which undo's the entry prompting the warning. The VBA below works great if you hit Yes, but it you hit no it undo's the entry but continues to loop the warning until you hit Yes. Any advice?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
Dim Ans As Variant
Dim Msg As String
     
Set rng1 = Range("A1:J354")
Set rng2 = Range("C355:C473")
Set rng3 = Range("H355:I473")
Set rng4 = Range("K1:GM473")


If Not Intersect(Target, Union(rng1, rng2, rng3, rng4)) Is Nothing Then
        Msg = "This cell should not be changed unless specifically intended.  Changes may result in errors throughout this workbook.  Are you sure you want to make changes?"
        Ans = MsgBox(Msg, vbYesNo)
        If Ans <> vbYes Then
            Application.Undo
        End If
    End If
End Sub

You could try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
Dim Ans As Variant
Dim Msg As String
     
Set rng1 = Range("A1:J354")
Set rng2 = Range("C355:C473")
Set rng3 = Range("H355:I473")
Set rng4 = Range("K1:GM473")


If Not Intersect(Target, Union(rng1, rng2, rng3, rng4)) Is Nothing Then
        Msg = "This cell should not be changed unless specifically intended.  Changes may result in errors throughout this workbook.  Are you sure you want to make changes?"
        Ans = MsgBox(Msg, vbYesNo)
        If Ans <> vbYes Then
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
        End If
    End If
End Sub

BSB

Perfect, that nailed it! Thanks for the quick help!

No problem at all. Happy to help.

BSB