How to save the date in EXCEL?

Microsoft Office excel 2007 home & stude...
August 14, 2010 at 02:05:33
Specs: Windows XP
When I write something in cell A1, is it possible to get the date I changed this displayed in cell A2? So everytime I update cell A1, I can always see when it was last changed?

See More: How to save the date in EXCEL?

Report •

August 14, 2010 at 03:40:56

The answer is 'Yes', but only if you use Visual Basic.

Right-click on the name Tab of the worksheet where you want this to happen.
Select 'View Code'
In the large VBA window enter this:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHnd

'disable events so that changes made by this code
'do not re-trigger it
Application.EnableEvents = False

'test if changed cell is the one we are interested in
'must use the $ signs in the cell address
If Target.Address = "$A$1" Then
    'place the time in the cell one row below it
    Target.Offset(1, 0).Value = Date
    'format the cell
    Target.Offset(1, 0).NumberFormat = "dd/mmm/yy"
    're-enable events
    Application.EnableEvents = True
End If
Exit Sub

'error handler
're-enable events
Application.EnableEvents = True
End Sub

From the VB window menu bar click File - Save
Use Alt+f11 keys (the Alt and function key 11 pressed together) to return to the main Excel window.

This code will respond to Excel's change event. Whenever data in a cell is changed, this code will run. The code tests to see if the changed cell was the one to respond to, and if so, it places the date in the cell in the row below the changed cell.

Note that there is no test for or warning that the cell may already contain data.


Report •

August 14, 2010 at 20:02:00

Exactly what I was thinking about!

Im not used to Visual Basic, any good places on the net to start learning about using VB in Excel?

But thank you very much!

Report •

Related Solutions

Ask Question