Add "Now" if something is added to a cell

Question

I would not like this code to execute when the cell is empty. This code adds Now when I try to delete the contents of the cell.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Column = 1 Then
        Range("E" & Target.Row) = Now()
    End If
End Sub

score 3 · Accepted Answer · edited May 23 '17 at 12:31

Is this what you are trying?

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    If Target.Cells.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

    If Not Intersect(Target, Columns(1)) Is Nothing Then
        If Target.Value <> "" Then Target.Offset(, 4).Value = Now
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

More explanation about Worksheet_Change can be found HERE

Edit:

I tried test did not work! Date did not show do not know where wrong! – Horby 6 hours ago

The above code is for one worksheet and should be pasted in the relevant sheet code area. If you want to make it work for all worksheets then use the below code and paste it in the ThisWorkbook code area

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error GoTo Whoa

    If Target.Cells.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

    If Not Intersect(Target, Columns(1)) Is Nothing Then
        If Target.Value <> "" Then Target.Offset(, 4).Value = Now
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

I tried test did not work! Date did not show do not know where wrong! — Horby, Sep 13 '15 at 00:03
Yes, working now! thank you. if i want code work after line 10? — Horby, Sep 13 '15 at 22:02
Change `If Not Intersect(Target, Columns(1)) Is Nothing Then` to `If Not Intersect(Target, Columns(1)) Is Nothing And Target.Row > 9 Then` — Siddharth Rout, Sep 14 '15 at 11:04

Excel Hero · Answer 2 · 2015-09-13T20:18:09.290

0

To prevent your code from executing when the Target is empty, just make this small alteration:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Column = 1 And Target.Count = 1 Then
        If Len(Target) Then 
            Application.EnableEvents = False
            Range("E" & Target.Row) = Now()
            Application.EnableEvents = True
        End If
    End If
End Sub

edited Sep 13 '15 at 20:18

answered Sep 13 '15 at 06:07

Excel Hero

14,253
4
33
40

You should never use `Worksheet_Change` or `Workbook_SheetChange` like that :( – Siddharth Rout Sep 13 '15 at 06:45
Would you like to join me for a chat in `XLCODE`? – Siddharth Rout Sep 13 '15 at 07:08
thank you for code, i ask also if i want code work after specific line? why you dont like use Worksheet_Change or Workbook_SheetChange ?? – Horby Sep 13 '15 at 22:04

Add "Now" if something is added to a cell

2 Answers2