Hi
New to the site.
Is it possible to protect or lock a series of cells when the worksheet is UNPROTECTED?
I am in my mid 70's but still love Excel and sometimes when the sheet is unprotected I accidentally enter data in a cell that has a formula.
I have learned a lot from this board.
Help
Tom
Is it possible?
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible?
Welcome to Eileen's Lounge!
To lock cells, you have to protect the worksheet. This takes two steps:
1) Select all cells that the user should be able to edit.
Press Ctrl+1 to activate the Format Cells dialog.
Activate the Protection tab.
Clear the Locked check box.
Click OK
(You can also select ALL cells, unlock them as described above, then select the cells the user should NOT be able to edit, and lock them)
2) The above only becomes effective when you protect the worksheet.
On the Review tab of the ribbon, click Protect Sheet.
Use the check boxes to specify what you'll be allowed to do.
At the least, leave the check box 'Select unprotected cells' ticked.
If it is just for yourself, you don't have to specify a password.
Click OK.
The caption of the Protect Sheet button will change to Unprotect Sheet.
If you later on want to edit a locked cell, you can unprotect the sheet temporarily, then protect it again when you're done.
To lock cells, you have to protect the worksheet. This takes two steps:
1) Select all cells that the user should be able to edit.
Press Ctrl+1 to activate the Format Cells dialog.
Activate the Protection tab.
Clear the Locked check box.
Click OK
(You can also select ALL cells, unlock them as described above, then select the cells the user should NOT be able to edit, and lock them)
2) The above only becomes effective when you protect the worksheet.
On the Review tab of the ribbon, click Protect Sheet.
Use the check boxes to specify what you'll be allowed to do.
At the least, leave the check box 'Select unprotected cells' ticked.
If it is just for yourself, you don't have to specify a password.
Click OK.
The caption of the Protect Sheet button will change to Unprotect Sheet.
If you later on want to edit a locked cell, you can unprotect the sheet temporarily, then protect it again when you're done.
Best wishes,
Hans
Hans
-
- PlutoniumLounger
- Posts: 15636
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Is it possible?
Tom, I have two other options besides the regular method suggested to you by Hans.
(1) Wade thigh-deep into Excel VBA programming and learn how to monitor changes in cell contents and have the VBA code
protest if a change is made and then (the VBA code) effects an Edit-Undo. I have never done this, to the best of my 77-year old memory.
(2) Define an Excel style called "Input" where the Format, Patterns only, is set to "shaded" and "pale blue" (or the colour of your choice) For me those pale blue cells tell me "you can type data in here" without going through a process of [un]protecting cells. I do this all the time.
Cheers, Chris
You do not have the required permissions to view the files attached to this post.
There's nothing heavier than an empty water bottle
-
- 4StarLounger
- Posts: 582
- Joined: 14 Nov 2012, 16:06
Re: Is it possible?
You can reserve a certain range where you are allowed to enter data. The range where the calculations take place can be situated elsewhere 'far beyond your reach'.
So structuring the worksheets into areas with separate functions can be helpful too.
Besides: did you consider to use a Userform ? (nomen est 'omen')
So structuring the worksheets into areas with separate functions can be helpful too.
Besides: did you consider to use a Userform ? (nomen est 'omen')