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

Topic

New Reply

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?

thanks so much,
..dane

Reply | Quote

Replies

Add this code to the thisworkbook object in VB

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

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

Steve

Reply | Quote

Steve/Hans,

Thanks so much! Works like a charm.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

>> 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.

Reply | Quote

>> 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.

Reply | Quote

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

Steve

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote

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

Steve

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Steve/Hans,

Thanks so much! Works like a charm.

Reply | Quote

Steve,

A followup to your suggestion:

[indent]

---

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

---

[/indent]

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

Reply | Quote

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.

Reply | Quote

If you only place the last save date in the header or footer, the undo stack remains intact. As soon as you add the date to e.g. a defined name as I showed in another post, or to a cell, it gets flushed.

Reply | Quote

If you only place the last save date in the header or footer, the undo stack remains intact. As soon as you add the date to e.g. a defined name as I showed in another post, or to a cell, it gets flushed.

Reply | Quote

---

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.

---

Hans,

I looked at the example you referenced. And I agree, a generic “undo” is a major undertaking indeed. For my purposes, I am fine with supporting a “false” undo routine that doesn’t really do anything, just supports undoing changes prior to my event handler changes..

I updated my two routines as follows:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

  ' Whenever a change is made to any worksheet,
  ' Update it's "Last Updated..." field
  With Sh.Range("A2")
    ' We don't want a recursive event...
    Application.EnableEvents = False
        
    ' Sheet changed... update the "last updated" date
    .Value = FormatDateTime(Now, vbLongDate) & " at " & FormatDateTime(Now, vbShortTime)
    
    ' Re-enable events... we're done.
    Application.EnableEvents = True
        
    ' Hide cell data...
    .NumberFormat = ";;;"
  End With
    
  ' We don't really support "UNDOing" the last timestamp,
  ' but we do want to support undoing things done BEFORE that timestamp
  ' So we'll just go ahead and define a generic "undo" callback
  Application.OnUndo "Undo Timestamp", "UndoTimestamp"

End Sub

Public Sub UndoTimestamp()

  ' Don't actually do anything, just support the undo operation...

End Sub

When I update a sheet, the undo function indeed does not go away anymore. However, when I try to actually undo something, I get the following error:

The macro "'!UndoTimestamp" cannot be found

I’ve tried prefixing the sub with “Public” and have also tried putting it in the active workbook’s code module. Am I overlooking something obvious?

thanks!
..dane

Reply | Quote

Adding an Undo only adds that particular undo to the stack (i.e. the call to the sub). The rest of the stack is cleared. There is NO way around that, apart from removing everything from your event code that clears the stack (as I wrote earlier if I recall correctly).

Reply | Quote

[indent]

---

Adding an Undo only adds that particular undo to the stack (i.e. the call to the sub). The rest of the stack is cleared. There is NO way around that, apart from removing everything from your event code that clears the stack (as I wrote earlier if I recall correctly).

---

[/indent]

Ah-ha… So I misunderstood the capabilities of the undo handler Hans recommended… it only provides a one-deep undo, not an undo which inherently preserves the rest of the undo stack…

Let me ask you; is there any way to update some public variable that an equation on a worksheet uses, insead of updating a worksheet cell directly? I wonder if that would be a way to update a timestamp without destroying the “undo” stack?

thanks,
..dane

Reply | Quote

You have to store it someplace that does not trigger the undo stack clear. As mentioned earlier you can add it to the header or footer and then retrieve it later (as long as someone does not manually change it). Adding it to a cell or a name will clear the stack.

Steve

Reply | Quote

Granted I’m fairly new to using Excel in advanced ways…

I understand that I need to store it in a location that doesn’t clear the undo stack. My question is, what locations are available to me that do not clear the undo stack, that are still accessible by equations within a worksheet? For instance, cell A1’s value might become:

= "Last Updated on " & 

If that can be a header/footer, great. If not, then I’m looking for another route…

thanks,
..dane

Reply | Quote

If you add this to the this workbook object (change format of the date/time as desired)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Sh.PageSetup.RightFooter = Format(Now, "mmmm d, yyyy h:mm am/pm")
End Sub

And add this function to a normal module:

Function GetRightFooter() As String
    GetRightFooter = ActiveSheet.PageSetup.RightFooter
End Function

You can add on each sheet a formula like:
= “Last Updated on ” &getrightfooter()

To get the last modified date, and not affect the Undo Stack.[Of course you can change to the header or use the left or center instead of the right.]. If someone manually changes the header, it will be overwritten when the worksheet is changed to reflect the changed date/time.

Steve

Reply | Quote

If you add this to the this workbook object (change format of the date/time as desired)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Sh.PageSetup.RightFooter = Format(Now, "mmmm d, yyyy h:mm am/pm")
End Sub

And add this function to a normal module:

Function GetRightFooter() As String
    GetRightFooter = ActiveSheet.PageSetup.RightFooter
End Function

You can add on each sheet a formula like:
= “Last Updated on ” &getrightfooter()

To get the last modified date, and not affect the Undo Stack.[Of course you can change to the header or use the left or center instead of the right.]. If someone manually changes the header, it will be overwritten when the worksheet is changed to reflect the changed date/time.

Steve

Reply | Quote

Steve,

Thanks for that suggestion. I’ve put the items in place as you mentioned, but it seems that the cell does not update even though the footer is correctly updating.. However, when I go to that cell and select the formula and hit [enter], it updates. It’s obviously an event trigger problem that the cell is not going and ‘grabbing’ the latest information in the footer. Any ideas? Again, sorry for my newbiness to excel.. I’m an Access guy..

I tried looking for some sort of refresh method; I found “Calculate” but when I tried to use it, it seems same cell in each worksheet would update to the active sheet’s footer, rather than only the active sheet’s cell… This is what I used in my sheetchange() event handler:

Worksheets(ActiveSheet.Name).Range("A1").Calculate

And it seems that even that doesn’t always seem to work the way I hope it would..

..dane

Reply | Quote

Or add
Application.Volatile to the UDF.

Reply | Quote

Jan–

Did as you suggested; no change. I just created a dummy workbook as a test to attach here… Am I just misunderstanding what is being suggested?

I’ve attached the dummy test book here…

Reply | Quote

It works fine for me if I change your line to this

Sh.PageSetup.RightFooter = “Last Modified on ” & Format(Now, “dd mmm yyyy”” at “”h:mm:ss am/pm”)

to get rid of the runtime error I get with XL97.

Steve

Reply | Quote

Steve,

Must be a difference between XL97 and XL2000… Even after the change you suggested. I change one sheet, and the A1 cell updates on BOTH sheets to the new date/time. Do I need to use some different approach on XL2000?

..dane

Reply | Quote

I didn’t realize that the problem was the function defining the “activesheet” so it gets updated incorrectly.

Try this, modify the function to have an argument that is a cell on the sheet.

Function GetRightFooter(rCell As Range) As String

    ' This routine is volatile...
    ' That is, it must be recalculated every time the function is called, 
    'not only when the input (cell) is changed...
    Application.Volatile
    
    ' Get the "last modified" timestamp from the page footer.
    GetRightFooter = rCell.Parent.PageSetup.RightFooter
    
End Function

Then change your formulas to:

=GetRightFooter(a1)

Steve

Reply | Quote

B-E-A-utiful!

Steve, thanks for that tip. I didn’t even think about passing a parameter and grabbing the calling-page from that parameter. Very nice. Works like a champ. And my undo-stack is preserved, too! This is a fabulous day.

thanks again for yours and everyone else’s help (Hans, Jan)

..dane

Reply | Quote

B-E-A-utiful!

Steve, thanks for that tip. I didn’t even think about passing a parameter and grabbing the calling-page from that parameter. Very nice. Works like a champ. And my undo-stack is preserved, too! This is a fabulous day.

thanks again for yours and everyone else’s help (Hans, Jan)

..dane

Reply | Quote

I didn’t realize that the problem was the function defining the “activesheet” so it gets updated incorrectly.

Try this, modify the function to have an argument that is a cell on the sheet.

Function GetRightFooter(rCell As Range) As String

    ' This routine is volatile...
    ' That is, it must be recalculated every time the function is called, 
    'not only when the input (cell) is changed...
    Application.Volatile
    
    ' Get the "last modified" timestamp from the page footer.
    GetRightFooter = rCell.Parent.PageSetup.RightFooter
    
End Function

Then change your formulas to:

=GetRightFooter(a1)

Steve

Reply | Quote

Steve,

Must be a difference between XL97 and XL2000… Even after the change you suggested. I change one sheet, and the A1 cell updates on BOTH sheets to the new date/time. Do I need to use some different approach on XL2000?

..dane

Reply | Quote

It works fine for me if I change your line to this

Sh.PageSetup.RightFooter = “Last Modified on ” & Format(Now, “dd mmm yyyy”” at “”h:mm:ss am/pm”)

to get rid of the runtime error I get with XL97.

Steve

Reply | Quote

Works for me in both XL2000 and XL2002.

Reply | Quote

Works for me in both XL2000 and XL2002.

Reply | Quote

Jan–

Did as you suggested; no change. I just created a dummy workbook as a test to attach here… Am I just misunderstanding what is being suggested?

I’ve attached the dummy test book here…

Reply | Quote

Or add
Application.Volatile to the UDF.

Reply | Quote

Steve,

Thanks for that suggestion. I’ve put the items in place as you mentioned, but it seems that the cell does not update even though the footer is correctly updating.. However, when I go to that cell and select the formula and hit [enter], it updates. It’s obviously an event trigger problem that the cell is not going and ‘grabbing’ the latest information in the footer. Any ideas? Again, sorry for my newbiness to excel.. I’m an Access guy..

I tried looking for some sort of refresh method; I found “Calculate” but when I tried to use it, it seems same cell in each worksheet would update to the active sheet’s footer, rather than only the active sheet’s cell… This is what I used in my sheetchange() event handler:

Worksheets(ActiveSheet.Name).Range("A1").Calculate

And it seems that even that doesn’t always seem to work the way I hope it would..

..dane

Reply | Quote

You can store data in a Name, I use this a lot.

thisworkbook.names.add "savethis", "stuff to keep here"

Then you can refer to the Name ‘savethis’ (without the quotes) just like any other variable in formulas. If it’s a string you’re saving you can use the shortcut notation “[savethis]” (again no quotes) in your code. Try this in the Immediate window for the IDE. Type ?[savethis] and you’ll get what the Name contains.

If ‘savethis’ is referencing a cell, then you need to qualify it with

?names("savethis")

in the Immediate window to get the cell contents.

Deb

Reply | Quote

As mentioned earlier in the thread, whether you use a name or a cell, these will both clear the undo stack.

Writing to the footer/header will not clear the stack.

Steve

Reply | Quote

As mentioned earlier in the thread, whether you use a name or a cell, these will both clear the undo stack.

Writing to the footer/header will not clear the stack.

Steve

Reply | Quote

You can store data in a Name, I use this a lot.

thisworkbook.names.add "savethis", "stuff to keep here"

Then you can refer to the Name ‘savethis’ (without the quotes) just like any other variable in formulas. If it’s a string you’re saving you can use the shortcut notation “[savethis]” (again no quotes) in your code. Try this in the Immediate window for the IDE. Type ?[savethis] and you’ll get what the Name contains.

If ‘savethis’ is referencing a cell, then you need to qualify it with

?names("savethis")

in the Immediate window to get the cell contents.

Deb

Reply | Quote

Granted I’m fairly new to using Excel in advanced ways…

I understand that I need to store it in a location that doesn’t clear the undo stack. My question is, what locations are available to me that do not clear the undo stack, that are still accessible by equations within a worksheet? For instance, cell A1’s value might become:

= "Last Updated on " & 

If that can be a header/footer, great. If not, then I’m looking for another route…

thanks,
..dane

Reply | Quote

You have to store it someplace that does not trigger the undo stack clear. As mentioned earlier you can add it to the header or footer and then retrieve it later (as long as someone does not manually change it). Adding it to a cell or a name will clear the stack.

Steve

Reply | Quote

[indent]

---

Adding an Undo only adds that particular undo to the stack (i.e. the call to the sub). The rest of the stack is cleared. There is NO way around that, apart from removing everything from your event code that clears the stack (as I wrote earlier if I recall correctly).

---

[/indent]

Ah-ha… So I misunderstood the capabilities of the undo handler Hans recommended… it only provides a one-deep undo, not an undo which inherently preserves the rest of the undo stack…

Let me ask you; is there any way to update some public variable that an equation on a worksheet uses, insead of updating a worksheet cell directly? I wonder if that would be a way to update a timestamp without destroying the “undo” stack?

thanks,
..dane

Reply | Quote

Adding an Undo only adds that particular undo to the stack (i.e. the call to the sub). The rest of the stack is cleared. There is NO way around that, apart from removing everything from your event code that clears the stack (as I wrote earlier if I recall correctly).

Reply | Quote

---

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.

---

Hans,

I looked at the example you referenced. And I agree, a generic “undo” is a major undertaking indeed. For my purposes, I am fine with supporting a “false” undo routine that doesn’t really do anything, just supports undoing changes prior to my event handler changes..

I updated my two routines as follows:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

  ' Whenever a change is made to any worksheet,
  ' Update it's "Last Updated..." field
  With Sh.Range("A2")
    ' We don't want a recursive event...
    Application.EnableEvents = False
        
    ' Sheet changed... update the "last updated" date
    .Value = FormatDateTime(Now, vbLongDate) & " at " & FormatDateTime(Now, vbShortTime)
    
    ' Re-enable events... we're done.
    Application.EnableEvents = True
        
    ' Hide cell data...
    .NumberFormat = ";;;"
  End With
    
  ' We don't really support "UNDOing" the last timestamp,
  ' but we do want to support undoing things done BEFORE that timestamp
  ' So we'll just go ahead and define a generic "undo" callback
  Application.OnUndo "Undo Timestamp", "UndoTimestamp"

End Sub

Public Sub UndoTimestamp()

  ' Don't actually do anything, just support the undo operation...

End Sub

When I update a sheet, the undo function indeed does not go away anymore. However, when I try to actually undo something, I get the following error:

The macro "'!UndoTimestamp" cannot be found

I’ve tried prefixing the sub with “Public” and have also tried putting it in the active workbook’s code module. Am I overlooking something obvious?

thanks!
..dane

Reply | Quote

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.

Reply | Quote

Steve,

A followup to your suggestion:

[indent]

---

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

---

[/indent]

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

Reply | Quote

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

Reply | Quote

How about this?

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

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

Reply | Quote

Maybe even better to use a local (hidden) name:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    On Error Resume Next
    If Sh.Names("ChangeDate") Is Nothing Then _
    ThisWorkbook.Names.Add "'" & Sh.Name & "'!ChangeDate", "1", False
    On Error GoTo 0
    ThisWorkbook.Names("'" & Sh.Name & "'!ChangeDate").RefersTo = CStr(Now)
    Sh.PageSetup.CenterHeader = "Sheet Modified: " & Format(Date, "mmm d, yyyy")
End Sub

Reply | Quote

Jan Karel,

Thanks.

I think I follow the code. Let me see:
– check if the name collection for the sheet already has a ChangeDate member; if not create it (altho I don’t understand the entire syntax for the ThisWorkbook.Names.Add statement – looks like you’re creating a name like Sheet1!ChangeDate but I’m not sure if the IF test is testing for this name or if the names are “segregated” by sheet so it’s sufficient to look at the collection of names associated with the sheet; also not sure what the 1 and False arguments do)

– then you’re setting a value for the ChangeDate name

– then you’re setting the center header to the current date formatted appropriately. Is there a reason why ChangeDate is not used in this statement? Does it matter?

I like this approach better since it doesn’t add anything to the cells. But see my other post and response to you about making a copy of a workbook w/o macros.

Fred

Reply | Quote

I just create the name if it isn’t there and subsequently add the change date to it.
Since the code sample earlier in this thread also doesn’t do anything with the cell that received the date I decided not to do anything with the name either.

Of course you can use the name anywhere in the sheet when needed, just use =ChangeDate as a formula.

The “1” just puts the string 1 into the newly defined name, the False causes the name to be hidden from the user (that is, if he doesn’t use my name manager ).

Reply | Quote

Jan Karel,

Duh, I must have been dense this morning. I was thinking of the names you were mentioning as the equivalent of Word’s DocProperties – something that’s in the VBA env but on a sheet basis.

OK got it now.

So it seems that each sheet has a name collection (for sheet-specific/local names?) that you’re testing with If Sh.Names(“ChangeDate”), creating it if necessary. Would it be equally valid to test
If Sh.Name & “‘!ChangeDate” Is Nothing
to see if such a name exists? What’s the difference?

Then you access the ChangeDate name corresponding to the sheet that was being changed to store today’s date with CSTR(NOW). I don’t think it matters for me but does that store the entire “Now” as in the original solution that was stored in a1 (ie, including the time)?

The reason I said I don’t think it matters any more (this is still evolving) is that your last statement

Sh.PageSetup.CenterHeader = “Sheet Modified: ” & Format(Date, “mmm d, yyyy”)

may do all I need. I thought I needed to have some storage (cell, name) to copy the save-date from into the header; maybe I still need it but maybe I don’t.

And of course, my recipients won’t be using your Name Manager.

Thanks.

Fred

Reply | Quote

<>

Sh.Name & “‘!ChangeDate” returns a string and therefore never is Nothing.

<>

Exactly.

<>

Well, should you decide you do need it, you can still use the name stuff. One great advantage of just setting the header is that you don’t loose the Undo stack.

<>

Well, you never know for sure…

Reply | Quote

<>

Sh.Name & “‘!ChangeDate” returns a string and therefore never is Nothing.

<>

Exactly.

<>

Well, should you decide you do need it, you can still use the name stuff. One great advantage of just setting the header is that you don’t loose the Undo stack.

<>

Well, you never know for sure…

Reply | Quote

Jan Karel,

Duh, I must have been dense this morning. I was thinking of the names you were mentioning as the equivalent of Word’s DocProperties – something that’s in the VBA env but on a sheet basis.

OK got it now.

So it seems that each sheet has a name collection (for sheet-specific/local names?) that you’re testing with If Sh.Names(“ChangeDate”), creating it if necessary. Would it be equally valid to test
If Sh.Name & “‘!ChangeDate” Is Nothing
to see if such a name exists? What’s the difference?

Then you access the ChangeDate name corresponding to the sheet that was being changed to store today’s date with CSTR(NOW). I don’t think it matters for me but does that store the entire “Now” as in the original solution that was stored in a1 (ie, including the time)?

The reason I said I don’t think it matters any more (this is still evolving) is that your last statement

Sh.PageSetup.CenterHeader = “Sheet Modified: ” & Format(Date, “mmm d, yyyy”)

may do all I need. I thought I needed to have some storage (cell, name) to copy the save-date from into the header; maybe I still need it but maybe I don’t.

And of course, my recipients won’t be using your Name Manager.

Thanks.

Fred

Reply | Quote

I just create the name if it isn’t there and subsequently add the change date to it.
Since the code sample earlier in this thread also doesn’t do anything with the cell that received the date I decided not to do anything with the name either.

Of course you can use the name anywhere in the sheet when needed, just use =ChangeDate as a formula.

The “1” just puts the string 1 into the newly defined name, the False causes the name to be hidden from the user (that is, if he doesn’t use my name manager ).

Reply | Quote

Jan Karel,

Thanks.

I think I follow the code. Let me see:
– check if the name collection for the sheet already has a ChangeDate member; if not create it (altho I don’t understand the entire syntax for the ThisWorkbook.Names.Add statement – looks like you’re creating a name like Sheet1!ChangeDate but I’m not sure if the IF test is testing for this name or if the names are “segregated” by sheet so it’s sufficient to look at the collection of names associated with the sheet; also not sure what the 1 and False arguments do)

– then you’re setting a value for the ChangeDate name

– then you’re setting the center header to the current date formatted appropriately. Is there a reason why ChangeDate is not used in this statement? Does it matter?

I like this approach better since it doesn’t add anything to the cells. But see my other post and response to you about making a copy of a workbook w/o macros.

Fred

Reply | Quote

As I was searching for a method of adding the sheet modification date to the header, I found this thread. This is, almost, exactly what I need. However; is there any way to make it update if the value in a cell containing a function changes?

I have several summary worksheets which are used by supervisors to review the work done in other spreadsheets (often, separate workbooks). They view or print these spreadsheets as periodic (daily, weekly, monthly, etc.) reports of various activities that are being tracked within their areas of supervision. The summary sheet contains nothing but formulas and links to other sheets, and no data is directly entered into any cells. As a result, the modification date in the header (or footer) would never change in the code that is included here.

Thanks,

Reply | Quote

You can add the same code to the Workbook_SheetCalculate event. This event occurs whenever a worksheet is recalculated. Or you might add the code to the Worksheet_Calculate event of the summary worksheets if you want to limit it to those worksheets.
I haven’t tested this in a calculation-intensive workbook. I suspect it might have a negative impact on performance.

Reply | Quote

You can add the same code to the Workbook_SheetCalculate event. This event occurs whenever a worksheet is recalculated. Or you might add the code to the Worksheet_Calculate event of the summary worksheets if you want to limit it to those worksheets.
I haven’t tested this in a calculation-intensive workbook. I suspect it might have a negative impact on performance.

Reply | Quote

If you really want to change the header/footer whenever a sheet is change, You could write a routine in the Worksheet-CHange event to change the header whenever a change was made to the sheet. It might get a little sluggish.

Add this to the thisworkbook object and any change made to a sheet will change that sheets center footer to indicate the date/time it was last modified

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Sh.PageSetup.CenterFooter = "Last Modified on " & _
        Format(Now, "mmm d, yyyy hh:mm AM/PM")
End Sub

Steve

Reply | Quote

Thanks Steve.

I have chart sheets associated with some (not all) of the summary sheets (multiple charts per sheet in almost every case). Is there a way to make sure that the modification dates carry over to those page footers, as well?

Reply | Quote

I know of no way to tell if a chart is modified. I do not think there is an event to trap on the chart sheet.

Working with chart objects (on a chart sheet or a worksheet) has even less accessible events.

Perhaps someone else knows a method.

Steve

Reply | Quote

I know of no way to tell if a chart is modified. I do not think there is an event to trap on the chart sheet.

Working with chart objects (on a chart sheet or a worksheet) has even less accessible events.

Perhaps someone else knows a method.

Steve

Reply | Quote

Wouldn’t it be sufficient to create a beforeprint event that handles the header/footer setting? That way there is no performance hit on changes and/or recalculation, just a little wait when one wants to print:

In the Thisworkbook Module:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim oSh As Object
    On Error Resume Next 'In case the sheet has no header/footer property or is protected
    For Each oSh In ThisWorkbook.Sheets
        oSh.PageSetup.LeftFooter = "Last modified: " & Format(Now, "mm/dd/yyyy hh:mm")
    Next
End Sub

Reply | Quote

If done this way, I prefer:

oSh.PageSetup.LeftFooter = “Printed on: ” & Format(Now, “mm/dd/yyyy hh:mm”)

to distinguish that this is the time it was printed, and not implying that it was changed.

If you make no changes to a workbook, your method will indicate that it has been changed just by printing it. With the above modification, there is no indication of when it was last modified, just when it was printed, which might not answer the real goal, but is more accurate

Steve

Reply | Quote

Good point Steve.

Reply | Quote

Good point Steve.

Reply | Quote

Hans, Steve & Jan Karel,

Thank you. Your help is appreciated more than I can ever express.

I already include the print date in the header of the chart sheet (using &[Date]). I need to include the data modification date, somehow. Prior to your help, yesterday, whenever the data is updated in a spreadsheet, someone had to go in and, manually change the modification date in the footer of both the worksheet and the chartsheet (as of today, only the chartsheet has to be changed that way). Since each chartsheet is linked to a single worksheet (regardless of how many charts are on the chartsheet), would there be a way to pass the modification date from the header/footer of the worksheet to the header/footer of the chartsheet — even if it was passed as a graphic (which is how I include a single legend on a chartsheet with multiple charts — all using the same color scheme, of course)?

Reply | Quote

How about a line of code like this in the macro for the sheet change

Sheets("Chart1").PageSetup.CenterFooter = "Data Last Modified on " &  _
    Format(Now, "mmm d, yyyy hh:mm AM/PM")

This will update the chart footer (change the name of the chart sheet as appropriate) whenever the data is modified.

Steve

Reply | Quote

Here’s the code as I’ve modified it to fit one of my workbooks:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Sh.PageSetup.RightFooter = “Updated: ” & _
Format(Now, “mmmm d, yyyy”)
Sheets(“2004 Chart”).PageSetup.RightFooter = “Updated: ” & _
Format(Now, “mmmm d, yyyy”)
End Sub

The only problem with this, is that there are multiple worksheets and related chartsheets in a workbook. For example, the “Mthly to Qtrly Turn Around.xls” contains the following objects:

Chart5 (2005 Charts) Sheet4 (2005 Data)
Chart4 (2004 Charts) Sheet3 (2004 Data)
Chart6 (2003 Charts) Sheet5 (2003 Data)

Since someone could update a previous year’s data at any time, I cannot afford to keep “2004 Charts” hard-coded into the VBA script. Unfortunately, I’m still enough of a VBA novice, that I’m not sure how to insure that, when “2005 Data” is changed, “2005 Charts” receives the modified footer. How should I make this happen?

Reply | Quote

You could replace 2005 with the first 4 characters of the worksheet name:

Sheets(Left(sh.Name, 4) & ” Charts”).PageSetup.RightFooter = …

Reply | Quote

You might want to include:

On error resume next
Sheets(Left(sh.Name, 4) & " Charts").PageSetup.RightFooter = ...
On error goto 0

Just in case the sheet does not exist (which is always a possibility), especially if there are “other sheets” (without the year) in the workbook.

Steve

Reply | Quote

I read Jamie’s For example, the “Mthly to Qtrly Turn Around.xls” contains the following objects to mean “only the following objects“, but I realize that that’s not necessarily true, so good point.

Reply | Quote

I wasn’t sure about only currently, but it is probably better to head it off now, just in case in the future more sheets are added that do not have chart sheets or are named differently.

It is minor change that could prevent headaches later…

Steve

Reply | Quote

I wasn’t sure about only currently, but it is probably better to head it off now, just in case in the future more sheets are added that do not have chart sheets or are named differently.

It is minor change that could prevent headaches later…

Steve

Reply | Quote

I read Jamie’s For example, the “Mthly to Qtrly Turn Around.xls” contains the following objects to mean “only the following objects“, but I realize that that’s not necessarily true, so good point.

Reply | Quote

Steve,

I do have several workbooks that have worksheets without associated chartsheets. I’ll add the “on error” lines after we correct the subscript (and any other) errors.

Reply | Quote

This will fix the “subscript error”. As Hans mentioned it occurs because there are sheets with no “chart sheets” based on their first 4 letters. I suggested these lines in anticipation of that problem. Since we expect this “error” and we know it is not a “big deal”, we tell VB to ignore it.

Other errors will still be “caught” (as normal) by VB

The first line:
On error resume next
tells VB to go to the next line (after the line the error occurs in) if an error occurs.
This If a sheet name does not exist (subsript error), it will just be ignored.
The 2nd on error:
On error goto 0
just returns error handling to “normal” and VB will handle any others that may occur.

Steve

Reply | Quote

I have the On Error lines in the code. I had commentted them out in order to try to determine why the chart footer was not being updated. When I hardcode the chart name into the code, it works fine, but when I use the Left(Sh.Name, 4) & ” Charts”, it stops updating any of the chartsheets.

Reply | Quote

Could you copy the entire Workbook_SheetChange procedure into a reply?

Reply | Quote

Could you copy the entire Workbook_SheetChange procedure into a reply?

Reply | Quote

I would guess that the name of the chart sheet does not exactly match the name generated by Left(Sh.Name, 4) & ” Charts. Possibly something like the chart sheet name has two spaces between the year and “Charts”, or the sheet name has “Chart” not “Charts”, or “CHARTS” in all upper case, or extra spaces before the year or after “Charts”.

Reply | Quote

BINGO!!!

Legare, your suspicion was right on! It figures that I would choose to test this code on the one workbook that had a slight difference in the names of the chart sheets from all the others. I have now evaluated all of the workbooks and made sure that all of the sheets are named using the same pattern. The code works perfectly now — in every workbook. It just goes to show that patience and careful examination are more important than trying to meet deadlines.

With all the help everyone has given me in this forum during the past couple of weeks, I have expanded my understanding of Excel and VBA, enormously. I hope that, in time, I might become as helpful to others as all of you are to me.

With the greatest appreciation to all the wizards on this forum,

Reply | Quote

BINGO!!!

Legare, your suspicion was right on! It figures that I would choose to test this code on the one workbook that had a slight difference in the names of the chart sheets from all the others. I have now evaluated all of the workbooks and made sure that all of the sheets are named using the same pattern. The code works perfectly now — in every workbook. It just goes to show that patience and careful examination are more important than trying to meet deadlines.

With all the help everyone has given me in this forum during the past couple of weeks, I have expanded my understanding of Excel and VBA, enormously. I hope that, in time, I might become as helpful to others as all of you are to me.

With the greatest appreciation to all the wizards on this forum,

Reply | Quote

I would guess that the name of the chart sheet does not exactly match the name generated by Left(Sh.Name, 4) & ” Charts. Possibly something like the chart sheet name has two spaces between the year and “Charts”, or the sheet name has “Chart” not “Charts”, or “CHARTS” in all upper case, or extra spaces before the year or after “Charts”.

Reply | Quote

I have the On Error lines in the code. I had commentted them out in order to try to determine why the chart footer was not being updated. When I hardcode the chart name into the code, it works fine, but when I use the Left(Sh.Name, 4) & ” Charts”, it stops updating any of the chartsheets.

Reply | Quote

This will fix the “subscript error”. As Hans mentioned it occurs because there are sheets with no “chart sheets” based on their first 4 letters. I suggested these lines in anticipation of that problem. Since we expect this “error” and we know it is not a “big deal”, we tell VB to ignore it.

Other errors will still be “caught” (as normal) by VB

The first line:
On error resume next
tells VB to go to the next line (after the line the error occurs in) if an error occurs.
This If a sheet name does not exist (subsript error), it will just be ignored.
The 2nd on error:
On error goto 0
just returns error handling to “normal” and VB will handle any others that may occur.

Steve

Reply | Quote

Steve,

I do have several workbooks that have worksheets without associated chartsheets. I’ll add the “on error” lines after we correct the subscript (and any other) errors.

Reply | Quote

You might want to include:

On error resume next
Sheets(Left(sh.Name, 4) & " Charts").PageSetup.RightFooter = ...
On error goto 0

Just in case the sheet does not exist (which is always a possibility), especially if there are “other sheets” (without the year) in the workbook.

Steve

Reply | Quote

Hans,

This is giving me a subscript out of range error. Any idea what the problem might be?

Reply | Quote

You should follow Steve’s suggestion. Since you have worksheets without corresponding chart sheets, the code tries to update the footer of non-existing chart sheets.

Alternatively, you could put code in the Worksheet_Change event of the “200x Data” sheets that have corresponding chart sheets, and remove the code involving the chart sheets from the Workbook_SheetChange event. But that is more work, and harder to maintain.

Reply | Quote

You should follow Steve’s suggestion. Since you have worksheets without corresponding chart sheets, the code tries to update the footer of non-existing chart sheets.

Alternatively, you could put code in the Worksheet_Change event of the “200x Data” sheets that have corresponding chart sheets, and remove the code involving the chart sheets from the Workbook_SheetChange event. But that is more work, and harder to maintain.

Reply | Quote

Hans,

This is giving me a subscript out of range error. Any idea what the problem might be?

Reply | Quote

You could replace 2005 with the first 4 characters of the worksheet name:

Sheets(Left(sh.Name, 4) & ” Charts”).PageSetup.RightFooter = …

Reply | Quote

Here’s the code as I’ve modified it to fit one of my workbooks:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Sh.PageSetup.RightFooter = “Updated: ” & _
Format(Now, “mmmm d, yyyy”)
Sheets(“2004 Chart”).PageSetup.RightFooter = “Updated: ” & _
Format(Now, “mmmm d, yyyy”)
End Sub

The only problem with this, is that there are multiple worksheets and related chartsheets in a workbook. For example, the “Mthly to Qtrly Turn Around.xls” contains the following objects:

Chart5 (2005 Charts) Sheet4 (2005 Data)
Chart4 (2004 Charts) Sheet3 (2004 Data)
Chart6 (2003 Charts) Sheet5 (2003 Data)

Since someone could update a previous year’s data at any time, I cannot afford to keep “2004 Charts” hard-coded into the VBA script. Unfortunately, I’m still enough of a VBA novice, that I’m not sure how to insure that, when “2005 Data” is changed, “2005 Charts” receives the modified footer. How should I make this happen?

Reply | Quote

Hans, Steve & Jan Karel,

Thank you. Your help is appreciated more than I can ever express.

I already include the print date in the header of the chart sheet (using &[Date]). I need to include the data modification date, somehow. Prior to your help, yesterday, whenever the data is updated in a spreadsheet, someone had to go in and, manually change the modification date in the footer of both the worksheet and the chartsheet (as of today, only the chartsheet has to be changed that way). Since each chartsheet is linked to a single worksheet (regardless of how many charts are on the chartsheet), would there be a way to pass the modification date from the header/footer of the worksheet to the header/footer of the chartsheet — even if it was passed as a graphic (which is how I include a single legend on a chartsheet with multiple charts — all using the same color scheme, of course)?

Reply | Quote

If done this way, I prefer:

oSh.PageSetup.LeftFooter = “Printed on: ” & Format(Now, “mm/dd/yyyy hh:mm”)

to distinguish that this is the time it was printed, and not implying that it was changed.

If you make no changes to a workbook, your method will indicate that it has been changed just by printing it. With the above modification, there is no indication of when it was last modified, just when it was printed, which might not answer the real goal, but is more accurate

Steve

Reply | Quote

Wouldn’t it be sufficient to create a beforeprint event that handles the header/footer setting? That way there is no performance hit on changes and/or recalculation, just a little wait when one wants to print:

In the Thisworkbook Module:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim oSh As Object
    On Error Resume Next 'In case the sheet has no header/footer property or is protected
    For Each oSh In ThisWorkbook.Sheets
        oSh.PageSetup.LeftFooter = "Last modified: " & Format(Now, "mm/dd/yyyy hh:mm")
    Next
End Sub

Reply | Quote

Thanks Steve.

I have chart sheets associated with some (not all) of the summary sheets (multiple charts per sheet in almost every case). Is there a way to make sure that the modification dates carry over to those page footers, as well?

Reply | Quote

If you really want to change the header/footer whenever a sheet is change, You could write a routine in the Worksheet-CHange event to change the header whenever a change was made to the sheet. It might get a little sluggish.

Add this to the thisworkbook object and any change made to a sheet will change that sheets center footer to indicate the date/time it was last modified

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Sh.PageSetup.CenterFooter = "Last Modified on " & _
        Format(Now, "mmm d, yyyy hh:mm AM/PM")
End Sub

Steve

Reply | Quote

Oh Ye Amazing VB Magicians,

It’s been a while since you mixed this wonderful potion, but I need a new ingredient added. Previously, we were trying to insert the data update date into the header/footer of a worksheet in the workbook “Mthly to Qtrly Turn Around.xls”. This is the code I ended up with which accomplished this feat of legerdomain:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Sh.PageSetup.RightFooter = "Updated: " & Format(Now, "mmmm d, yyyy")
    On Error Resume Next
    Sheets(Left(Sh.Name, 4) & " Charts").PageSetup.RightFooter = "Updated: " & _
        Format(Now, "mmmm d, yyyy")
    On Error GoTo 0
End Sub

Now, a quarterly workbook, “Qtrly Turn Around – HCFA to UB.xls”, has been added, in which the worksheets and charts are updated indirectly from the original “Mthly to Qtrly Turn Around.xls”. The Workbook_SheetChange event never occurs, because nothing in any of the “Qtrly Turn Around – HCFA to UB.xls” worksheets is directly modified.
The charts in the new workbook update from their respective worksheets in the same workbook. However, each worksheet uses links (in individual cells) to pick up data from the corresponding year

Reply | Quote

You could, in addition to putting the date in a header/footer, also put it into a cell during the change event.

The quarterly worksheet, since I presume it is just linked to and reading the cells, can have a link to this date cell and have it displayed somewhere on the sheet.

other options would be to:
Have your current code, access the quaterly workbook and change the last updated at the same time
Whenever the qtrly workbook is open, have it read the last update info from the mthly one and update it that time.

Steve

Reply | Quote

Steve,

As always, you’ve come through for me. And, as is usually the case, the simplest solution is the best and most elegant. Save the date to an available cell in the monthly worksheet, link it to an available cell in the quarterly worksheet, and pull it into the footer text from there.

Thank you,

Reply | Quote

As I was searching for a method of adding the sheet modification date to the header, I found this thread. This is, almost, exactly what I need. However; is there any way to make it update if the value in a cell containing a function changes?

I have several summary worksheets which are used by supervisors to review the work done in other spreadsheets (often, separate workbooks). They view or print these spreadsheets as periodic (daily, weekly, monthly, etc.) reports of various activities that are being tracked within their areas of supervision. The summary sheet contains nothing but formulas and links to other sheets, and no data is directly entered into any cells. As a result, the modification date in the header (or footer) would never change in the code that is included here.

Thanks,

Reply | Quote

Maybe even better to use a local (hidden) name:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    On Error Resume Next
    If Sh.Names("ChangeDate") Is Nothing Then _
    ThisWorkbook.Names.Add "'" & Sh.Name & "'!ChangeDate", "1", False
    On Error GoTo 0
    ThisWorkbook.Names("'" & Sh.Name & "'!ChangeDate").RefersTo = CStr(Now)
    Sh.PageSetup.CenterHeader = "Sheet Modified: " & Format(Date, "mmm d, yyyy")
End Sub

Reply | Quote

Steve,

Thanks. I forgot about the useful trick of a custom format of ;;;

Agree that’s better than making the font color match the fill color.

Fred

Reply | Quote

Steve,

Thanks. I forgot about the useful trick of a custom format of ;;;

Agree that’s better than making the font color match the fill color.

Fred

Reply | Quote

How about this?

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

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

Reply | Quote

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

Reply | Quote

Add this code to the thisworkbook object in VB

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

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

Steve

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote