Create a timestamp when a cell is updated

  • Thread starter Thread starter Victoria Chin
  • Start date Start date
V

Victoria Chin

Hello all,

I need help creating a timestamp that only updates when a
record in another cell is updated and does not recalculate
when you close or open the worksheet or hit F9 etc..

Example: The record in A1 is changed, it's detected and a
timestamp is generated in A2 noting when it was changed.

This is how I created the timestamp, I created the
following Macro called "Timestamp":

=now() in cell A2 (I click green X generating a value)
I Copy and without moving to a new cell I choose Paste
Special. I choose the Values Box under Paste and choose
None under Operation

The above works great. I've even hooked it to a button.
The Problem I have is hooking it to a function to monitor
cell A1 for changes and create a timestamp automatically.

Can anyone help me?

Thanks and Hugs!

Vicky
 
Vicky

you need to put your code in a Worksheet_Change event in the sheet you want
to monitor

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
' check that cell A1 is being changed
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
'check that cell A2 is empty
If Range("A2") <> "" Then Exit Sub
' check to see if you're blanking the cell
On Error Resume Next
If Target.Value = "" Then Exit Sub
On Error GoTo 0
' set the date
Application.EnableEvents = False
With Range("A2")
.NumberFormat = "dd/mm/yyyy"
.Value = Now()
.Columns.AutoFit
End With
Application.EnableEvents = True
End Sub

Regards

Trevor
 
Thank you for your help Trevor.

Vicki
-----Original Message-----
Vicky

you need to put your code in a Worksheet_Change event in the sheet you want
to monitor

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
' check that cell A1 is being changed
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
'check that cell A2 is empty
If Range("A2") <> "" Then Exit Sub
' check to see if you're blanking the cell
On Error Resume Next
If Target.Value = "" Then Exit Sub
On Error GoTo 0
' set the date
Application.EnableEvents = False
With Range("A2")
.NumberFormat = "dd/mm/yyyy"
.Value = Now()
.Columns.AutoFit
End With
Application.EnableEvents = True
End Sub

Regards

Trevor





.
Click to expand...
 
Back
Top