Excel-Macro to close the file if is in use by another user

Occasional Contributor

Good Morning
To avoid Onedrive synchronization errors, is there an effective way so that when entering the Excel file, if it is already open, a message appears that it will close and when pressing OK, close the file?
I think this way it would be possible to avoid conflicts between users. Is there any effective macro for this problem?. Thank you for your help

3 Replies

@csoares 

 

Why should someone be able to open a file even though he is not allowed to see it?

Why not provide password protection when opening the file?

 

Protect an Excel file

To prevent others from accessing data in your Excel files, protect your Excel file with a password.

 

Excel file is locked for editing

You are trying to open an Excel file and you get a message about the file being locked. Sometimes the file is locked by another user, and other times the "another user" is actually you. If you have locked the file yourself, it might be because the file is open on a different device, or the previous instance of the file didn't close properly.

Tip: Sometimes a file may get locked if everyone editing isn't using a version that supports co-authoring. To co-author together, make sure everyone is using the Microsoft 365 subscription version of Excel or Excel for the web.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

Hello good morning, we are all using office 368 premium and Family right now.
In relation to having a password in the file, I don't think I need it, because everyone can access the file, there is no need for that kind of security.
The problem is that if a second user enters the file already open, Onedrive will give a sync error message and suggest having 2 files.
So I wanted excel not to let the file be used by more than 1 person. When the second user enters, the macro verifies that it is open and closes the file. I hope I'm explaining well the problem I want to solve. Thanks

@csoares 

Then this link should help you.

Collaborate on Excel workbooks at the same time with co-authoring

You and your colleagues can open and work on the same Excel workbook. This is called co-authoring.

To co-author in Excel for Windows desktops, you need to make sure certain things are set up before you start. After that, it just takes a few steps to co-author with other people.

 

Thank you for your patience and understanding

 

Nikolino

The solution is always simple. You just have to find it :).
(Alexander Solzhenitsyn)