Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Save date/time and User for a specific sheet in a shared work
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Save date/time and User for a specific sheet in a shared work
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 "Greg in CO" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Save date/time and User for a specific sheet in a shared
WOW! That looks like it worked. Is there a change to the "username" that
would pull the User Name from the ToolsOptionsGeneralUser 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 "Gary''s Student" wrote: 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 "Greg in CO" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Save date/time and User for a specific sheet in a shared
Sure... to get the "other" name use:
ActiveWorkbook.BuiltinDocumentProperties(3) -- Gary''s Student - gsnu200805 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Save date/time and User for a specific sheet in a shared
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 "Gary''s Student" wrote: Sure... to get the "other" name use: ActiveWorkbook.BuiltinDocumentProperties(3) -- Gary''s Student - gsnu200805 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Save date/time and User for a specific sheet in a shared
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 "Gary''s Student" wrote: Sure... to get the "other" name use: ActiveWorkbook.BuiltinDocumentProperties(3) -- Gary''s Student - gsnu200805 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Save date/time and User for a specific sheet in a shared work
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 "Greg in CO" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Save date/time and User for a specific sheet in a shared
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 "Greg in CO" wrote: 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 "Greg in CO" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Save date/time and User for a specific sheet in a shared
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 "Greg in CO" wrote: 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 "Greg in CO" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering current time in shared work book - 2003 | Excel Discussion (Misc queries) | |||
Last Save Time for non active shared workbook | Excel Programming | |||
Save user-defined chart types in a shared Excel template? | Charts and Charting in Excel | |||
Using Button on User Form to save Data to specific Cell?? | Excel Programming | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel |