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
CodePudding user response:
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 "Infinite Loop"