Protecting cells and total workbook

[mrwul62]

I don't know if at all the following would be possible and if so what the best solution might be.

For the weekly competition in our bridgeclub an Excel workbook/sheet is being used.

The workbook is protected (not with a password, i.e. it is easy to unprotect it)
Someone adds the results to the sheet, protects it, and distributes the sheet to members.
There are 2 persons that are doing this.
Regretfully neither one has any Excel experience whatsoever and often formulas are deleted or written over.

In such cases, there are simply no results, until someone has found some old, already used, Excel workbook, with the formulas still in place. It is a clumsy way of working.

It is a big workbook, with quite a number sheets and complicated formulas.
The person who created this workbook regretfully passed away.

I know there is a way to protect the cells containing the formulas and leave open only those that should be filled.
That is the first protection.

The results are distributed, but before doing so, the whole workbook should be protected.
So the workbook has '2 layers' of protection.

Q: Is it possible to remove -only- the workbook protection, without removing the cell protection?
Maybe cell protection should be protected with a password?

Thanks!

 

[BadgerRoad]

You can protect the Worksheet and the Workbook separately, assuming you are using a later version of Excel. I think it started in Excel 2007, but I'm not entirely sure. Go to the Review Tab and you will see two options, one for protecting the Workbook and one for protecting the Worksheet.

 

[mrwul62]

You can protect the Worksheet and the Workbook separately, assuming you are using a later version of Excel. I think it started in Excel 2007, but I'm not entirely sure. Go to the Review Tab and you will see two options, one for protecting the Workbook and one for protecting the Worksheet.

The worksheet/workbook they use is still the .xls type and includes '28 sheets' (tabs).

Within all these sheets specific regions should be protected (containing the formulas)
other parts are unprotected (for filling in names and results per round)

 

[BadgerRoad]

The worksheet/workbook they use is still the .xls type and includes '28 sheets' (tabs).

Within all these sheets specific regions should be protected (containing the formulas)
other parts are unprotected (for filling in names and results per round)

Is there is a question in your response?

 

[mikerickson]

Archive records (e.g. who won last week's Swiss Teams event) shouldn't involve formulas.
I would recommend converting all the formulas to their values before distributing the sheet to the club members.

If you (or some Excel savy coder) could code the workbook to have a "Prepare workbook for distribution" button to create the worksheet that the two officers send to everyone, that would probably work.

 

[mrwul62]

Is there is a question in your response?

No, the question is in my first post, the 2nd one is just to provide additional explanation.

 

[mrwul62]

Archive records (e.g. who won last week's Swiss Teams event) shouldn't involve formulas.
I would recommend converting all the formulas to their values before distributing the sheet to the club members.

If you (or some Excel savy coder) could code the workbook to have a "Prepare workbook for distribution" button to create the worksheet that the two officers send to everyone, that would probably work.

These are 70+elderly ladies, they just fill in and distribute the sheet. To illustrate: the last time it took me a few screen shots and to explain how to ctrl-c / ctrl-v a formula that they had overwritten by hitting the space or so.
That is the reason that I was considering to 'improve' the sheet in a way that the formulas are protected against overwriting. So they can fill in, without messing up the formula, then protect (they hit the button protect, no password) and send the sheet out.

Maybe I want to assist in a way that the cells containing formulas are protected with a password and the overall sheet protected without a password.
I tried myself with some test Excel sheet, but could not figure out whether it will work.

So, as per my first post:
1. formula's protected with password
2. overall Excel sheet protected, but without password

Maybe it isn't possible at all?

 

[BadgerRoad]

These are 70+elderly ladies, they just fill in and distribute the sheet. To illustrate: the last time it took me a few screen shots and to explain how to ctrl-c / ctrl-v a formula that they had overwritten by hitting the space or so.
That is the reason that I was considering to 'improve' the sheet in a way that the formulas are protected against overwriting. So they can fill in, without messing up the formula, then protect (they hit the button protect, no password) and send the sheet out.

Maybe I want to assist in a way that the cells containing formulas are protected with a password and the overall sheet protected without a password.
I tried myself with some test Excel sheet, but could not figure out whether it will work.

So, as per my first post:
1. formula's protected with password
2. overall Excel sheet protected, but without password

Maybe it isn't possible at all?

You can do what you you are asking.
1. You need to unlock each cell that you want to allow the users to edit. You need to unlock each cell you want to allow to be edited. To do this, right click the cell, select Format Cells, then under the Protection Tab uncheck the Locked box. That will unlock that cell, once the worksheet is protected. Do that for each cell you want to allow to be edited. After that you need to protect the worksheet. To do so, under the Review Tab select Protect Sheet. A password is optional. There are also various options for the level of protection to want to implement.
2. To protect the Workbook, under the Review Tab select Protect Workbook. Once again, a password is optional.
There are other ways to perform these steps, but this is the way I do it. If you have problems please advise.

 

[mrwul62]

Yes, I did so.

The situation is that they can only access the unlock cells and whilst using the [Tab] or [Enter] key, they move from one cell to the other.
Unable to come outside the 'free/unlocked' cells.
Within each sheet, the protected / locked cells are protected with a password.
Again, the cells where names and results should be entered, those only can be edited.
Basically there is no need to remove the protection.
So far fine.

Now, the 2nd step is that the workbook needs to be distributed.

Workbook protection, either with or without password, doesn't work: the unlocked cells can still be edited.
My guess is this feature isn't (yet) supported.
(Export to PDF isn't an option, too complicated)

 

[BadgerRoad]

Yes, I did so.

The situation is that they can only access the unlock cells and whilst using the [Tab] or [Enter] key, they move from one cell to the other.
Unable to come outside the 'free/unlocked' cells.
Within each sheet, the protected / locked cells are protected with a password.
Again, the cells where names and results should be entered, those only can be edited.
Basically there is no need to remove the protection.
So far fine.

Now, the 2nd step is that the workbook needs to be distributed.

Workbook protection, either with or without password, doesn't work: the unlocked cells can still be edited.
My guess is this feature isn't (yet) supported.
(Export to PDF isn't an option, too complicated)

What are you trying to accomplish by protecting the Workbook?