Solved: Update a cell based on another cell.

[OdiN]

I have a simple spreadsheet.

I want to date/time stamp when a cell is updated.

So for example, when Cell A3 is updated, put date/time stamp in E3. The value should only update if A3 is updated, so it can't be just Date() in the cell.

It needs to do this for the entire sheet though, not just a few cells.

Thanks for the help!

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A3" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 3).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[OdiN]

Thanks!

Is there a way that this will work with locked cells?

I've tried enabling the ability to format cells when protecting the worksheet but that doesn't work. The end-user can't be able to change this value, but the VBA needs to be able to.

[OdiN]

I updated the code to unprotect, run, then protect. I would rather not do that but with such short code it shouldn't ever error out and leave the sheet unprotected.

Of course I protected the code so nobody can look at it.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A3" '<== change to suit

On Error Goto ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Unprotect
With Target
If .Value <> "" Then
.Offset(0, 3).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If

ws_exit:
Me.Protect
Application.EnableEvents = True
End Sub


this way if it errors it protects it again.

[OdiN]

Thanks, I had already put the protect code in the error handler.

Only other way I can think of getting around that is someone doing a break but there's no way they are fast enough for such a quick operation.