Last Save date/time and User for a specific sheet in a shared work

[Greg in CO]

Hi All!

I have a shared workbook which will h ave several users. I would like to
enter a UDF so that in a cell at the top of each worksheet, it will post the
last time that worksheet was saved and who saved it. I have found several
batches of code here that come close, but I do not want the the date/time and
user from a save on Worksheet A to show up on Worksheet B. Is this possible
or does Excel only show the saves for the workbook?

Ideally, if Fred and Ethel are working in the shared book, Fred on SheetA
and Ethel on SheetB, and Fred saves at 9:00 and Ethel saves at 9:15, when
someone else opens the workbook, on SheetA, they will see:

Last Saved: 09:00
Last Saved by: Fred Mertz

and on SheetB:

Last Saved: 09:15
Last Saved by: Ethel Mertz

I would like these two cells to be updated as soon as there is a save action
on the specific sheet.

Also, what should be entered into the cells?

Thanks!

 

[Gary''s Student]

Put the following event code in the workbook code area:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1").Value = Environ("username")
Range("A2").Value = Now - Date
Range("A2").NumberFormat = "[$-F400]h:mm:ss AM/PM"
End Sub

Before saving, the required data is written in the sheet the user is using.

 

[Greg in CO]

WOW! That looks like it worked. Is there a change to the "username" that
would pull the User Name from the Tools>Options>General>User Name field?

I tested it on two different sheets...to different times...and I am guessing
that with a shared workbook, it will show two different users.

You win today's "You rock!" award...enjoy!

Thanks so much!

Greg
--
Greg

Put the following event code in the workbook code area:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1").Value = Environ("username")
Range("A2").Value = Now - Date
Range("A2").NumberFormat = "[$-F400]h:mm:ss AM/PM"
End Sub

Before saving, the required data is written in the sheet the user is using.
--
Gary''s Student - gsnu200805

Hi All!

I have a shared workbook which will h ave several users. I would like to
enter a UDF so that in a cell at the top of each worksheet, it will post the
last time that worksheet was saved and who saved it. I have found several
batches of code here that come close, but I do not want the the date/time and
user from a save on Worksheet A to show up on Worksheet B. Is this possible
or does Excel only show the saves for the workbook?

Ideally, if Fred and Ethel are working in the shared book, Fred on SheetA
and Ethel on SheetB, and Fred saves at 9:00 and Ethel saves at 9:15, when
someone else opens the workbook, on SheetA, they will see:

Last Saved: 09:00
Last Saved by: Fred Mertz

and on SheetB:

Last Saved: 09:15
Last Saved by: Ethel Mertz

I would like these two cells to be updated as soon as there is a save action
on the specific sheet.

Also, what should be entered into the cells?

Thanks!

 

[Gary''s Student]

Sure... to get the "other" name use:

ActiveWorkbook.BuiltinDocumentProperties(3)

 

[Greg in CO]

Gary, that is awesome! Thanks!

Should the date be showing up as well? All I get is the time with AM/PM,
but no date before it.

 

[Greg in CO]

Actually, I edited the function so the date shows up in its own cell (so I
have references to Range("A1"), A2, A3 instead of A1, A2, A2)

I am getting a date of January 0, 1900.

Eeek!

 

[Greg in CO]

Hi Gary!

So, in trying to resolve/learn how to do this myself, I fiddled with the
function and now have this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("h1").Value = BuiltinDocumentProperties("Last Save Time").Value
Range("H2").NumberFormat = "[$-F400]h:mm:ss AM/PM"
Range("H3").Value = ActiveWorkbook.BuiltinDocumentProperties(3)
End Sub

I rearranged the arguements so that the cells are in order (H1,H2,H3) and
inserted the BuiltinDocumentProperties("Last Save Time").Value in hopes that
it might return the date and the time together. It did, but was a minute or
two off, even though the file Properties showed the last save time correctly.
Also, with the new arguement for H1, the "[$-F400]h:mm:ss AM/PM" for H2
ceased to function entirely.

I am getting the date and time, but I am not sure if it is actually posting
the Save date and Time for each sheet. If I go to a differnt sheet (not new)
and I hit Save 3 times in succession in a couple of seconds, it will post the
current date and the time about 1-2 miutes off, then 1 minute off and then
the correct time, as if there is a dealy in the time stamp.

Bizzare!

I liked your original formula and the times were correct...what do I need to
do to get the date with it as well?

Thanks Mucho!

 

[Greg in CO]

Hi! Me again........

The fiddling continues...I looked up the
ActiveWorkbook.BuiltinDocumentProperties to return the date and time...and
this appears to be working on each worksheet. I will need to test the file
in a shared mode.

The time, however, is still off by a minute or so.

If you can adjust your original formula to show the date, I will use that,
as it worked without issue.

Thanks!!!!



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("h2").Value = ActiveWorkbook.BuiltinDocumentProperties(12)
Range("H3").Value = ActiveWorkbook.BuiltinDocumentProperties(7)
End Sub
--
Greg

Hi Gary!

So, in trying to resolve/learn how to do this myself, I fiddled with the
function and now have this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("h1").Value = BuiltinDocumentProperties("Last Save Time").Value
Range("H2").NumberFormat = "[$-F400]h:mm:ss AM/PM"
Range("H3").Value = ActiveWorkbook.BuiltinDocumentProperties(3)
End Sub

I rearranged the arguements so that the cells are in order (H1,H2,H3) and
inserted the BuiltinDocumentProperties("Last Save Time").Value in hopes that
it might return the date and the time together. It did, but was a minute or
two off, even though the file Properties showed the last save time correctly.
Also, with the new arguement for H1, the "[$-F400]h:mm:ss AM/PM" for H2
ceased to function entirely.

I am getting the date and time, but I am not sure if it is actually posting
the Save date and Time for each sheet. If I go to a differnt sheet (not new)
and I hit Save 3 times in succession in a couple of seconds, it will post the
current date and the time about 1-2 miutes off, then 1 minute off and then
the correct time, as if there is a dealy in the time stamp.

Bizzare!

I liked your original formula and the times were correct...what do I need to
do to get the date with it as well?

Thanks Mucho!
--
Greg

Hi All!

I have a shared workbook which will h ave several users. I would like to
enter a UDF so that in a cell at the top of each worksheet, it will post the
last time that worksheet was saved and who saved it. I have found several
batches of code here that come close, but I do not want the the date/time and
user from a save on Worksheet A to show up on Worksheet B. Is this possible
or does Excel only show the saves for the workbook?

Ideally, if Fred and Ethel are working in the shared book, Fred on SheetA
and Ethel on SheetB, and Fred saves at 9:00 and Ethel saves at 9:15, when
someone else opens the workbook, on SheetA, they will see:

Last Saved: 09:00
Last Saved by: Fred Mertz

and on SheetB:

Last Saved: 09:15
Last Saved by: Ethel Mertz

I would like these two cells to be updated as soon as there is a save action
on the specific sheet.

Also, what should be entered into the cells?

Thanks!

 

[Greg in CO]

FOLLOW UP:

I researched variations to the code and got it to work as desired:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("h3").Value = ActiveWorkbook.BuiltinDocumentProperties(7)
Range("h2").Value = Now
Range("h2").NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"
End Sub

However, Excel seems to want you to flip back and forth between worksheets
before it will show the User Name. I tried this with a different user as
well. If you Save on the active sheet, it will show the new time stamp, but
not the User. Go to another sheet, Save, and it works ok...then go back to
the sheet you were on and Save and it is OK>

Latency issue in Excel?
--
Greg

Hi Gary!

So, in trying to resolve/learn how to do this myself, I fiddled with the
function and now have this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("h1").Value = BuiltinDocumentProperties("Last Save Time").Value
Range("H2").NumberFormat = "[$-F400]h:mm:ss AM/PM"
Range("H3").Value = ActiveWorkbook.BuiltinDocumentProperties(3)
End Sub

I rearranged the arguements so that the cells are in order (H1,H2,H3) and
inserted the BuiltinDocumentProperties("Last Save Time").Value in hopes that
it might return the date and the time together. It did, but was a minute or
two off, even though the file Properties showed the last save time correctly.
Also, with the new arguement for H1, the "[$-F400]h:mm:ss AM/PM" for H2
ceased to function entirely.

I am getting the date and time, but I am not sure if it is actually posting
the Save date and Time for each sheet. If I go to a differnt sheet (not new)
and I hit Save 3 times in succession in a couple of seconds, it will post the
current date and the time about 1-2 miutes off, then 1 minute off and then
the correct time, as if there is a dealy in the time stamp.

Bizzare!

I liked your original formula and the times were correct...what do I need to
do to get the date with it as well?

Thanks Mucho!
--
Greg

Hi All!

I have a shared workbook which will h ave several users. I would like to
enter a UDF so that in a cell at the top of each worksheet, it will post the
last time that worksheet was saved and who saved it. I have found several
batches of code here that come close, but I do not want the the date/time and
user from a save on Worksheet A to show up on Worksheet B. Is this possible
or does Excel only show the saves for the workbook?

Ideally, if Fred and Ethel are working in the shared book, Fred on SheetA
and Ethel on SheetB, and Fred saves at 9:00 and Ethel saves at 9:15, when
someone else opens the workbook, on SheetA, they will see:

Last Saved: 09:00
Last Saved by: Fred Mertz

and on SheetB:

Last Saved: 09:15
Last Saved by: Ethel Mertz

I would like these two cells to be updated as soon as there is a save action
on the specific sheet.

Also, what should be entered into the cells?

Thanks!