Password Protect Specific Range Of Cells

[trip_to_tokyo]

In EXCEL 2007 I want to do the following:-

1. Have a Worksheet into which I can enter,edit and save data freely in
(nearly) all cells.

2. In the same Worksheet I want to password protect a specific cell range
(say D83 to D85 but the cell references are immaterial).

How do I do this please?

I know how to protect a Workbook.

I know how to protect a Worksheet within a Workbook.

Thanks for any replies.

 

[Jacob Skaria]

All cells are locked by default, but locking does not mean anything until
you protect the worksheet from Tools>Protection menu.

--First select any cells that a user is allowed to change
--Hit Ctrl+1 and go to the 'Security' tab to unlock these cells.
--Go to Tools->Protection->Protect Sheet, and you can assign a password
(optional)


If this post helps click Yes

 

[JLatham]

Sometimes it's easier to select all cells, go to the Security tab and change
that to the All Cells Unlocked state. Then go back to the worksheet and
select only the few cells that are to be locked, and with them selected, go
back and set them to Locked in the Security tab. Then apply protection to
the sheet.

 

[trip_to_tokyo]

Hi Jacob. Thanks for your input first of all.

I have just come round to testing this on Monday Oct 5/9.

1. Your input does not seem to relate to EXCEL 2007 which is the version in
which I am working.

2. (a) Following your instructions this is what I do in EXCEL 2007:-

2. (b) “First select any cells that a user is allowed to changeâ€

I highlight the whole Worksheet in which I am working to achieve what you
have said.

2. (c) “Hit Ctrl+1 and go to the 'Security' tab to unlock these cellsâ€

I hit the Ctrl and the number 1 keys simultaneously and the Format Cells pop
up window launches.

2. (d) I select the tab called:-

Protection

2. (e) In the:-

Protection

- tab there is a field called:-

Locked

There is a green tick in the field called:-

Locked

I remove that green tick and hit OK.

2. (f) I believe at this point that I have now done what you asked me to do
at 2 (c) above and that ALL of the cells for the Worksheet in which I am
working are now unlocked.

3. (a) “Go to Tools->Protection->Protect Sheet, and you can assign a passwordâ€

3. (b) In EXCEL 2007 I now do the following to try and comply with what you
have said immediately above.

3. (c) Highlight the range D8385 (these are the cells that I wish to
password protect).

3. (d) Home / Cells / Format / Protect Sheet.

3. (e) A Protect Sheet pop up window appears in with the following 3 fields
all have a green tick in them:-

- Protect worksheet and contents of locked cells

- Select locked cells

- Select unlocked cells

3. (f) All I do in the Protect Sheet pop up window is to enter the word:-

nochange

- into the field called:-

Password to unprotect sheet:

3. (g) I hit OK.

3. (h) I enter the word:-

nochange

- into the Confirm Password pop up window and hit OK.

4. I now go into cell:-

D83

- and change the value that appears there.

The change is accepted without a password. ***** THIS APPEARS TO BE
INCORRECT ***** I was expecting to have to enter a password into cell D83 if
I tried to change its contents.

So, as far as I can see, what you have said does not bear up to testing
(unless I have done something wrong).

I notice that a MVP has commented below you so I shall look at those
comments over the next day or so to see if I can get what is contained there
to work.

Thanks.

 

[trip_to_tokyo]

1. Let me test the above comments.

2. Open the file named:-

c:\excel\rainbowNEW.xlsx

yr 2008

- Worksheet.

3. (a) JLatham says:-

“Sometimes it's easier to select all cellsâ€

3. (b) I therefore select all the cells in the Worksheet in which I am
working:-

yr 2008

(I did this by single left hand clicking in the cell in the top right hand
corner: the one to the left of the letter A and above the number 1).

I have now therefore selected all the cells.

3. (c) JLatham goes on to say:-

“go to the Security tab and change that to the All Cells Unlocked state.â€

I therefore take the following actions (which seem to be the same as Jacob’s
so I have copied, pasted and amended what follows).

3. (d) “Hit Ctrl+1 and go to the 'Security' tab to unlock these cellsâ€

I hit the Ctrl and the number 1 keys simultaneously and the Customer Lists
pop up window launches.

3. (e) I select the tab called:-

Protection

3. (f) In the:-

Protection

- tab there is a field called:-

Locked

There is a green tick in the field called:-

Locked

I remove that green tick and hit OK.

3. (g) I now seem to be at the following point then:-

“go to the Security tab and change that to the All Cells Unlocked state.â€

This seems to be the same as Jacob at this point which was:-

“I believe at this point that I have now done what you asked me to do at 2
(c) above and that ALL of the cells for the Worksheet in which I am working
are now unlocked.â€

4. (a) JLatham now goes on to say:-

“Then go back to the worksheet and select only the few cells that are to be
locked, and with them selected, go back and set them to Locked in the
Security tabâ€

I therefore take the following actions to try and achieve the above (JLatham
seems to say something similar to Jacob).

4. (b) In EXCEL 2007 I now do the following to try and comply with what you
have said immediately above.

4. (c) Highlight the range D8385 (these are the cells that I wish to
password protect).

So now I am here:-

“Then go back to the worksheet and select only the few cells that are to be
locked, and with them selectedâ€

4. (d) I now need to:-

“go back and set them to Locked in the Security tabâ€

**** AT THIS POINT PROCESS CHANGES FROM WHAT JACOB SAID *****

4. (e) I now need to take the following actions to try and comply with:-

“go back and set them to Locked in the Security tabâ€

4. (f) “Hit Ctrl+1 and go to the 'Security' tab to unlock these cellsâ€

I hit the Ctrl and the number 1 keys simultaneously and the Custom Lists pop
up window launches.

4. (g) I select the tab called:-

Protection

4. (h) In the:-

Protection

- tab there is a field called:-

Locked

There is NO green tick in the field called:-

Locked

I ADD a green tick and hit OK.

4. (i) I now seem to be at the following point then:-

“Then go back to the worksheet and select only the few cells that are to be
locked, and with them selected, go back and set them to Locked in the
Security tab.

5. (a) I now need to move on to the final step which is:-

“Then apply protection to the sheet.â€

5. (b) In order to achieve the above I now take the following steps.

5. (c) Home / Cells / Format / Protect Sheet.

5. (d) A Protect Sheet pop up window appears in with the following 3 fields
all have a green tick in them:-

- Protect worksheet and contents of locked cells

- Select locked cells

- Select unlocked cells

5. (e) All I do in the Protect Sheet pop up window is to enter the word:-

nochange

- into the field called:-

Password to unprotect sheet:

5. (f) I hit OK.

5. (g) I enter the word:-

nochange

- into the Confirm Password pop up window and hit OK.

6. I now go into cell:-

D83

- and change the value that appears there.

***** EXCEL CORRECTLY PREVENTS ME FROM CHANGING CELL D83.

7. I try to change the values in cells:-

D84 and D85 and EXCEL correctly prevents from changing those cells.

8. I try to change cells:-

A88
A89
A90

- and EXCEL correctly allows me to change those cells.

As far as I can see this passes testing!

Looks good to me: thanks a lot!

 

[JLatham]

Glad you got it to work for you.

The difference between my instructions and Jacob's set is really based on
how many cells you need to protect or unprotect. Since all cells are, by
default, in the Locked state, if you only have a few cells that need to be
unlocked, it's simpler to select those few cells and go to the Security Tab
and unlock them.

But if you only have a few to lock and many to be unlocked, then the reverse
is true: easier to first unlock them all, then go back and just select and
lock the few that need to be locked/protected.

Either process will work, so long as you know the initial state of the cells
you'll be working with, and after deciding which is the easier path to take:
1. Start with all locked and just unlock a few, or
2. Start with all locked, go Unlock them all, then just lock the few once
more.

 

[trip_to_tokyo]

Thanks for your, and Jacob's, input; it is appreciated.

I must admit I don't like the way MS have built this functionality; it's
confusing to say the least.

You mention about, "Since all cells are, by default, in the Locked state" -
this, to me anyway, seems to be the wrong starting point, but maybe there is
some underlying architectural reason for that.

It would seem more intuitive to me if all cells started off in an,
"un-locked" state and then you work forward from there and, "lock" the cells
that you want to.

However . . . those are issues that are not relevant to the original one
that I raised.

Once again, thanks.