1

If a file is locked for editing Excel allows the file to be opened as read only and saved as different file name. I want to avoid this and stop the file from being opened at all if it is locked for editing by another user.

  • maybe you could create a marco that on file open tests if it is read only, and closes if yes. Save this into your personal.xlsm to have it always available. – gns100 Feb 14 at 17:50
  • That sounds like a possibility. I'll investigate. Thanks. – Bob Baker Feb 19 at 10:19
0

The only way I know of is to password protect the file.

Go to File -> Save AS

On the Save box click Tools -> general options

Enter a password in the Password to open box. Re enter

Then save.

Next time someone wants top open the document they will need to know the password and will not get the option to read only.

enter image description here enter image description here

IF DONE CORRECTLY YOU WILL NOT GET ANY OPTION TO OPEN THE SPREADSHEET WITH OUT HAVING THE PASSWORD, THIS IS TRUE FOR ALL VERSIONS OF EXCEL. SEE IMAGE BELOW FOR REFERENCE

enter image description here

  • The file can be modified by an number of users (and that is fine). When the document is updated the file is saved with the initials of the user and the date. This allows old versions to be seen and to see who (and when) the file has been modified. But we have an issue that some users open as read only and then save as a new version (as they are supposed to) but if the file is open then there may be modifications that do not appear on the latest version. – Bob Baker Feb 17 at 12:17
  • using the above answer with a password to open will prevent read only copies from being opened – PeterH Feb 17 at 13:05
  • Sadly not. It is already password protected to limit the users than can open it. – Bob Baker Feb 17 at 14:08
  • is the password been put in the 'to modify' box ? password to open will not allow a file to be opened without the password – PeterH Feb 17 at 14:20
  • The file is password protected and four users have the password to allow them to open and modify. It is an inventory list so it changes regularly. The problem is that if the file is open then a user can open a read only version and make changes and then there are two versions. I could beat them with a stick (aka Management Control) but I'd rather lock out the possibility. – Bob Baker Feb 19 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.