Upcoming Power Excel Seminars
Likes Likes:  0
Results 1 to 7 of 7

Thread: Auto Update Time Stamp?

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Auto Update Time Stamp?

    Hello,

    I am trying to have a cell (or cells) that log when the last change was made to a shared workbook.

    I have tried a few options through VBA that I found through searching:
    Public Function ModDate()
    ModDate = Format(FileDateTime(ActiveWorkbook.FullName), "m/d/yy hh:nn ampm")
    End Function

    or
    Sub Last_Save()
    Range("E2").Value = Format(ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"), "m/d/yy hh:nn ampm")
    End Sub
    or
    Function LastModified() As Date
    LastModified = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
    End Function


    Each work, but only each time I run the function. I want this to run automagically whenever there is a change (so that my boss can see how current the workbook data are).

    I also tried to force the function to update:
    Sub UpdateCell(D2)
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:5"), "List1.UpdateCell"
    End Sub




    This didn't work at all.

    Please help.

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,187
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Auto Update Time Stamp?

    Welcome to the Board!

    You probably want to put your code in a Worksheet_Change event procedure, which is code that runs automatically when something is manually updated on the sheet.
    Something like this: https://www.mrexcel.com/forum/excel-...ime-stamp.html

    For more on event procedures, read this: http://www.cpearson.com/excel/events.aspx
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Update Time Stamp?

    I appreciate the quick reply. I should have noted that I am a complete noob in VBA, so the more elementary the explanation the better.

    That said, I'm not opposed to (attempt) learning. In a quick read of the second link, it notes that event procedures don't update on formula calculation. I am only really interested monitoring changes on the summary worksheet that calculates from the other sheets.

    So, can I make an event procedure that works on formula calculations somehow? Or can I have it read from the entire workbook?

    The formulas I posted above seemed to pull from the workbook's info/properties. I was hoping that I could continue in that direction (tracking last save is accurate enough). I thought/hoped that there was an easy way to make one of the above codes refresh on save.

    Does that make sense?

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,187
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Auto Update Time Stamp?

    There is a Worksheet_Calculate event, if you want to search/read-up on that.

    I am only really interested monitoring changes on the summary worksheet that calculates from the other sheets.
    So, that implies that there are changes happening to these other sheets. How exactly are these changes happening?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Update Time Stamp?

    This is a team project summary workbook. Each team member has a sheet. Each sheet is updated as projects progress. There is a summary page that calculates based on the combined team project progress. My boss wants to look at the summary page and be able to easily tell the last time anyone updated anything. It doesn't need to be member/sheet/project specific, she just wants to know if it has been lying fallow for two weeks.

    I thought that would be an easy thing to put in... but nothing is ever easy.

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,187
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Auto Update Time Stamp?

    Looks like there is some good information here that might be helpful: http://www.ozgrid.com/VBA/track-changes.htm

    Maybe this too: https://support.office.com/en-us/art...d-eeb23725bd15
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Update Time Stamp?

    I think I found what I needed.

    Adding "Application.Volatile" to the VBA code seems to do the trick.

    as in:

    Public Function ModDate()
    Application.Volatile
    ModDate = Format(FileDateTime(ThisWorkbook.FullName), "m/d/yy h:n ampm")
    End Function


User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com