Automatic output of Date, Time & User (via VBA) on transactions is being erroneously updated when rows are deleted

[Collie]

Hopefully my title hasn't been too confusing.

I have inserted VBA code into a worksheet to record when transactions were entered and by whom. One of the processes that run over month end determines where the transactions are to end up, and moves certain transactions by copying them to the new output and deleting the original. I have found that the deletion results in the next transaction to be erroneously stamped with the current date time and user.

Is there a solution? or a way to prevent the code from running when rows are being deleted?
this is the code I have in the worksheet:-


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTableRange As Range
Dim myDateRange As Range
Dim myTimeRange As Range
Dim myUser As Range

'code to automatically stamp trans with date, time & user
Set myTableRange = Range("B2:I10000")

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

Set myDateRange = Range("BL" & Target.Row)
Set myTimeRange = Range("BM" & Target.Row)
Set myUser = Range("BN" & Target.Row)

myDateRange.Value = DateValue(Now)
myTimeRange.Value = TimeValue(Now)
myUser.Value = Application.UserName
End Sub

 

[Collie]

Hi again

I've still been searching the web looking for anything I can use as a solution..... Is it possible to test for a value (event) that has triggered the worksheet change? For example, if I could determine between changes, additions and deletions, and then not update the row on deletion, I would be alright.

Thanks in advance

 

[Norie]

If entire rows are being deleted you could check the no of columns in Target.

If Target.Columns.Count = Columns.Count Then
    ' row deleted
    Exit Sub
End If

 

[Collie]

Hi Norie, I appreciate your reply, but would that identify differently for additions and changes compared to deletions?

 

[Norie]

It would be for additions/deletions of entire rows.

Whenever you add/delete entire row(s) you are changing all the columns in the row(s).

 

[Collie]

Thankyou Norie. tested through and through, and although I still don't fully understand the logic, I cant fault it and it is performing perfectly over the two thousand plus transactions in my worksheet.