Macro to extract and retain last users

[Iron_Man]

Hi guys,

I have a workbook in which I'd like to keep track of the last 10 users who modified my workbook.

I currently have the following code to extract the current user but cannot figure out how to modify it to achieve what I want:

Private Sub Workbook_Open()
sheet3.Range("O12").Value = Environ("username")
End Sub

Ideally, I'd like to have in O12 the username of the last user who modified the workbook and in P12 the date and time at which the change was made (or the file was closed, providing a change was made).
Then, in O13, the second last username, etc.

And ideally, have a macro linked to a button where I can erase the whole list of 10 and obviously enter my username back at the top of the list with the appropriate date.

Am I being completely unrealistic or can this be done?

Thanks in advance to whoever can help me

IM

 

[Iron_Man]

Ok I'll do that.

Just two questions:

. what will happen once an 11th person changes the file
. what does i =12 to 4 mean?

I understand the other values you changed but not these two.

 

[Comfy]

Ok I'll do that.

Just two questions:

. what will happen once an 11th person changes the file
. what does i =12 to 4 mean?

I understand the other values you changed but not these two.

The macro will start i = 12 to 4 Step - 1 (Loop through values of i from 12 to 4.)

If i = 12 This Cells(i,20).Value would be Cells(12,20).Value which translates to Range("T12").Value.

So If

i = 12 -- the value in row 11 is copied to row 12

Sheets("Log").Cells(12, 20).Value = Sheets("Log").Cells(12 - 1, 20).Value

i = 11 -- the value in row 10 is copied to row 11

Sheets("Log").Cells(11, 20).Value = Sheets("Log").Cells(11 - 1, 20).Value

.....
i = 4 -- the value in row 3 is copied to 4

Sheets("Log").Cells(4, 20).Value = Sheets("Log").Cells(4 - 1, 20).Value

And then finally the current user and time are added to row 3

Sheets("Log").Cells(3, 20).Value = GetUserName()
Sheets("Log").Cells(3, 21).Value = Now()

So it doesn't matter how many users have made changes. Only the last 10 will be saved in Rows 3 to 12

 

[Iron_Man]

Then Sir, you are a legend!

I can't say anything other than a big thank you.

 

[Comfy]

Then Sir, you are a legend!

I can't say anything other than a big thank you.

Have you managed to get the correct username?

 

[Iron_Man]

I did, it all works well with the initial code you gave me (and the change to put in the cells I wanted in the end).

I will save this thread somewhere with all the details so I can re-use it in the future.

THANK YOU!

 

[Comfy]

I did, it all works well with the initial code you gave me (and the change to put in the cells I wanted in the end).

I will save this thread somewhere with all the details so I can re-use it in the future.

THANK YOU!

Just happy to help.