how to lock some cells in a sheet without protecting sheet or locking sheet?I do not want to lock all cells only some cells say F5:F40, I5:I40 etc.By protecting sheet all other functions on ribbon are disabled such as font , etc
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
how to lock some cells in a sheet
- Thread starter vra
- Start date
Your answer helped me lot. Still can i have way out that cells having formula cannot changed by anyone? If i lock cells ,protect sheet then though some features are open but still Ctrl+A, font changing of locked cells , trace dependent etc. cannot be done.
Any help will be greatly appreciable.
Any help will be greatly appreciable.
Upvote
0
Highlight the whole spredsheet and in the Font Tab in Home go to the Protection tab and uncheck Locked.
Now highlight all the cells that you would like to lock and repeat the step above this time checking the Locked option.
You can then decide what level of protection these cells have by clicking on the review tab, and then click on "Protect Sheet". Check the option that you would like depending on the different levels of protection you want for the cells. You can then apply this by clicking ok. You can password protect it prior to clicking ok by putting any password in the password option.
I thhink that this is very good and I use it quite a lot for various different functions
Upvote
0
Now i could unprotect my sheet , thanks!! But i did not posted my first issue correctly . Actually if i protect sheet then to change whole sheet formatting at once ctrl+a func cannot be used. Trace dependent /precedent etc cannot be used so is there solution for it so that formulaes are also not changed & above also can be done.
Thanks!!
Thanks!!
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 65,337
- Office Version
- 365
- Platform
- Windows
When you protect the sheet, if you check the "Select locked cells" and "Format cells" boxes then you should be able to use Ctrl+A and format all cells.
As far as I am aware there isn't a way to trace dependents/precedents on a protected sheet.
You could see if something like this is any use. It requires the user to enable macros and is not 100% foolproof. As an example if you had formulas in C7:C22 and G7:H25 that you wanted protected you could try this code. To implement, with the sheet UNprotected ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window.
4. Test
<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br> <SPAN style="color:#007F00">'Change line below to suit your 'protected' range(s)</SPAN><br> <SPAN style="color:#00007F">Const</SPAN> myFormulas <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C7:C22,G7:H25"<br> <br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range(myFormulas)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">With</SPAN> Application<br> .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br> .Undo<br> .EnableEvents = <SPAN style="color:#00007F">True</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> MsgBox "Cannot change cells in " & myFormulas<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
Upvote
0
Similar threads
- Solved
