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