I have a shared workbook that is used by 10 people.

When you click on Tools/Shareworkbook it tells you who has the book open.
Is there a way I can use this data in the workbook itself?

If a user if called "JohnS", could I have a formula in A1 that says

=If(<JohnS has workbook open,"John Sharing","John Absent")

What formula do I put between "<" and ""

Many thanks

Would that be Bilbo or Frodo?

I don't know of a formula that would accomplish what you ask, but the
following macro will paste a list of current users and their status in
columns "A" through "C" on worksheet("Sheet1").

Sub WhoHasIt()

Dim users As Variant
Dim row As Long

users = ActiveWorkbook.UserStatus
With ThisWorkbook.Sheets("Sheet1")
For row = 1 To UBound(users, 1)
.Cells(row, 1) = users(row, 1)
.Cells(row, 2) = users(row, 2)
Select Case users(row, 3)
Case 1
.Cells(row, 3).Value = "Exclusive"
Case 2
.Cells(row, 3).Value = "Shared"
End Select
Next
End With

End Sub


"Baggins" wrote:

I have a shared workbook that is used by 10 people.

When you click on Tools/Shareworkbook it tells you who has the book open.
Is there a way I can use this data in the workbook itself?

If a user if called "JohnS", could I have a formula in A1 that says

=If(<JohnS has workbook open,"John Sharing","John Absent")

What formula do I put between "<" and ""

Many thanks

Tom:

That is brilliant - I am sure I can use it.

Many thanks
Brodo :-)


"TomPl" wrote:

Would that be Bilbo or Frodo?

I don't know of a formula that would accomplish what you ask, but the
following macro will paste a list of current users and their status in
columns "A" through "C" on worksheet("Sheet1").

Sub WhoHasIt()

Dim users As Variant
Dim row As Long

users = ActiveWorkbook.UserStatus
With ThisWorkbook.Sheets("Sheet1")
For row = 1 To UBound(users, 1)
.Cells(row, 1) = users(row, 1)
.Cells(row, 2) = users(row, 2)
Select Case users(row, 3)
Case 1
.Cells(row, 3).Value = "Exclusive"
Case 2
.Cells(row, 3).Value = "Shared"
End Select
Next
End With

End Sub


"Baggins" wrote:

I have a shared workbook that is used by 10 people.

When you click on Tools/Shareworkbook it tells you who has the book open.
Is there a way I can use this data in the workbook itself?

If a user if called "JohnS", could I have a formula in A1 that says

=If(<JohnS has workbook open,"John Sharing","John Absent")

What formula do I put between "<" and ""

Many thanks