Consulting

Results 1 to 4 of 4

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

  1. #1

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

    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!

  2. #2
    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
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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.

  4. #4
    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...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •