list of shared workbook participants

I would like to know if you could help me will the below VBA code.

basically, I have the below code to return the user name in a cell:

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
ReturnUserName = UCase(Trim(tString))
End Function


It works, but only for the current workbook. When the workbook is shared, it only returns the current username, not a list of the people using the spreadsheet.

Use the UserStatus property of the Workbook object to list users of a shared workbook

From VB help:

UserStatus Property

Returns a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list. The first element of the second dimension is the name of the user, the second element is the date and time when the user last opened the workbook, and the third element is a number indicating the type of list (1 indicates exclusive, and 2 indicates shared). Read-only Variant.

Remarks

The UserStatus property doesn't return information about users who have the specified workbook open as read-only.

VB help example:

UserStatus Property Example

This example creates a new workbook and inserts into it information about all users who have the active workbook open as a shared list.

Please Login or Register  to view this content.

Hope this helps,
theDude