Securing Excel 2016 Sheet Against Multiple Users Editing Simultaneously

Good morning everyone,

Recently, when preparing Excel Excel sheet for planning and archiving tests, I came across an unusual problem.

In standard, if the spreadsheet is shared over the corporate network, then when it is used by one person, the other person can open it in "read-only" mode.

After creating a password to grant access to only certain people, I set up a password that protects the document from editing by unauthorized persons.

After creating the password, the document can open for editing several users at once.
Only changes made by the last user are saved.

Is there an option to secure the document against several users opening the document for editing?

I attach a sheet in the attachment.
Worksheet password: tests 11

Thank you in advance for your help.
Greetings,
Toczi user

Toczi wrote:

... In standard, if the spreadsheet is shared over the corporate network, then when it is used by one person, the other person can open it in "read-only" mode.
After creating a password to grant access to only certain people, I set up a password that protects the document from editing by unauthorized persons.
After creating the password, the document can open for editing several users at once.

... 1. Only changes made by the last user are saved .
... 2. Is there an option to secure the document against several users opening the document for editing?

Ad 1) Changes in the shared workbook are preserved on the basis of 'negotiating' conflict situations:
01.jpg Download (206.89 kB) 02.jpg Download (222.47 kB) 03.jpg Download (221.61 kB)

It doesn't matter if the last or the first ... If the first saves and the last does not, and they both close the notebook without further saving the changes, then the order does not matter.
I have an old Excel, but there was probably no revolution here ...

Ad 2) If the notebook has been made available to some users there, I do not understand the question ...
The last question excludes the sense of sharing the notebook with many users ...

Of course, you can additionally password the notebook by creating a password to open the notebook ("Password to protect against opening"), or to write a notebook ("Password to protect against changes"), or both - in "Options" and then "Security "or via the" Save as ... "option, then" Tools "=>" General options ", but 'somewhat' undermines the sense of sharing such a file.

You can also use the "Protect and share workbook" option>> "Sharing with change tracking" plus entering the password.

You can also check from the level of macros linked to a specific tab / tabs which user is currently working in the application and limit editing to a minimum, but is it better to do it from the level of company network management by admin?

Hello,

Thanks for the answer.
Unfortunately, the specifics of this worksheet and the users' actions themselves force me to do such solutions.

- The sheet is a living document. Various departments have access to it.
- Unfortunately, some people cannot have access to editing. Read-only (e.g. production).
- The planning department and project leaders need to log in sometimes from different computers (even from a computer to production sometimes) they need to have access to editing. Hence the solution with a password, not assigning permissions.
- Leaders complete the spreadsheet on an ongoing basis, adding new tests to the next empty lines. There is a situation that 2 people fill in another empty row at the same time. Because of the rush, lack of reading messages or other excuses, the cells are overwritten by the last person. I would like to force, and that only one person can edit the document at a time, the rest must wait for the previous one to finish typing.

PS Unfortunately, the conflict message popping up during the second save did not work effectively, and there is no way to get along to let only one person use the spreadsheet.
Currently, I used automatic saving, which slows down the file, but this is only a temporary solution.

Toczi wrote:

... Due to the rush, lack of reading messages or other excuses sometimes, the cells are overwritten by the last person. I would like to force, and that only one person can edit the document at a time, the rest must wait for the previous one to finish typing.
... the conflict message popping up during the second save did not work effectively, and there is no way to get along to let only one person use the spreadsheet ...

Hmm ... then the most sensible solution would probably be to remove the "Workbook sharing" at all ("Tools or Reviews" option) and set up different passwords: one for opening the notebook in general and the other for saving changes in it, you can add protection to the password of the edited spreadsheet itself. Well, unless the file also serves as e.g. a base for printing labels, then such labeling programs even require sharing a file in which changes are made on one side, and downloads data on the other for printing.
So if the file is not shared (shared for multi-edition), the "user" who first opens it and makes changes in it, "wins", and the rest has to wait their turn.

One could set something like this from the macro command level:
1. In the automation options when opening a file, you can set a procedure that reacts to the fact of opening a given notebook (Workbook_Open)
2. This procedure, at the moment of opening the notebook, will check whether the file being opened is already open or not, and "immediately" "reserve" the first free cell / row / sheet for editing only for the given person who is just opening it - a separate matter , how to do it (temporary disabling of sharing, temporary password on the edited sheet / range of cells / etc., marking with some temporary entry of a given cell, etc.)
3. Due to the different behavior of different computer stations, one of them will always start "first" and first "pushes" to the spreadsheet, despite the fact that it started at the same time with another.
However, with this solution it would be a lot of "fun" and the result is uncertain ...

You could try something with VBS scripts, but it's probably a form over content.

You could try with separate notebooks / sheets with the same structure and combined after a day's work into one starting whole for the next day, but it may not be possible ...

Or give up Excel, which is not yet a database, and use access to "real database" (?).