excelvbamsgbox

VBA - Exiting out of MsgBox()


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.

enter image description here

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

Solution

  • 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