Allowing row deletion in worksheet with locked cells

M

MT

I have a need to create an Excel file that has one column that contains
values that I don't want the user to be able to change, however, they can add
new rows (the locked column would be blank) and delete rows. I unlocked the
columns that they can manipulate, locked the one column and then when I
protected the worksheet I said to allow users to insert and delete rows. I
can add a row but I cannot delete a row. It tells me that the row has a
locked cell and I must remove worksheet protection before I can delete the
row.

Is there a way to lock column values but still be able to delete the row?
 
L

Liliana

You need VBA


Add a button with the code:

Sub DeleteRowAtSelectedCell()
'warning. will delete
'every row in the range selected
'without undo
PW = "thepassword"

ActiveSheet.Unprotect Password:=PW

Selection.EntireRow.Delete

ActiveSheet.Protect Password:=PW
End Sub


You could add a message box to tell the user what s/he is about to do
and seek confirmation but it remains dangerous.

--
Lil


I have a need to create an Excel file that has one column that
contains values that I don't want the user to be able to change,
however, they can add new rows (the locked column would be blank) and
delete rows. I unlocked the columns that they can manipulate, locked
the one column and then when I protected the worksheet I said to allow
users to insert and delete rows. I can add a row but I cannot delete
a row. It tells me that the row has a locked cell and I must remove
worksheet protection before I can delete the row.

Is there a way to lock column values but still be able to delete the
row?



--
 
M

MT

Liliana said:
You need VBA


Add a button with the code:

Sub DeleteRowAtSelectedCell()
'warning. will delete
'every row in the range selected
'without undo
PW = "thepassword"

ActiveSheet.Unprotect Password:=PW

Selection.EntireRow.Delete

ActiveSheet.Protect Password:=PW
End Sub


You could add a message box to tell the user what s/he is about to do
and seek confirmation but it remains dangerous.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top