Code to return name of user who has locked workbook in MsgBox

[MooseAUH]

Hi all and a Happy New Year

I have a workbook saved on our network drive. I have been able to create a code to restrict users from accessing a ReadOnly version, in other words only 1 person can have the file open at a time.

What I want to achieve now is for the name of the person who has the file locked to appear in a MsgBox when a 2nd user tries to open the file. He will receive a message that ReadOnly is disable and needs to contact person x. I know that Excel already has a popup to show who has locked the file, and then provides options to click ReadOnly or Notify. The reason I want the name in my own MsgBox is so that the user does not have to re-open the file to find out who is locking the file - in case the user did not pay attention to the Excel popup!

I have been searching this forum and have found a solution to the problem, now I just need some help getting it to work.

I have the following code in my ThisWorkBook:

Please Login or Register  to view this content.

The bit of code which I need to get working is:

Please Login or Register  to view this content.

This is the problem I am facing:

1. When I enter this code in a new WorkBook it works, when I try to add the code to this workbook it is showing me errors to define Users = and For Row =. I defined them as Dim Users as String and Dim Row as Integer but that does not seem to work. When the code is entered in a new WorkBook with only this code in the ThisWorkBook section VB does not ask me to define anything ...
2. In my message box, if i delete "The current user is: & Sheets("User").Range("a1")", _ it works, but with the line added it gives me an error and shows the line in red , perhaps I am doing something wrong with my " _ & ( ) characters

Help will be much appreciated. I am just about to give up on the idea.

[LJMetzger]

Try

Please Login or Register  to view this content.

The following code works for me in Excel 2003:

Please Login or Register  to view this content.

Lewis

[MooseAUH]

Hi Lewis,

Thank you for your reply, I am on Excel 2010. I found out where to find the information Debug.Print populates. In VBA press <kbd>Ctrl</kbd>+<kbd>G</kbd>

This great, but now I have no idea how to get this info to display in my MsgBox, at the moment the code refers to a cell:

Please Login or Register  to view this content.

I'll start searching online for the answer.

[LJMetzger]

Sorry about the debug.print (I should have mentioned that CTRL G allows you to see the 'Immediate Window' where debug.print writes). You can also change variable values in the 'Immediate Window' while debugging.

I do not have access to a multi-user system, so I am only guessing that the current user information is:
Users(1, 1) is the User Name
Users(1, 2) is the User Log On Time
Users(1, 3) is 1 for Exclusive Use and 2 for Shared Use

The use of 'row' as a variable name is OK, but probably a bad choice, because
it looks a lot like an Excel VBA Keyword, which can't be used as a variable name.
See the link for Excel keywords:
http://msdn.microsoft.com/en-us/libr...=vs.90%29.aspx

Try cut and paste from the following code (tested using Excel 2003 on a single user system):

Lewis

Please Login or Register  to view this content.

[MooseAUH]

Hi Lewis,

I've tried the code on our network and does not work. When I have the file open, and run the Macro, it does work and returns the info. When i try on to access the file on a 2nd system I get a run time error and then the file opens as a Read-Only.

I think the problem could be that the 2nd system is also trying to get the Current User Name, but cant because the system is already open :S

[xlbiznes]

On which line does the code(provided by LJMetzger ) error out.

[MooseAUH]

The error is on the following line:

Please Login or Register  to view this content.

I think what is missing from the code is something like this: "When 2nd user opens workbook and ReadOnly=True" then "display the sprompt showing who has workbook open as ReadOnly=False" then "close the workbook".

For the life of me I just cant figure it out.

[LJMetzger]

Microsoft says: "The UserStatus property doesn't return information about users
who have the specified workbook open as read-only." See:
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

If you can't fix the problem, I offer a poor person's alternative. You can write the information into a separate Excel file. Attached is a file that does that, written and tested on a Single User Excel 2003 system. I imagine a few modifications may be required for a shared access system. The code in the file follows.

Lewis

Please Login or Register  to view this content.

[MooseAUH]

Hi, Xlbiznes, Lewis,

Thank you so much for your help and time!

Lewis, your poor man's alternative works! What I did was paste your code in a module, then added the following code to ThisWorkbook:

Please Login or Register  to view this content.

It is tested and works as desired on a shared network!

Thanks again!
Mike