Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Last Modified Macro (Excel 2000 SR-1)

    Hi,

    Is it possible to embed a macro or VB script in a cell that will display the date/time stamp that the workbook was last modified? I know how to do this with Javascript on a webpage, but not sure of the syntax to do this in Excel.

    Any help would be greatly appreciated.

    Thanks,

    DG

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

    Re: Date Last Modified Macro (Excel 2000 SR-1)

    1. You can create a custom worksheet function:

    Function LastSaveDateTime() As Date
    LastSaveDateTime = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
    End Function

    If you put this function in a module in the workbook itself, or in an add-in, you can use it in a formula in a cell:

    =LastSaveDateTime()

    If you put it in a module in your personal macro workbook Personal.xls, you must specify it as follows:

    =Personal.xls!LastSaveDateTime()

    2. You can also create a macro that will put the last saved date and time in the active cell as a fixed value.

    Sub SetLastSaveDateTime()
    ActiveCell = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
    End Sub

    You can put this macro in a module in the workbook itself, or in an add-in, or in your personal macro workbook Personal.xls.

    Remark: in both cases, you must set the date/time format of the target cell yourself.

  3. #3
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Last Modified Macro (Excel 2000 SR-1)

    Hans,

    Thanks for the help, and my apologies for the delay in responding. Didn't get a chance to review your post until just a few minutes ago.

    I tried the first option you suggested, and copied the code you provided into a new module, then set my formula in the cell. It seemed to work fine at first, as the current date/time immediately appeared. Unfortunately, if I make a change to this document, save it, close and reopen, upon reopening, the date/time doesn't update. It still shows the original date/time that appeared upon entering the formula in the cell.

    Any ideas?

    Thanks again,

    DG

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date Last Modified Macro (Excel 2000 SR-1)

    This might work better. The problem with user-functions is that sometimes, excel doesn't realize that they need to be recalculated.

    Also see Chip Pearson's site. Excel does not use this builtin property. You can set it if desired, but if you are going to do that you might as well use the approach below.

    Add this code to the thisworkbook object of the workbook of interest:

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("Sheet1").Range("a1").Value = Now
    End Sub</pre>


    And right before you save the workbook, sheet1!A1 will get the current date and time. Change the sheet and cell as appropriate. Change the format of the cell as desired.

    Steve

  5. #5
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Last Modified Macro (Excel 2000 SR-1)

    Steve,

    Thanks, that seems to do the trick. The only snag is that I can't lock the cell, as it keeps the sub from running by indicating I'm trying to make changes to a protected cell. Not a show-stopper tho'.

    Thanks for the help!

    DG

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date Last Modified Macro (Excel 2000 SR-1)

    Try this (new line to have macros ignore protection)

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("Sheet1").Protect userinterfaceonly:=True
    Worksheets("Sheet1").Range("a1").Value = Now
    End Sub</pre>


    Steve

  7. #7
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Last Modified Macro (Excel 2000 SR-1)

    Steve,

    Worked like a charm. Thank you once again for the helping hand!

    Regards,

    DG

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date Last Modified Macro (Excel 2000 SR-1)

    You are welcome. Happy to be able to help.

    Steve

Posting Permissions

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