Create date stamp when change detected

user_16980502 0
Viewed 44

This script ends up being a runtime error when more than one cell in the target is modified.

I basically need to be able to make multiple changes at once and still have the date stamp work.

I'm still new to these sorts of scripts, any help will be appreciated.

Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Auto Date
    Dim cell As Range
    
    'Unprotecting Text Submission tool tab
    wstextsubmissiontool.Unprotect "Abc123"
    
    For Each cell In Target
        If cell.Column = Range("E:E").Column Then
            If cell.Value <> "" Then
                Cells(cell.Row, "C").Value = Now
            Else
                Cells(cell.Row, "C").Value = ""
            End If
        End If
    Next cell

    'protecting Text Submission tool tab
    wstextsubmissiontool.Protect "Abc123"

End Sub
vba
1 Answers

The issue is that, by changing the cell that contains the time, you are changing the worksheet, so Excel wants to run your code to change the cell that contains the time... so basically the error is to prevent an infinite loop.

The way around it is to disable events at the start of your Worksheet_Change procedure with Application.EnableEvents = False. Just be sure to re-enable events at the End of the procedure (or also if you Exit the procedure early for some reason).

A simplified example (excluding your password protection) is:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  Sheets("sheet1").Range("a1") = Now()
  Application.EnableEvents = True
End Sub

More Info:

"Infinite Loop" (source)

user_81127760
Related