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?

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

"Paige" wrote:

> 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?

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

"Brotha Lee" wrote:

>
> Activesheet.unprotect and protect will do the trick. You can also try the
> macro recorder, which will show you the exact syntax
>
> "Paige" wrote:
>
> > 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?

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.

--
Regards,
Tom Ogilvy


"Paige" wrote:

> 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
>
> "Brotha Lee" wrote:
>
> >
> > Activesheet.unprotect and protect will do the trick. You can also try the
> > macro recorder, which will show you the exact syntax
> >
> > "Paige" wrote:
> >
> > > 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?

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.


"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Paige" wrote:
>
> > 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
> >
> > "Brotha Lee" wrote:
> >
> > >
> > > Activesheet.unprotect and protect will do the trick. You can also try the
> > > macro recorder, which will show you the exact syntax
> > >
> > > "Paige" wrote:
> > >
> > > > 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?

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

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.

--
Regards,
Tom Ogilvy


"Paige" <(E-Mail Removed)> wrote in message
news6F638AB-3A0A-4A78-AA8E-(E-Mail Removed)...
> 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.
>
>
> "Tom Ogilvy" wrote:
>
>> 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.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Paige" wrote:
>>
>> > 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
>> >
>> > "Brotha Lee" wrote:
>> >
>> > >
>> > > Activesheet.unprotect and protect will do the trick. You can also try
>> > > the
>> > > macro recorder, which will show you the exact syntax
>> > >
>> > > "Paige" wrote:
>> > >
>> > > > 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?

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

"GS" wrote:

> 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

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

Garry