Show the User Name

[Angiec50]

Hi

I need to add a function to a worksheet that shows the name of the previous person who used the document.

I know about the Function GetUserName, but obviously this just shows the username of the person who has just opened the document. Is there anyway that we show the previous user????

Thanks all for your help

 

[snowblizz]

You would need to take the current username and save it somewhere. This would then be the "previous" username when you open the document the next time.

 

[Angiec50]

You would need to take the current username and save it somewhere. This would then be the "previous" username when you open the document the next time.

This is what I would normally do, but I am wanting it so that if anyone else opens the document it will log their name so I can see who worked on it last. I cant rely on the users copying the cell and saving it somewhere else.

 

[snowblizz]

What I was suggesting was a macro/code that when you open the workbook would "log" the username.

Excel isn't set up for this kind of control really. There's no log of who used the file saved anywhere automatically. That I know of at least.

 

[Angiec50]

Ummm, I am not very good with code, I am just starting to learn it. Do you have any code that I could work with. Is the GetUserName function the best way of doing this, or would it be more simple to have a drop down list with the names of users, and that for them to be able to work on the document they have to select a username first (although they could just select a different user, but it might it be easier)? In which case I presume I would need a different Macro for that.

Sorry, this is just driving me MAD

 

[the_original_invisible]

Enter something like this in to the "ThisWorkbook" area in the Visual Basic Editor (VBE)

To get to the VBE, right click a sheet tab, click View Code, and hopefully you'll see "This Workbook" on the left hand side of the VBE

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheet1.Range("A1").Value = Application.UserName ' change the sheet and range to match your requirements
End Sub

 

[snowblizz]

Check the 4th post in this thread:
http://www.bigresource.com/Tracker/Track-vb-qg02jwncO/

also look at this:

Log files are useful in different situations, specially for developers.
Log files are plain text files that can store information temporary or more permanent.
You don't need much code to create a log file:

Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\FOLDERNAME\TEXTFILE.LOG"
Dim FileNum As Integer
    FileNum = FreeFile ' next file number
    Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
    Print #FileNum, LogMessage ' write information at the end of the text file
    Close #FileNum ' close the file
End Sub

The macro above can be used from other macros like this:

Private Sub Workbook_Open()
    LogInformation ThisWorkbook.Name & " opened by " & _ 
        Application.UserName & " " & Format(Date, "yyyy-mm-dd hh:mm")
End Sub

from
http://www.exceltip.com/st/Log_files_using_VBA_in_Microsoft_Excel/493.html

 

[Angiec50]

Enter something like this in to the "ThisWorkbook" area in the Visual Basic Editor (VBE)

To get to the VBE, right click a sheet tab, click View Code, and hopefully you'll see "This Workbook" on the left hand side of the VBE

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheet1.Range("A1").Value = Application.UserName ' change the sheet and range to match your requirements
End Sub

Thanks for this, I have managed to pop the code into VBE and saved the document as a macro enabled file type, but for this to work I presume I need to write a function into the cell, however, Getusername is not available, have I missed something?

 

[the_original_invisible]

No, if you entered the code in to the "ThisWorkbook" sheet in the VBE, on closing the file it should write the name of the last user name to cell A1 on the first sheet in the workbook

The next time you open it you should see the name of the last user in that cell

Here's a working example

http://www.yousendit.com/transfer.php?action=batch_download&batch_id=MVNlWmdpVnN5UkZMWEE9PQ

 

[Angiec50]

Thanks for this, I have managed to pop the code into VBE and saved the document as a macro enabled file type, but for this to work I presume I need to write a function into the cell, however, Getusername is not available, have I missed something?

Excellent, thanks this works a treat. Didnt think it would be sooooo easy