Show user that last modified workbook in a cell on a workbook

[mandy4514]

I want to show users the last person to save the workbook in the workbook for other users to see. How can I do this?

This form was last edited by:

(automatically generate name here)

<tbody>
</tbody>

 

[AkaTrouble]

press CTRL + F11 keys to open VBE (visual basic editor) top left click ThisWorkbook

put this code in ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Sheets("Sheet1").Range("A1").Value = Application.UserName
Sheets("Sheet1").Range("A1").Value = Environ("username")

End Sub

change the sheet and cell reference to your desired location

note i have put a comment line you can choose which username you want saved or can put both in seperate cells

the workbook will need to be saved as macro enabled XLSM

 

[mandy4514]

Can I reference multiple Sheets?

 

[AkaTrouble]

yes just repeat the code for as many sheets as you want

 

[AkaTrouble]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Sheet1").Range("B1").Value = Application.UserName
Sheets("Sheet1").Range("A1").Value = Environ("username")

Sheets("Sheet2").Range("B1").Value = Application.UserName
Sheets("Sheet2").Range("A1").Value = Environ("username")

End Sub

this will put application username in cell B1 and Environment username in cell A1 on both sheet1 and sheet2

 

[mandy4514]

So I want one cell to show any change in the entire workbook that contains like 30 sheets that users can edit. Then once I add this code, how do I pull it into a cell?

Thanks for your assistance.

 

[AkaTrouble]

the code i put will place the user name of the person using workbook as it closes

if you tell me the sheet name and the cell you want the name in i can modify the code for that location

if you want it to only activate on save then use before save code instead of on close

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets("Sheet1").Range("B1").Value = Application.UserName
Sheets("Sheet1").Range("A1").Value = Environ("username")

End Sub

if you want to record any change in the workbook with username that is a bit more complicated but can also be done but would require a log sheet to be added

my code saves to sheet1 cell A1 and B1 this can be changed to any sheet and cell .. the username is entered as the workbook closes so will not be visible until you open the workbook again

 

[mandy4514]

 

[AkaTrouble]

try this code in ThisWorkbook module

i have concatenated both usernames into one string this can be changed

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Consolidated Summary").Range("G4").Value = Environ("username") & " " & Application.UserName

End Sub

 

[mandy4514]

So I changed to Sub Test() and was able to pull in the info but it is pulling two pieces of info and I only want the full name not the username is this possible?