CODEWITHSUNDEEP
vbaexcel
DANNYJ1
DANNYJ1

Reputation: 31

Worksheet Change add Now() without Automatically updating

Hello I am very new to VBA but I was hoping someone can help. I am trying to create a timestamp using the code below. But I can't figure out how to copy it for the next row. 

 Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 And Target.Row = 2 Then
    If Target.Value = "" Then
      Cells(2, 4).Value = ""
    Else
      Cells(2, 4).Value = Format(Now, "H:mm:ss AM/PM")
    End If
  End If
End Sub

It works fine for that 1 cell. But how can I repeat this when I enter something in A3,A4..so on. This is probably a very basic question but I am a total newbie!

Upvotes: 2

Views: 103

Answers (3)

user3598756
user3598756

Reputation: 29421

just to throw in a one-liner:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then Cells(Target.Row, 4).Value = IIf(Target.Value = "", "", Format(Now, "H:mm:ss AM/PM"))
End Sub

NOTE:

with _Change events you normally want to add code lines that set Application.EnableEvents to False before code lines that change something (cells content in this case) and to then back to True once you're done with the changes. This, not to have the event handle trigger itself in a infinite loop

In this case such an Application.EnableEvents handling isn't necessary because your are changing cells in column 4 and the _Change event handler would effectively do something only for changes in column 1

Upvotes: 0

0m3r
0m3r

Reputation: 12499

This should do it,

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A99999")) Is Nothing Then
        If Target.Value = "" Then
            Cells(2, 4).Value = ""
        Else
            Cells(2, 4).Value = Format(Now, "H:mm:ss AM/PM")
        End If
    End If
End Sub

Or else you wanna add on every offset cell

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A99999")) Is Nothing Then
        If Target.Value = "" Then
            Target.Offset(0, 3).Value = ""
        Else
            Target.Offset(0, 3) = Format(Now, "H:mm:ss AM/PM")
        End If
    End If
End Sub

Worksheet.Change Event (Excel)

Range.Offset Property (Excel)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

Remove the check for row 2 and use Target.Row:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1  Then
    If Target.Value = "" Then
      Cells(Target.Row, 4).Value = ""
    Else
      Cells(Target.Row, 4).Value = Format(Now, "H:mm:ss AM/PM")
    End If
  End If
End Sub

Upvotes: 2

Related Questions