VBA macro to tell who last saved a file and when

[Vera]

Please I need help with a macro (saved on the workbook not in the Personal macro file) which should give the following info (lets say on sheet 1, starting A1, A2) who last saved that file and the date. Thank you so much.

Vera

 

[Greg Truby]

Ummm...why all this fuss? near as I can tell, ain't nobody actually asked any questions on dis here thread since one lil' ol' question (and no followup) three months ago, 'n' 'fore that the last post was waaaaaaay back in March of '04. Did I miss something?

 

[Joe Was]

Greg, Vera had posted something to the effact that her systems people re-set every one to high security and now the solution she was using will not work [VBA] and she can'y use a self-signed certificate, she did not sy why?

Now her post is gone [deleted]?

So this must be part of the confusion?


Nope, Found it some how it moved up, I thought it was just above my post?

In any case, here it is:


Hello everybody. I am back to this problem after a while. I was using the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("List1").Range("A1111") = Application.UserName
Sheets("List1").Range("A1112") = Now
End Sub


with no problem, because I turned to all my users the macro security level to low. But unfortunately now we are required by IT guys to have it at Higt. I tried the SelfCert certificate but is not an option.

My question is if there is a way to get the info that I need (who saved last the file and when) or using Excel's functions (I wonder why the damned Cell or Info don't have such capabilities [using Excel 2002 Windows]) or VBA even when the macro security is set at High level. Thank you so much


Vera

 

[Greg Truby]

ummm, ok, did you notice that the post you cite is dated 11 Mar 2004? Perhaps there was another that did get deleted??? Anyhow, back to my day job....

 

[sillylupie77]

Any codes to display windows username instead?

Thanks

 

[Greg Truby]

Environ("username") returns the OS's sign-on name.

 

[singlgl1]

I have a similar need except I don't need to know who saved the active sheet, but when it was last saved.

 

[Greg Truby]

You cannot save a particular sheet; only the workbook in its entirety. Therefore, unless I am misunderstanding your question or you are misstating it, you should be able to simply right-click the file in Windows explorer and look at its properties to see when it was last saved.

 

[Lazerus3511]

Howdy Joe Was,

I wanted to thank you for this little gem. It is exactly what I needed

I use this in one of my applications, as a share file on the network, the file is transferred to and updated on the users PC then saved back to the network. Each person who saves the file is added to the ongoing log on Sheet1(UserLog). I cleaned up the code here to work on a simple file. JSW

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Builds an on going list of who saved file when.
'Code must go into the "ThisWorkbook" module!
'All data is placed in column "A" adjust width and formats.

With Worksheets("Sheet1")
.Range("A1") = "This file last saved ""By"" who ""On"", list!"
Range("A65536").End(xlUp).Offset(1, 0) = _
"By: " & Application.UserName & ", On: " & Now & " "
End With
End Sub