Forum Discussion
Christina333
May 17, 2021Copper Contributor
Lock cell without protecting worksheet
Hi all,
Hope everyone is doing well there.
I would like to seek advise from experts here on how to 'lock certain cells without protecting the worksheet.' Is it possible to do so?
My company bought in a software license and we can generate the SPC graph on the same worksheet as the raw data (side-by-side), which is really convenient.
However, in order to make this happen, the worksheet cannot be protected. But there are certain cells i need to protect to avoid the technician from simply changing the info and etc.
Looking forward to receiving feedbacks from the experts out there.
Thank you!
You could use VBA code to prevent some cells from being edited, but that only helps with accidental edits. Someone who knows a bit about Excel can easily circumvent it by either opening the workbook without allowing macros, or by temporarily disabling events.
Here is what you could do:
- Right-click the sheet tab.
- Select 'View Code' from the context menu.
- Copy the code listed below into the worksheet module.
- Change A2,C2,A4,C4 to the list of cells that you want to protect. You can use ranges too: A2:B10,D2:H10
- Switch back to Excel.
- Save the workbook as a macro-enabled workbook (*.xlsm).
- Instruct users to allow macros when they open the workbook.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A2,C2,A4,C4"), Target) Is Nothing Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub
- ajitjoshCopper Contributor
Dear Christina,
This works perfectly, But how to handle this when you need to copy the same set of cells to some other location say 5 lines below the present lines? Please advise.
Thanks
This discussion is about locking cells against being edited.
You appear to want something else, so please start a new discussion.
- MsKimFCopper ContributorTHANKS - i am not a programmer and have been trying to figure this out for years. You made me so happy! The advice other people are giving above does not work.
- ajitjoshCopper Contributor
Hi There, This works perfectly, I have a question, How do i make it work in a macro when i need to copy the same range of cells to a few lines below?
can you please help,
Thanks
email address removed for privacy reasons
This is a long discussion. Which code are you referring to?
- NikolinoDEGold Contributor
Protect individual cells in Excel
1. Start Excel.
2. Switch to the “Check” tab and select “Remove sheet protection”. You may need to allow this with a password if the worksheet is locked.
3. Select all cells by clicking in the top left corner of the table.
4. In the “Start” tab, select “Format> Format cells> Protection” and uncheck “Locked”. Click OK".
5. Now select the cells that you want to protect and choose "Format> Format cells> Protection" again. Check the box next to "Locked" and confirm with "OK".
6. Finally switch to "Check" and select "Protect sheet".Adjust the options in the list to suit your needs.
Lock or unlock specific areas of a protected worksheet
I wish you continued success with Excel (the coolest invention since chocola... uh ... Microsoft! :-)))
and…Please keep asking here - I just taught myself Excel with the help of this forum.
Hope I was able to help you with this info.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
- paritoshkavraCopper Contributor
If we want to protect cell only without protecting sheet, then what to do?
Here you are protecting sheet and my tool does not work for protected sheet.
- NikolinoDEGold Contributor
Protect / lock individual cells in Excel
- First of all, you should prevent Excel from locking the entire document when sheet protection is activated.
- So first select all cells of the table with Ctrl + A or click in the top left corner of the table.
- Right click on any cell and select "Format Cells ...". In the Protection tab, remove the checkmark next to "Locked". Click OK.
- Now, in Excel, use the mouse to select the cells or the range that you want to protect.
- Right-click on it and select “Format cells ...” again. In the Protection tab, check the box next to Blocked.
- Select the “Check” menu in Excel and click the “Protect sheet” button. Assign a password and confirm this twice.
Do not forget!
In "All users of this worksheet may:" submenu
The checkmarks for “Select locked cells” and “Select unlocked cells” should be ticked.
You can also do it with VBA code.
Sub CellProtect() Dim Blatt As Worksheet, rng As Range Set Blatt = Worksheets("Sheet1") Set rng = Blatt.Range(Cells(1, 1), Cells(1, 1)) rng.Select Blatt.Unprotect Blatt.Cells.Locked = False rng.Locked = True Blatt.Protect End Sub
Example file is inserted with VBA code
Additional Infos:
Lock or unlock specific areas of a protected worksheet
I would be happy to know if I could help.
NikolinoDE
Was the answer useful? Mark them as helpful!
This will help all forum participants.
- Keliz123Copper ContributorYou absolutely did not read the question.
- NikolinoDEGold Contributor
You wrote in the Wrong Message and Wrong Person. As far as I can see, I've been writing to another person. If you have any problem with any answer or with myself, you can send me a private message and I will answer you for sure.
- Christina333Copper Contributor
- NikolinoDEGold ContributorI am pleased that you have received a suggested solution for your requirement.
If you found one of the suggested solutions helpful, please mark it as "Best answer" or / with a like. This will be of great benefit to other community members who read here.
Always welcome.
Wish you a nice day / night with a lot of health, joy and love.
Nikolino
I know I know nothing (Socrates)