1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Adding Date and Time Last Modified to an Excel worksheet

Discussion in 'Business Applications' started by LizzardLen, Nov 2, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. LizzardLen

    LizzardLen Thread Starter

    Joined:
    Nov 2, 2008
    Messages:
    2
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Hi there,

    Define "change". As in, any change whatsoever? And did you use the workbook before save event? Use a workbook event to get what you want, just use the workbook sheet change event (still in ThisWorkbook module), and be sure you change the activesheet reference (in your linked thread) to the sheet object name (in the parenthesis of the event, I believe it is "Sh"). That should pretty much be it. If you need more help, post your code and tell us exactly what you are looking to do in a little greater detail.

    HTH
     
  3. LizzardLen

    LizzardLen Thread Starter

    Joined:
    Nov 2, 2008
    Messages:
    2
    Thanks so much for quick replies.

    Techtalk: I think that function would be volatile and always update whether a worksheet is edited or not.

    Zack: Originally (based on the prior thread) I had:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    For Each Sheet In ThisWorkbook.Sheets
    Sheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
    Next Sheet
    End Sub

    Which changed the date for all worksheets whether changed or not.

    Using your suggestion:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Sh.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
    End Sub

    This does what I want. However, it would run constantly while working on a spreadsheet and I guess slowing it down. I'd like something more elegant (the frustrated pascal 1.01 programmer in me) such as:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    For Each Sheet In ThisWorkbook.Sheets
    If Dirty
    Sheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
    Next Sheet
    End Sub

    Is there a way of implementing "If dirty"?
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Yeah, by using a Workbook_SheetChange event. LOL! No real elegant way of doing that without having some kind of code running somewhere. And in Excel, there just isn't that many options.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/765388