VBA - Exiting out of MsgBox()

Question

I haven't worked with VBA for a while, so my skills are a bit rusty. I've managed to put together a small script that will update the Date Contacted and Date Modified column I have in my excel sheet If I were to change the respective cell in column A. For all cells in column A, I set up a List by using the Data Validation tool. List = A, B, C, D.

The script should input the current date in Date Contacted (column H) when one of the list items is selected. If the cell is changed to a new item a message box will ask if the user wants to commit the changes. If yes, the Date Modified (column I) will be updated. If no, Column A should revert back to it's original self and neither one of the Date columns should be updated.

There are no bugs in the code, but when prompted to answer yes or no, clicking "no" continues to loop back to the message box. I am stuck in the loop and unsure how to escape.

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
Dim Answer As Integer

For Each Cell In Target
    If Cell.Column = Range("A:A").Column Then
    
        If Cells(Cell.Row, "H").Value <> "" And Cell.Value <> "" Then
            Answer = MsgBox("Are you sure you want to make these changes?", vbYesNo)
            
            If Answer = vbYes Then
                Cells(Cell.Row, "I").Value = Int(Now)
            Else
                Application.Undo
            End If
                
        Else
            Cells(Cell.Row, "H").Value = Int(Now)
        End If
    End If
Next Cell
End Sub

You need to turn off events. Your `Application.Undo` is likely re-triggering the worksheet change event which is leading to a infinite loop — urdearboy, Jun 09 '21 at 18:06
Your application isn't crashing (unlike the linked thread), but the underlying problem and solution are the same. — BigBen, Jun 09 '21 at 18:24

score 0 · Accepted Answer · answered Jun 09 '21 at 18:11

0

You need to turn off events before making any changes to the worksheet via the macro. Changes trigger the event and when your event makes changes, it re-triggers the event resulting in a loop, or in your case, a conditional loop.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
        'Any code that makes changes to worksheet nested inside
    Application.EnableEvents = True

End Sub

answered Jun 09 '21 at 18:11

urdearboy

14,439
5
28
58

Ahh Thank You! I had those `Enable Events` functions present at one time, but they weren't doing anything. Thank you for the simple and concise solution! – fruitsmoothie Jun 09 '21 at 18:26

VBA - Exiting out of MsgBox()

1 Answers1