I have a spreadsheet that has formulas that I want to lock once the information in the row is completed, (there is a discharge date). I want the end-user to be able to delete the row and insert it into another worksheet. I do not want to use VBA; I was successful in sequencing the protection steps, but now cannot repeat the process. There has to be a way to use the protect sheet "delete rows" check box and locked cells together to enable this process.
Excel – Delete rows with locked cells in Excel 2010
microsoft-excel-2010
Related Solutions
As the message indicates, you're trying to insert rows into a Table in your sheet. Typically, but not necessarily, tables will have banded formatting. When you click in a cell in a table, the Table tab will appear, like so:
The Insert and Delete commands get grayed out when there's more than one table intersecting the row you're trying to delete (and maybe other times):
I haven't figured out how to "Insert Copied Cells" into just a table row in one step. Instead I insert a row, or rows, into the table and then copy the content. To insert a row into the table, right-click a cell in the table and choose Insert:
You can turn the tables back into normal cells. Do it like this:
You want only macro changes to take effect? Protect the user interface:
Sheet1.Protect Password:="Password", UserInterfaceOnly:=True
Best Answer
This has to do with the nature of worksheet protection. The purpose of locking cells is to prevent users from changing the contents. If users delete rows, they will be permanently changing the cells, so it makes sense that Excel doesn't allow users to delete rows with locked cells. What's confusing is that Excel gives you the option to select "delete rows" in the worksheet protection dialog, even though that setting doesn't seem to do anything. The only complete solution is to use macros to unprotect the sheet, delete/copy the rows, and protect it again. If you want the cells to be locked for most users, but allow some users to delete rows, another option is to use the "allow users to edit ranges" feature to allow particular users or users with a password to modify cells even though they are locked. See How to Sort Locked Cells in Protected Worksheets That article is about sorting, but it explains the relevant concepts well.