Work Sheet Change Event

[ManUBlueJay]

I am trying to have excel log when any cell in Col E changes. I used the following Code

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 5 Then
        ActiveCell.Offset(-1, -2) = Now()
    End If
End Sub

It works perfect when I hit enter after a change. However when either a mouse click or the tab key is used to move out of the cell after the change it does not work.
Any help would be appreciated.

 

[Scott T]

How does it now work?

 

[ManUBlueJay]

Thanks, the problem was not the "Now" part.
It was when I hit enter the current active cell is 1 row down and -2 cols When I hit tab the cell that I want to have log is same row but the col is now -3

 

[Scott T]

The active cell changes when you enter(assuming you have Excel setup to move on enter) or tab or click away. The code is using the cell you tab or click into for the active cell and then offset from there.

try

Target.Offset(0, -2) = Now()

 

[Peter_SSs]

There are a few issues with your code:
1. The one you are asking about. The main problem is that you are using ActiveCell where I believe you should be using Target.
2. If, after entering a value in column E, the active cell is in row 1, the code will error.
3. If you enter a value in column E the code will put a value somewhere on the sheet. This action will (needlessly) re-call the worksheet change code again.
4. What do you want to happen if the user changes several cells at once in column E? Usually the answer would be to log the timestamp for each one.

So, here is how I would do it.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim Changed As Range, c As Range

 Set Changed = Intersect(Target, Columns("E"))
 If Not Changed Is Nothing Then
  Application.EnableEvents = False
  For Each c In Changed
    c.Offset(0, -2).Value = Now
  Next c
  Application.EnableEvents = True
 End If
End Sub

 

[ManUBlueJay]

Perfect. Thanks for contributing to my education

 

[ManUBlueJay]

Thanks Peter. What Scott contributed also works, which is better, his is simpler, I simply used Activecell incorrectly.

If Target.Column = 5 Then
Application.EnableEvents = False
        'ActiveCell.Offset(-1, -2) = Date
        Target.Offset(0, -2) = Date
Application.EnableEvents = True
    End If
End Sub

 

[Peter_SSs]

Thanks Peter. What Scott contributed also works, which is better, his is simpler, I simply used Activecell incorrectly.

If Target.Column = 5 Then
Application.EnableEvents = False
        'ActiveCell.Offset(-1, -2) = Date
        Target.Offset(0, -2) = Date
Application.EnableEvents = True
    End If
End Sub

As a couple of experiments I'd suggest:
- Select E5:J5 and then hit the Delete key.
- Select D10:E12, type an x and press Ctrl+Enter. You will have altered cells in column E but not got a time stamp.
You may not be likely to do those things of course, I'm just pointing out that it isn't as robust. Your choice though.

In your circumstances though I didn't need to deal with each cell separately (I grabbed some generic code used elsewhere). Could have been:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim Changed As Range

 Set Changed = Intersect(Target, Columns("E"))
 If Not Changed Is Nothing Then
  Application.EnableEvents = False
    Changed.Offset(0, -2).Value = Now
  Application.EnableEvents = True
 End If
End Sub