GetUserName in a shared workbook

[SingularitysPoint]

In a shared workbook, I'm trying to find a solution for users to update a sheet at the same time. We have one Worklist sheet and, if someone edits the update cells on a particular row, on hitting enter (cell change), the details are posted to an ongoing sheet so a pivot table can show the edit history.

Originally, this was causing issues as people would be overwriting the same rows on the Ongoing sheet. I thought I could get the username of the person editing, assign a number to them and post to rows starting at that number, for example 1000, 2000 etc. This would prevent the problem of overwriting the same rows on the Ongoing sheet. It works in getting the username and assigning a number, but if someone else then opens the sheet, the current user is updated for everyone so they effectively lose their number. So, I have it open and am assigned rows beginning 1000. One of my team opens it and it then updates the username in my sheet too, so my number would change to the other person. How do I make it so it keeps the user of that excel instance, rather than the latest one to open it, so everyone can keep their number?

I hope that makes sense!

 

[ranman256]

vUser = Environ("Username")
msgbox vUser

 

[SingularitysPoint]

vUser = Environ("Username")
msgbox vUser

Thank you. I'm already using that, but when someone else opens the workbook, it updates the value in my instance as if it's showing the username for the most recent person to open it? Is there a way to stop that, so each person who has it open keeps their reference number? Or perhaps a number relating to which one they are in the list of people who have it open, such as the first or third, so at least they wills till be posting to a different row?

 

[Jaafar Tribak]

it updates the value in my instance as if it's showing the username for the most recent person to open it

A workaround is to store the username somewhere upon opening the workbook so you can retrieve it whenever you need it.

 

[Jaafar Tribak]

A workaround is to store the username somewhere upon opening the workbook so you can retrieve it whenever you need it.

EDIT: re-read your post. I don't think the above workaround would work for your needs if many users have the shared workbook opened at the same time.

 

[SingularitysPoint]

A workaround is to store the username somewhere upon opening the workbook so you can retrieve it whenever you need it.

Thanks, I've done that, but when someone else opens the sheet while someone else is in it, it's overwritten by the new person - even in the other users' versions.

So, if I open it, it gets my username and assigns a number. When a colleague opens it while I'm in it, my username is overwritten in all open cases, not just theirs. It changes mine to theirs too.

 

[SingularitysPoint]

EDIT: re-read your post. I don't think the above workaround would work for your needs if many users have the shared workbook opened at the same time.

Ah, I'd just replied. No, that's the issue I'm having. It's only registering the latest person to open it, so each person's instance isn't keeping their own name.

 

[Jaafar Tribak]

Ah, I'd just replied. No, that's the issue I'm having. It's only registering the latest person to open it, so each person's instance isn't keeping their own name.

Would getting the computer name instead of the username work for you ?

 

[SingularitysPoint]

Would getting the computer name instead of the username work for you ?

Good idea, but it would still overwrite when someone new opens while others are in it.

The users don't need to keep the same numbers. It's only to assign a row number section for their ongoing data to be copied to, so they're not overwriting. So, if I could just put something to identify them uniquely that doesn't get overwritten when someone else opens the sheet, that's work. For example, if someone new opened the workbook when it's already opened, it reassigns everyone a number, but then I'd need to know everyone who has it open each time.

 

[Jaafar Tribak]

but it would still overwrite when someone new opens while others are in it.

Yes. I forgot that the computer name is equally an environ variable.

How about the volume serial number of the system drive which should be unique to each user:

Something wrapper function like this :

Option Explicit

#If VBA7 Then
   Private Declare PtrSafe Function GetVolumeInformation Lib "kernel32" Alias "GetVolumeInformationA" (ByVal lpRootPathName As String, ByVal lpVolumeNameBuffer As String, ByVal nVolumeNameSize As Long, lpVolumeSerialNumber As Long, Optional lpMaximumComponentLength As Long, Optional lpFileSystemFlags As Long, Optional ByVal lpFileSystemNameBuffer As String, Optional ByVal nFileSystemNameSize As Long) As Long
#Else
   Private Declare Function GetVolumeInformation Lib "kernel32" Alias "GetVolumeInformationA" (ByVal lpRootPathName As String, ByVal lpVolumeNameBuffer As String, ByVal nVolumeNameSize As Long, lpVolumeSerialNumber As Long, Optional lpMaximumComponentLength As Long, Optional lpFileSystemFlags As Long, Optional ByVal lpFileSystemNameBuffer As String, Optional ByVal nFileSystemNameSize As Long) As Long
#End If
 
Function GetSerial(ByVal str As String) As Long
    Const MAX_PATH = 260
    Dim Buf As String
    Dim Serial As Long
    If GetVolumeInformation(str, Buf, MAX_PATH + 1, Serial) Then
        GetSerial = Serial
    End If
End Function

Then call it like :
Debug.Print GetSerial(Environ("SystemDrive") & "\")
I Don't think the SystemDrive environ variable will also get updated.