Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert current date (static) when file is modified (2003)

    I am VB-impaired (severely). But, I think this can be done. One of our users wants a cell to show the current date and time when the spreadsheet is modified. He wants it to be static so that it only changes when something in the spreadsheet is modified.

    How can I do this? I know you can click in the cell and press Ctl+; for the current date, but he wants it to be automatic AND to include the time. Also, he would really like to have the user's name inserted also. That way, as soon as anyone opens the workbook, he/she can see when the file was modified and who modified it.

    Help please.

    Thank you!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert current date (static) when file is modified (2003)

    Yes, depending on exactly what you want. The following code, placed in the worksheet change event routine in the module behind a worksheet will place the current date and time into cell A1 on that sheet.

    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Range("A1").Value = Now()
    End Sub
    </code>

    To place that code in the worksheet change event routine, copy the code and then right click on the worksheet tab and select "View code" from the pop up menu. Paste the code into the VBE code window. Now, any time a change is made to the worksheet, the current date and time will be put in cell A1.

    If you have a workbook with more that one sheet, and want to do this for all sheets, then you can put the code below in the workbook sheet change event routine.

    <code>
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Sh.Range("A1").Value = Now()
    End Sub
    </code>

    To put this code in the workbook sheet change event, in the VBE window, right click on the ThisWorkbook object in the project explorer window and select "View Code" from the pop-up menu. Paste the code into the code window there.

    You can change the code to store the date and time into any desired cell.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Insert current date (static) when file is modified (2003)

    This data is shown under File | Properties | Statistics - is that not want the user wants?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert current date (static) when file is modified (2003)

    Yes and no. He doesn't want to go anywhere to find the info. He wants to put it in cell A1 and make it prominent when he opens the worksheet.

    Hans, how can I also have the user name inserted? Either in A1 or B1 - it doesn't have to be all in one cell.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert current date (static) when file is modified (2003)

    Did someone mention my name? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    The solution provided by Legare will keep the modification date/time updated as the workbook is edited, but it will also disable the Undo function. If you only need the date/time the workbook was last saved, you could put the following code in the ThisWorkbook module:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("Sheet1").Range("A1") = Now
    Worksheets("Sheet1").Range("B1") = Application.UserName
    End Sub

    It will put the date/time in A1 and the username in B1 on Sheet1 (you can change the sheet name, of course).

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert current date (static) when file is modified (2003)

    Wow.. Sorry Legare... and Hans. Seems you look alike to me today! Thank you, Hans. I think that will work great.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert current date (static) when file is modified (2003)

    Do I look like a lizard? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Insert current date (static) when file is modified (2003)

    Hey, Legarehans! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Under some circumstances I have run into, such as macros that change the print header, or formulas containing time (=NOW() come to mind), users are prompted to Save the Workbook when they have not actively changed formulas or sheet content.

    I'm not sure how discriminating this macro needs to be, but I can envision confused users being prompted to save a Workbook that they do not beliveve they have changed, and therefore being regarded as the last user if they do save it.

    For that reason I'd be inclined to use Legare's macro, or for this user:

    Private Sub Workbook_Open()
    Application.Dialogs(xlDialogProperties).Show
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert current date (static) when file is modified (2003)

    Good point. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •