Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Sorry to have to post for this topic, but I cannot figure out how to use the Search feature on this new board.....my question is this: I want to use the NOW() function in a workbook, but I only want it to update the date & time if the workbook is changed. It should not update date & time if the workbook is opened as a Read-only document.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See Help Topic -> Searching Topics and Posts.

    Instead of a formula, you can use code in the Workbook_BeforeSave event procedure in the ThisWorkbook module:
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      Worksheets("MySheet").Range("A1") = Now
    End Sub
    Change this to use the worksheet and cell that you want to populate.

    Note: the cell will be modified even if the user uses Save As and cancels it.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could also just look at the workbook properties?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you, Hans & Rory......I am not sure what you mean, Rory, when you say that 'you could also just look at the workbook properties'......I assume you mean that you mean that viewing of the workbook contents could be restricted other than by making it read-only but I don't know how to do that...would it leave the workbook fully viewable, but completely locked (other than if I use a password)?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Actually I just meant that the workbook properties include the last save time of the workbook as well as who saved it. I assumed that was what you were interested in?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Rory.....not so much that...what I have is a workbook that will be uploaded to a Sharepoint site for people to view...it is a work schedule. If I show the date and time of the last change to the schedule by using NOW(), whenever someone opens the workbook (even as a read-only), that date and time will update and people won't be able to know that actual date/time of the last change.

    I was hoping to get a NOW() formula (or some such formula) that would only change if the workbook was opened for editing (to make schedule changes) and then SAVED (and posted back on the Sharepoint site. That way, people would see that the date/time of the last change (eg: Tues March 31/09 @ 2:00pm) and they would know that they are reading a fairly current schedule.....whereas, if it updates upon every viewing, it may SEEM current, when in fact the data is not current.....does this make sense to you?

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If the changes are manual (rather than as the result of formulas), you would use the Workbook_SheetChange event to write the current time to a cell, as in Hans' suggestion but if you use this event rather than the BeforeSave, then the time will only be updated if they actually save the file.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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