Delete Row with Protected Cells

[Guest]

I need worksheet code that specifies if a user selects 1 or more rows to
delete, it will unprotected the sheet and allow the deletion, then re-protect
the sheet when the deletion is done. In a post from February 2006, a
gentleman by the name of Turboj indicated he had posted code for this, but I
can't find it anywhere. Does someone know how to do this?

 

[Guest]

Activesheet.unprotect and protect will do the trick. You can also try the
macro recorder, which will show you the exact syntax

 

[Guest]

Thanks; however, I understand that part. I had the code below, but it still
doesn't allow the user to delete a row; it just unprotects/reprotects.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Target.EntireRow.Address Then
ActiveSheet.Unprotect
End If
ActiveSheet.Protect
End Sub

 

[Guest]

If your using xl2002 or later, you should be able to specify as part of the
protection options to allow the user to delete a row even on a protected
sheet.

 

[Guest]

Hi, Tom. Am using 2002; however, when I go to delete rows and select
Edit/Delete, I get a message saying locked cells cannot be deleted when the
sheet is protected...because the row I'm trying to delete has some locked
cells. Even though when you go to protect you select the option to allow the
user to delete rows, they can only delete a row if all the cells in that row
are unlocked. Maybe code that says if the user selects 1 or more rows, then
unlock all the cells in that row (to allow them to delete the row)....not
exactly sure how to do this though. Am trying to play around with it now.

 

[Guest]

There's a couple of things that concern me here.

First thing: If you put this code in the Worksheet_Change event, it will
delete entire rows every time an entire row is selected. Is this what you
want?

Second thing: Multiple selections will create ".Areas" on the sheet if the
selected rows are not contiguous. This necessitates deleting rows in reverse
order (last to first) so as they're deleted an error is avoided, and the
desired rows are the ones actually deleted. For example, if your user selects
rows 3, 5, and 7 and row3 is deleted first then rows 5 and 7 now become rows
4 and 6 respectively. This causes what used to be row 6 to be deleted next as
it's now row5 & next in line, ..causing the cycle to repeat, making your
original row7 eventually in the row5 position. As you may guess, row7 (now
row5) doesn't get deleted because row9, which does get deleted, is now in
row7 position.

Going in reverse order deletes row7 first, leaving rows 5 and 3 correctly
positioned, and next in line respectively. This requires more code, and the
use of a For..Next loop.

If, as you say, some of the cells in the rows are locked then don't allow
them to be selected. This makes things easier to code. You could use a
control (button) for users to fire the procedure with after they select cells
in the rows to delete. You wouldn't need an If..Then construct either. The
code would simply be something like:

With ActiveSheet
.Unprotect
.Range(Selection.Address).EntireRow.Delete
.Protect
End With

This lets Excel handle the issue for getting the right rows, and it's way
lots faster than using a For..Next loop or If..Then construct.

HTH
Regards,
Garry

 

[Tom Ogilvy]

You can't react to the users attempt to delete rows with an event (it would
be after the fact - after the failure). So I don't see code as an option
unless you will add a custom menu item that. the user selects the rows,
then hits the menu item and you code kicks in to unprotect the sheet, delete
the selection, reprotect the sheet.

 

[Guest]

Thanks, Garry; works like a charm! Appreciate the feedback and expertise.

 

[Guest]

You're very welcome! Glad to be of help..

Garry