Automatically enter a "date modified" in a cell

[guerreroca]

I'm trying to find a way to get a "modified date" or "saved date" automatically entered into a cell in a worksheet, but I can't figure this out. I've seen numerous websites give VB code to do this, but I must be doing something wrong because a date never appears in the cell I select. I'm using Excel 2007. Can someone please show me step by step how to do this?

I need it to look like: Cell B2 says "Updated" and the date is actually in cell B3, so it would look like "Updated 07-14-2010". Thank you.

 

[lenze]

Hi and welcome to the board!!!
There are several ways to do this. Do you really need it in a cell? Or would the footer work? You can also use one cell (B2) for both updated and the date?? Think about these options!! For your example, find the ThisWorkBook module in the VBE(ALT+F11) and paste this code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
If Me.Saved = False Then Sheet1.Range("$B$4") = Date
    Application.EnableEvents = False
    Me.Save
    Application.EnableEvents = True
End Sub

lenze

 

[guerreroca]

Hi Lenze,

I don't understand this. Am I supposed to change something in this code? I put this in VBE and saved it, then changed a cell, saved the workbook, even closed and reopened it, but no date shows up in any of the 4 worksheets in this workbook. I did change the ("$B$4") to ("$C$2") because that is the cell I want the date to show up in. (Not B2 as I originally said).

Thank you for your help!