Who's the last saver of this excel worksheet?

[d4d4ng]

Hi All,

This is my first post in this forum. Glad to know this community

I have an excel worksheet that was shared on a network. Everyone that has access to it can edit and save it but I want to make sure who was the last. If any wrong was data entered in the file, I can warn them to be careful to edit.

What I mean is the last saver identity/name should be put in a cell of that worksheet. I should imagine of a function to achieve it.

Would highly appreciate if anyone can show me the light to do it.

Thanks in advance.

d4d4ng

 

[VoG]

Try like this in the ThisWorkbook module. Change the sheet name and cell to suit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A1").Value = Environ("username")
End Sub

 

[d4d4ng]

Dear VoG,

Wow, what a great community !

I really appreciate it that I got an answer in three minutes.

I will apply it into my worksheet and report to you the result

Thanks again VoG

Regards,
d4d4ng

 

[d4d4ng]

Dear Peter,

I have tried the module you gave me.

My excel workbook is named "MyBook.xlsm" and I want the latest user's name saver of the file is inserted into cell "A1" of "Sheet1" sheet.

So I have to get the module as the following:

Private Sub MyBook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As  Boolean) 
Sheets("Sheet1").Range("A1").Value = Environ("username") 
End  Sub

Is the module above right?
I tried it and got an error : #NAME?
What's I have done wrong?

What should I do?

Regards,
d44ng

 

[VoG]

You need to use the exact code that I posted here http://www.mrexcel.com/forum/showpost.php?p=2864889&postcount=2

Press ALT + F11 to open the Visual Basic Editor, in the Project window double click ThisWorkbook and paste in the code.

 

[d4d4ng]

Dear Peter,
Thanks for your quick response.

Let me tell you of what I have done

1. Excel file name : MyBook.xlsm
2. Spreadsheet name : Sheet1
3. Pressed ALT + F11 to open Visual Basic Editor
4. Double click ThisWorkbook and pasted the code in.
5. Enabled the macro
6. Saved the file and open it again, but alas, I didn't see my username in the A1 cell

Can you show me what I have missed?

Regards,
d4d4ng

 

[VoG]

And did you use this exact code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A1").Value = Environ("username")
End Sub

I've just tested it and it works as soon as I click the Save icon.

The name of the workbook is irrelevant but it must be an .xlsm file.

 

[d4d4ng]

Dear Peter,
Thanks a lot.

I have checked the code in ThisWorkbook to make sure that it's exactly same as your code, and as soon I clicked the "Save" button, my username pop up in the target cell

BTW, if somebody else just open the excel file, is the last username still recorded in target cell?

Best regards,
d4d4ng

 

[VoG]

To have the user name appear when the file is opened, add the following underneath your existing code

Private Sub Workbook_Open()
Sheets("Sheet1").Range("A1").Value = Environ("username")
End Sub

 

[d4d4ng]

Dear Peter,

Thanks for your help to solve my problem.

I'm really glad to join this community. Hope that if I stumbled upon excel problems, I can seek help from you all.

Best regards,
d4d4ng