print the date/time last edited

[Michelle]

Our school district uses Excel 2007 and the secretary uses it to keep
last rosters? Since it's the beginning of the school year, there are
constant changes in these rosters. Is it possible to place a date/
time field that would automatically change whenever the worksheet is
edited? We would like to be able to tell from the print-out how old/
recent the roster is.

 

[Shane Devenshire]

Hi Michelle,

I probably am missing something but why don't you put the Date field in the
Page Footer:

Method 1:
1. choose Page Layout, and click the Quick Launch button in the bottom right
corner of the Page Setup group.
2. go to the Headers/Footers tab and click Customer Footer
3. Click the 4th button from the left, the Insert Date button or next to it
the Insert Time button.

Method 2:
1. Choose View, Page Layout
2. Scroll down till you can see the footer and click in one section of it
3. The Ribbon will change to the Header & Footer Tools,
4. Add date or time from the ribbon.

Note these automatically update at print time.

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[Bob Phillips]

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")

 

[Michelle]

Hi Michelle,

I probably am missing something but why don't you put the Date field in the
Page Footer:

Method 1:
1. choose Page Layout, and click the Quick Launch button in the bottom right
corner of the Page Setup group.
2. go to the Headers/Footers tab and click Customer Footer
3. Click the 4th button from the left, the Insert Date button or next to it
the Insert Time button.

Method 2:
1. Choose View, Page Layout
2. Scroll down till you can see the footer and click in one section of it
3. The Ribbon will change to the Header & Footer Tools,
4. Add date or time from the ribbon.

Note these automatically update at print time.

Cheers,
Shane Devenshire
Microsoft Excel MVP

I've tried that, but it just seems to use the time that it's printed.
If the secretary edits them today, but a teacher doesn't print them
until next week, the date appears as next week's date. That doesn't
really give us accurate information as far as when the data on the
worksheet was last updated.

Thanks for the idea though.

 

[Michelle]

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
   Application.Volatile
   On Error GoTo err_value
   DocProps = ActiveWorkbook.BuiltinDocumentProperties _
   (prop)
   Exit Function
err_value:
   DocProps = CVErr(xlErrValue)
End Function

and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")

--
__________________________________
HTH

Bob

Could you give me a little more direction? I'm not sure what I need
to do with the above code. I tried finding docprops in the function
menu but am not seeing it. I'm used to using Office on my Mac and the
Excel 2007 for Windows is very new to me. With a little more step-by-
step direction, I'm sure that I can figure it out. By the way, if I
add the "last edited" string in each tab, will it update only for that
tab or will the date change for all tabs (even those not edited)?

 

[Michelle]

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
   Application.Volatile
   On Error GoTo err_value
   DocProps = ActiveWorkbook.BuiltinDocumentProperties _
   (prop)
   Exit Function
err_value:
   DocProps = CVErr(xlErrValue)
End Function

and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")

--
__________________________________
HTH

Bob

Could you give me a little more direction? I'm not sure what I need
to do with the above code. I tried finding docprops in the function
menu but am not seeing it. I'm used to using Office on my Mac and the
Excel 2007 for Windows is very new to me. With a little more step-by-
step direction, I'm sure that I can figure it out. By the way, if I
add the "last edited" string in each tab, will it update only for that
tab or will the date change for all tabs (even those not edited)?

 

[Gord Dibben]

Michelle

Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Right-click on your workbook(project) and Insert>Module.

Copy/paste Bob's DocProps UDF into that module.

Alt + q to return to the Excel window.

In A1(or a cell of your choice) of a sheet enter =DocProps("last save
time")

Format as Date

If the formula is placed in multiple sheets, it will update in all sheets
when the workbook is saved, not when a particular sheet is edited.

You would need some sheet event code to show unique "last edited" on each
sheet.


Gord Dibben MS Excel MVP

 

[Michelle]

Michelle

Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Right-click on your workbook(project) and  Insert>Module.

Copy/paste Bob's DocProps UDF into that module.

Alt + q to return to the Excel window.

In A1(or a cell of your choice) of a sheet enter  =DocProps("last save
time")

Format as Date

If the formula is placed in multiple sheets, it will update in all sheets
when the workbook is saved, not when a particular sheet is edited.

You would need some sheet event code to show unique "last edited" on each
sheet.

Gord Dibben  MS Excel MVP

Thanks. A couple of more questions. I can't quite tell where the
beginning and end of Bob's UDF is--does it include the lines above and
below "Function DocProps(prop As String)" ? Sorry if that's a stupid
question. Since I don't do much VB work (really never), I'm not
familiar with the opening and closing of code to be able to recognize
which part is the actual code and which part is description of the
code.

2nd question (hopefully the last)--You said that I would need some
sheet event code to show unique "last edited" on each sheet. Is this
something that is too complicated for me to even deal with or is it
something that I can achieve by cutting/pasting from some place?

Thanks again for everyone's help.

 

[Gord Dibben]

Please top-post when replying to my posts, thanks.

Here is Bob's UDF in total.

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

That is all you copy/paste to the General Module.

The rest of Bob's post was explanation of how to use in a cell.

The sheet event would not be that difficult to set up and I can provide code
for copy/paste and instructions for use.

Would you want to have a cell on each sheet receive the date/time when any
cell or cells on that particular sheet was altered, not just when the
workbook is saved?

Here is some sample code to enter the data/time in Column B when any cell in
Column A is changed manually.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
End Sub

Right-click on the Excel Icon left of "File" on the worksheet menu bar and
"View Code" to open Thisworkbook Module.............not the same as the
General Module you pasted the UDF into.

Copy/paste the code into that Module.

Alt + q to return to the Excel window.

Any sheet you have active and you enter data in any cell in column A will
result in a timestamp in column B for that row.

Alterations to to the code to suit can easily be made.


Gord

 

[Gord Dibben]

Ooops!

Please change:

If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then

To

If Not Intersect(Target, Range("A:A")) Is Nothing Then


Gord

 

[Dave Peterson]

The Me keyword is ok here. It refers to the sheet that owns the code.

In fact, I think it's a good idea to qualify the range.

 

[Gord Dibben]

A sheet does not own the code.

The code is Workbook_SheetChange and Me.Range crashes the event.


Gord

 

[Dave Peterson]

Oops.

I should have looked more closely.

If Not Intersect(Target, sh.Range("A:A")) Is Nothing Then

would be the correct way to qualify the range.

 

[Gord Dibben]

Thanks Dave.

I was going to use ActiveSheet to qualify but sh shorter.

Gord

 

[Dave Peterson]

And that's what's passed to the subroutine, too.

And when there are changes to grouped sheets, then this would fail:

If Not Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then

(but it's hidden by the "on error" line.)

 

[Gord Dibben]

Cool!

Too soon old........too late smart

But with your assistance I can improve ever so slightly.

No reflection on your tutoring skills<g>


Gord

 

[Dave Peterson]

4 messages to get two characters right--be careful about listening too much to
me!

 

[Michelle]

Sorry about not top-posting. It just went to the default forum
behavior and I didn't notice.

Thanks for the help. Yes, I'm getting closer. I got the UDF entered
and it changes whenever the document is saved. I'd actually like this
to be whenever it's edited (even if it's not been saved again), but
I'm not sure if that kind of detail is possible. Although it sounds
like it is if I can set it different on each worksheet.

You said that "Any sheet you have active and you enter data in any
cell in column A will result in a timestamp in column B for that row.
Alterations to to the code to suit can easily be made." I'd actually
like the timestamp to occur whenever there is a change anywhere on
that sheet--not just specific to a certain column. The worksheets are
complex enough to need the detail so finite that it records changes to
each column--just the worksheet as a whole. Does that make sense?

So if I had 2-3 columns--one for student name, one for student ID, and
one for misc., I would want to create a timestamp either at the top or
at the bottom of that page whenever the student data is edited. So
I'm guessing that the only part that I would need to change is
changing "A:A" to a string that would include "A:C" Am I on the right
track or is there another portion of the code that would need
changed. The rest is pretty Greek to me, so I may be missing many
other things that would need changed.

Thanks again for your help. I love the community in the forums!

 

[Michelle]

Forgive me for getting a little lost when following the 2-way debate
about this. I wanted to test this just to see if I could figure out
how to do this portion. I changed that one portion of the code that
you requested, but I must still be doing something wrong. I entered
the code where you directed, but I'm not seeing anything change on the
worksheet. When I add or change something in column A, I'm seeing
nothing appear is column B.

Here's a screenshot of the module--hopefully it will come through
okay.

http://i100.photobucket.com/albums/m29/teacher24_70/
MiscScreenShots/VBScreenshot.jpg

 

[Michelle]

Forgive me for getting a little lost when following the 2-way debate
about this. I wanted to test this just to see if I could figure out
how to do this portion. I changed that one portion of the code that
you requested, but I must still be doing something wrong. I entered
the code where you directed, but I'm not seeing anything change on the
worksheet. When I add or change something in column A, I'm seeing
nothing appear is column B.

Here's a screenshot of the module--hopefully it will come through
okay.

http://i100.photobucket.com/albums/m29/teacher24_70/
MiscScreenShots/VBScreenshot.jpg