Page 1 of 5 123 ... LastLast
Results 1 to 15 of 64
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last save date of a worksheet (not workbook) (2000/sp3)

    Greets to all.

    A search only pulled up information about the last date the entire workbook was saved...

    I'd like something a bit more advanced; want each worksheet to have its own date of last modification; preferably both date and time.

    I've got one workbook that keeps track of progress across multiple active activities at the office, and need each activity (each on its own worksheet) update tracked seperately.

    Any takers for this challenge? [img]/forums/images/smilies/smile.gif[/img]

    thanks so much,
    ..dane

  2. #2
    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: Last save date of a worksheet (not workbook) (2000/sp3)

    Add this code to the thisworkbook object in VB

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    Sh.Range("a1").Value = Now
    Application.EnableEvents = True
    End Sub</pre>


    It will add the date/time in cell A1 (change as desired) of the sheet that has changed.

    Steve

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

    Re: Last save date of a worksheet (not workbook) (2000/sp3)

    Steve's solution is better

    One way would be to set aside a cell on each worksheet to contain the last modified date/time. Let's say this is A1 (in reality, probably not a good choice)

    Create a macro in a standard module (inserted in the Visual Basic Editor by selecting Insert | Module). Type or copy the following code into it:

    Public Sub UpdateModifiedDateTime()
    Range("A1") = Now
    End Sub

    Double click each worksheet object in turn in the Project Explorer in the Visual Basic Editor. Type or copy the following code into it:

    Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateModifiedDateTime
    End Sub

    Note: the cell will only be updated if the user changes something in the worksheet. Formulas automatically recalculating, such as =TODAY() or =RAND() will not trigger the Worksheet_Change event.

  4. #4
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last save date of a worksheet (not workbook) (2000/sp3)

    Steve/Hans,

    Thanks so much! Works like a charm.

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Last save date of a worksheet (not workbook) (2000/sp3)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Dane

    To add to the wonderful replies you already got, please keep in mind that you can't save a worksheet without saving the workbook it is in. So that is why the workbook always gets its date and time of last saved/modified, and all its worksheets will have that same date/time.

    Just to dot the Is and Cross the Ts and possibly put tails on the Qs.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  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: Last save date of a worksheet (not workbook) (2000/sp3)

    The routine I did, does not due it based on save.
    It updates when any sheet is changed and it updates it only for that sheet.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last save date of a worksheet (not workbook) (2000/sp3)

    I second Steve's solution, and is what I am using. It operates based on the "change" routine, not the "save" routine. Therefore, I don't care how many times the workbook is saved, only the date of last change on each worksheet. Steve's solution works absolutely perfectly; assuming Macros are enabled. Still not sure why that is required; is VBA code considered a macro?

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

    Re: Last save date of a worksheet (not workbook) (2000/sp3)

    >> is VBA code considered a macro?

    Yes, you get a macro warning if the workbook contains VBA code, and/or a userform (created in the Visual Basic Editor), and/or ActiveX controls in the workbook itself, i.e. controls inserted from the Control Toolbox.

  9. #9
    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: Last save date of a worksheet (not workbook) (2000/sp3)

    Yes VBA is the macro code. If macros are disabled this code (and any other code) will not run.

    Steve

  10. #10
    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: Last save date of a worksheet (not workbook) (2000/sp3)

    In addition:
    inserting a module and not adding any code will also trigger the warning.

    Steve

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

    Re: Last save date of a worksheet (not workbook) (2000/sp3)

    Steve,

    That is true. The mere presence of a (standard) module, even if empty, makes Excel include the components needed to handle macros in the workbook. These components are what cause the macro warning.

  12. #12
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last save date of a worksheet (not workbook) (2000/sp3)

    Steve,

    A followup to your suggestion:

    <hr><pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    Sh.Range("a1").Value = Now
    Application.EnableEvents = True
    End Sub</pre>

    <hr>

    With this code, the "undo" feature seems to not work... the undo button remains greyed out...

    Any ideas how to keep its functionality and still have some form of per-worksheet timestamp?

    thanks,
    ..dane

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

    Re: Last save date of a worksheet (not workbook) (2000/sp3)

    Running any VBA code clears the Undo buffer. Perhaps a future version of Excel will support undoing of macro actions - Microsoft word does have that.

    See Undoing a VBA Subroutine for ideas how to get around it for a specific macro. But providing a general undo would be a major undertaking.

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Last save date of a worksheet (not workbook) (2000/sp3)

    Steve,

    2 quick questions:
    1. If I wanted to format the value of the result, how would I do that. Apparently sh.range("a1").format doesn't seem to work. In particular, I'd like the result to be formatted w/o time (probably something like "mmm d, yyyy" or "m/d/yy" would be fine) and maybe format the font to be the same as the cell fill (making it invisible).

    2. If I wanted to capture the save-sheet date as part of the sheet's header or footer, how would I do that?

    TIA

    Fred

  15. #15
    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: Last save date of a worksheet (not workbook) (2000/sp3)

    How about this?

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    With Sh.Range("a1")
    Application.EnableEvents = False
    .Value = Now
    Application.EnableEvents = True
    .NumberFormat = ";;;"
    Sh.PageSetup.CenterHeader = "Sheet Modified: " & format(date, "mmm d, yyyy")
    End With
    End Sub</pre>


    It hide the contents of A1 and adds/formats the center header the date (change as desired).It is better to not display it then to color it the background color.

    Steve

Page 1 of 5 123 ... LastLast

Posting Permissions

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