Solved: Show custom error message if file already opened by someone else

[hunsnowboard]

Hi there Everyone! I have a very annoying problem. At our company there is a file which is used by many people. At this moment it is shared, so everyone can access it but this way (as many do not really understand excel) they save over each others rows, so many rows disappear. I would like to do the following with this file so no data will be lost, and everyone can save their data.
I would like to make something which works like this:

1. When I open the file (and it is not opened by anybody) I should get a userfrom asking for my name (combobox with names).
2. Then if the file is opened (by me) and somebody else tries to open it than he or she should get an error message, saying that: "This file is opened by John and you can access to it only when it is closed".

So (in case the file is opened by someone else) instead of getting the "open the file as read-only" the users should get an error message which says that they cannot access the file and a also show the name of the user who is working with the file. I hope you understand my problem and can help!
Thank you in advance!

[Jan Karel Pieterse]

You will always get the readonly message if you unshare the file. After that, you can test for the readonly status of the workbook to determine if you need to show your userform:

If ThisWorkbook.ReadOnly Then
UserForm1.Show
End If

[Kenneth Hobs]

As Jan said, the easier method is not to share the file.

If you want to pursue the matter, I recommend first making an audit trail. You can add one via a worksheet, an external file like a text file or an Access MDB table. An example using the latter method is in this thread.
http://vbaexpress.com/forum/showthread.php?t=22620

It is then just a matter of checking the CloseDate field in the last record. If empty, then the user in that record has the file open. The Open event would check it and then add your username or show the message and close if open already.

[hunsnowboard]

Well, I hoped I can get thru the readonly message. And then I will test if the file is readonly and run my user form.... this is (I think) the easiest. (I hoped that with the application.displayalerts I can disable the readonly...)