Worksheet Change Event

[nrage21]

This is what I'm currently using: The event triggers whenever a value i
enter manually anywhere in column "b" and a timestamp is added in colum
z.

My goal is to trigger this event when "pasting a new value" in colum
"b".

See the code that follows this example. I accidentally found it in thi
forum and I'm hoping it could be modified by an expert to meet m
requirement. TIA everybody!

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("b:b")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False

On Error GoTo errHandler:
With Target.Offset(0, 24)
.Value = Now 'date
.NumberFormat = "mm/dd/yyyy hh:mm"
End With

errHandler:
Application.EnableEvents = True
End Sub

The following code works "if" information is pasted in a range.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim changecolumn As Integer
Dim changerow As Integer
Dim changeworker As String

On Error GoTo errhandler:
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each cell In Target
changecolumn = cell.Column
changerow = cell.Row
changeworker = 0
If changecolumn >= 89 Then Exit For
Call change_flag(changerow, 1000, Me, changeworker)
Next

errhandler:
Application.EnableEvents = True
End Su

 

[Frank Kabel]

Hi
try
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Intersect(Target, Me.Range("b:b")) Is Nothing Then Exit
Sub
Application.EnableEvents = False
Application.ScreenUpdating = False

On Error GoTo errHandler:
For Each cell In Target
With cell.Offset(0, 24)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm"
End With
Next
errHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[nrage21]

Thanks Frank,

However, 2 things began to happen:

1) when I copy and paste one row with 3 columns of info from on
workbook to the other, timestamp is added from column z tru column ab.

If I paste 8 cells, then timestamp is added from z tru column ag (
columns)


2) If I clear or delete an entire row, then timestamp is added fro
column z tru column iv (the last column in the workbook)

I guess the target is still not specific enough... I don't know

- Larry -
VBA Amateu

 

[nrage21]

no solution?

Can the code be disable when deleting rows

 

[Dave Peterson]

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myRngToCheck As Range

Set myRngToCheck = Me.Range("b:B")

If Intersect(Target, myRngToCheck) Is Nothing Then Exit Sub

On Error Resume Next
Application.EnableEvents = False
For Each myCell In Intersect(Target, myRngToCheck).Cells
With myCell
If IsError(.Value) Then
'do nothing???
ElseIf .Value = "" Then
.Offset(0, 24).ClearContents
Else
.Offset(0, 24).Value = Now
.Offset(0, 24).NumberFormat = "mm/dd/yyyy hh:mm"
End If
End With
Next myCell
Application.EnableEvents = True

End Sub

Since you might be doing lots of things, this one just flies by any errors and
keeps going.