Multiple Timestamps

Hello everyone,

I posted this question in the formula thread, but I will revive it here with the hopes that someone can assist, as my concern is mostly VBE based.

I've got one timestamp working on my sheet, but I would like to add another. The two codes should trigger whenever a person enters data into column A, or column J, so I'm trying to get the code to work for two columns.

Here's what I have so far:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A8:A9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy h:mm:ss AM/PM"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
__________________________________________________ ____
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("J8:J9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy h:mm:ss AM/PM"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub


When I enter in the J column, I get an error reading "Compile Error: Ambiguous name detected: Worksheet_Change". I'm not really sure why I'm getting this error, but I have a feeling it's easy to fix. The first one works quite well, but only when I don't have any of the code for the second one entered.

Any ideas?

Thanks in advance!

as far as i know, you can only have one "change event code sub, with as many events in it as you wish.
the following should work

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A8:A9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy h:mm:ss AM/PM"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
remove the next 3 lines
'End Sub
'__________________________________________________ ____
'Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("J8:J9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy h:mm:ss AM/PM"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

there is probably a much "cleaner" / efficient way to do it (any suggestions are appreciated), but this should be ok

let me know how you get on

Originally Posted by peejay

as far as i know, you can only have one "change event code sub, with as many events in it as you wish.
the following should work...there is probably a much "cleaner" / efficient way to do it (any suggestions are appreciated), but this should be ok let me know how you get on

Excellent! Thank you so much!

One quick note, when the time is stamped, it produced a string of octothorpes ("######"). Is there an easy way to get them to display the actual timestamp without having the cell in question selected?

Thanks so much for the help!

no problem

the ####### is because the format you have selected does not fit the column width

either make the column wider or change the format

.NumberFormat = "dd mmm yyyy h:mm:ss AM/PM"
this gives a day,month,year,hour,minutes and seconds

.NumberFormat = "h:mm:ss AM/PM"
this gives hours,minutes and seconds

hope this helps