Hans J.Hau
asked on
How to lock an entire worksheet from user deleting data in cells but allow everything else?
This may seem like a dumb question and I may be stupid for asking this in the first place but I tried the obvious and still I can't get my worksheet to lock delete cell data only.
I made sure to activate locked cell protection on Format Cells:
And I even select all the checkboxes in Protect Worksheet except for Delete Rows and Delete Column (since I don't want users to do that).
But all I get from that is either the worksheet still allowing me to delete or the entire worksheet to stop responding and crash. Is there a reason for this? Or am I doing it wrongly?
I made sure to activate locked cell protection on Format Cells:
And I even select all the checkboxes in Protect Worksheet except for Delete Rows and Delete Column (since I don't want users to do that).
But all I get from that is either the worksheet still allowing me to delete or the entire worksheet to stop responding and crash. Is there a reason for this? Or am I doing it wrongly?
Hi,
After having selected 'Locked' to all relevant cells, you can run macro like
to lock whatever 'Locked' cells, within current Sheet.
After having selected 'Locked' to all relevant cells, you can run macro like
ActiveSheet.Protect Password:="your_pass"to lock whatever 'Locked' cells, within current Sheet.
ASKER
@ProfessorJimJam I think I wasn't clear on that question... But what I meant to say is that I don't want people to delete data WITHIN the worksheet. Not delete the worksheet entirely.
@HuaMinChen Won't that block any kind of interaction inside the locked cells? I want users to be able to insert data inside worksheet but block them from deleting.
@HuaMinChen Won't that block any kind of interaction inside the locked cells? I want users to be able to insert data inside worksheet but block them from deleting.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So with Protect Sheet activated, I cannot enter/delete data even if I check all the checkboxes? If so is there any other way to do this, like using VBA?
Yes, you can use VBA macro to protect some fields and also to leave some other fields "unprotected" per your needs.
@Hans J.Hau
if you want to prevent users from deleting cells, or columns or rows.
then simply you need to only leave two boxes ticked and that's it.
please see the screenshots.
if you want to prevent users from deleting cells, or columns or rows.
then simply you need to only leave two boxes ticked and that's it.
please see the screenshots.
ASKER
But that also means that they cannot key in data.... This may work if a small group of people are using (they can protect when not in use and unprotect it to enter new data), but this worksheet will be used by an entire division in my company who may not know Excel features like this or may forget to protect the sheet. I just want to block delete only as a safety measure.
Hans,
You can create macro to let the user be able to unprotect some parts of the sheet.
You can create macro to let the user be able to unprotect some parts of the sheet.
@Hans J.Hau
you misunderstood my message. the users can surely enter the data, but they cannot delete any cell or column or row.
i select an specific range if you want to lock, or you can select the entire worksheet then right click and on the protection untick the "Locked"
then click ok then go to Review tab and protect worksheet and only leave the first two boxex marked.
then click ok and you will see that any user can enter data, but deleting cell or row or column is not possible.
i have recorded 21 seconds video for you that demonstrates what i mentioned. please see attached video
Screen.mp4
you misunderstood my message. the users can surely enter the data, but they cannot delete any cell or column or row.
i select an specific range if you want to lock, or you can select the entire worksheet then right click and on the protection untick the "Locked"
then click ok then go to Review tab and protect worksheet and only leave the first two boxex marked.
then click ok and you will see that any user can enter data, but deleting cell or row or column is not possible.
i have recorded 21 seconds video for you that demonstrates what i mentioned. please see attached video
Screen.mp4
ASKER
OK I see your point... But that only works for right-click Delete. Its not going to stop them from clicking a cell and press Backspace to remove the data.
Hans
If you want to protect the workbook so that users can enter data but wouldn’t be able to edit or erase it , then this is not possible in excel with its built in features.
However, there is an alternative which can be done with VBA that a code is placed within worksheet change event that when the cell gets data then the cell with data gets locked and then once the user entered data then user cannot edit or delete its entered content, however, I do not recommend this option, because it will relly on the macro and if users do not enable macro , the automatic worksheet event will not work.
If you want to protect the workbook so that users can enter data but wouldn’t be able to edit or erase it , then this is not possible in excel with its built in features.
However, there is an alternative which can be done with VBA that a code is placed within worksheet change event that when the cell gets data then the cell with data gets locked and then once the user entered data then user cannot edit or delete its entered content, however, I do not recommend this option, because it will relly on the macro and if users do not enable macro , the automatic worksheet event will not work.
You can delete the question, but the answer selected for closure is not correct.
protecting worksheet only protects the content of worksheet, where the worksheet is the parent and all objects inside the worksheets are childs
and worksheet parent is workbook, so you need to protect workbook structure to disable deleting worksheet.
see the icon "protect Workbook"