Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date File Modified (2002)

    Is there a method by which a date can be automatically updated each time the file is changed. For example, if I have todays date in cell A1 and then I open the file and modify it tomorrow, is it possible to automatically change the date to tomorrows date when this change takes place?

  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: Date File Modified (2002)

    <P ID="edit" class=small>(Edited by sdckapr on 01-Aug-05 10:26. Oops, forgot to disable events, to prevent recursive calls...)</P>You could add some code, like this, to the thisworkbook object:

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.enableevents = false
    Worksheets(1).Range("A1").Value = Date
    Application.enableevents = true
    End Sub</pre>


    Change the sheet to store the date as appropriate. Beware, any change to any cell will change the date. Having code like this in the workbook, will clear the UNDO stack (you will no longer have undo).

    Steve

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

    Re: Date File Modified (2002)

    You can create a Workbook_BeforeSave event handler:
    - Activate the Visual Basic Editor (Alt+F11)
    - Double click ThisWorkbook.
    - Type or copy/paste the following code, modifying it as needed:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    WorkSheets("Sheet1").Range("A1") = Date
    End Sub

    - Each time the workbook is saved, the value in cell A1 on Sheet1 will be updated; if the workbook is closed without saving it, the value will remain unchanged.

  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 File Modified (2002)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>A before save makes more sense than a change event.

    That is what I get for answering the question instead of determining the "real question" <img src=/S/blush.gif border=0 alt=blush width=15 height=15>Steve

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date File Modified (2002)

    Hans, thanks. I have tried, but must be doing something incorrectly.

    I have the target file open. Select Alt+f11 which opens VB. I paste the code into the editor then go back to the file and make change, save but don't see anything happen in A1. I may be missing it by not double clicking "ThisWorkbook" but don't see that as an option...

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

    Re: Date File Modified (2002)

    It won't work if you put the code in a standard module, it MUST be in ThisWorkbook. See screenshot below.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date File Modified (2002)

    Get this: Runtime error 9. Subscript out of range...

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

    Re: Date File Modified (2002)

    Do you have a worksheet named "Sheet1" in the workbook? If not, then you need to modify the code to change the sheet name to the sheet where you want the date inserted.
    Legare Coleman

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

    Re: Date File Modified (2002)

    In

    WorkSheets("Sheet1").Range("A1") = Date

    you must edit "Sheet1" to the name of an actual existing woprksheet in the workbook.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date File Modified (2002)

    Tried but still get error. Here is file.

  11. #11
    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 File Modified (2002)

    <P ID="edit" class=small>(Edited by sdckapr on 01-Aug-05 15:02. Added PS)</P>Try:

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("DetailFinancialAnalysis").Range("A1") = Date
    End Sub</pre>


    Steve
    PS. Or are you after:
    Worksheets("DetailFinancialAnalysis").Range("B2") = Date

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

    Re: Date File Modified (2002)

    I don't understand - there is no code in your workbook. No wonder it doesn't work. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  13. #13
    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 File Modified (2002)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>You did not attach a workbook...

    Steve

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

    Re: Date File Modified (2002)

    Your workbook did not have any code in it, so I can not tell you what you were doing wrong. Try the attached workbook.
    Legare Coleman

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

    Re: Date File Modified (2002)

    Thanks. I am having connection problems, and the attachment apparently got dropped when the link went down again.
    Legare Coleman

Page 1 of 2 12 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
  •