Excel - Auto Save username & date/time to list

[srwalesh]

i am looking for some advice, and my assumption is that this is likely to need VBA... I currently run a shared file with VBA that will send me an email stating that changes have been made to said file. This is only useful, however, if the end user is has Microsoft Outlook running on their device. I would much prefer, if possible, to use a hidden tab that will add a date and time stamp to a column each time the file is saved by a user. Listing the username of the individual would also be a necessity. Is there a formula/set of formulas that could enter this information? i have an example of what i am thinking would be a good output, but i am open to suggestions.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Username[/TD]
[TD]date/time[/TD]
[/TR]
[TR]
[TD]bob hope[/TD]
[TD]01-15-2016 at 5:55 PM[/TD]
[/TR]
[TR]
[TD]bob dylan[/TD]
[TD]01-16-2016 at 3:27 AM[/TD]
[/TR]
[TR]
[TD]bob uecker[/TD]
[TD]01-20-2016 at 12:20 PM[/TD]
[/TR]
[TR]
[TD]bob ross[/TD]
[TD]01-25-2016 at 8:00 AM[/TD]
[/TR]
</tbody>[/TABLE]


any help is much appreciated, thanks everyone!

 

[BobUmlas]

Assuming Sheet2 is the hidden sheet, right-click any sheet, select View Code, double-click "ThisWorkbook", enter this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("Sheet2").Range("A1000000").End(xlUp).Offset(1)
.Value = Application.UserName
.Offset(, 1).Value = Now
End With
End Sub

 

[srwalesh]

that worked great! i modified the formatting a bit, but still did the job.

thanks!

 

[srwalesh]

i have run into an issue with this formula, but i do not know why. i have 5-6 people saving to a file at different times during the day, however, there is one person who when they save the file a time stamp is not created. any ideas?