Delete rows from a protected sheet (write macro)

[Matt]

I have a spreadsheet that I want to keep protected. Users are allowed to
change values in certain cells. If need be, they can add a row or delete a
row. I selected to allow this feature before I protected the sheet. The add
row works fine. When a row or rows is/are selected and deleted, a message
pops up indicating there are cells protected. I've written a macro that will
disable the security, delete the rows, and enable the security again. Here
is what I need help with: If the user has not selected at least one row, an
error message will pop up directing the user to do so. Second (if possible)
to make sure the is within a named range: I have named the range A3:A20
"Name". If the row(s) selected does not fall in this range, I want the
delete macro to do nothing or return a message that indicates "The row(s) you
have selected is/are not available to be deleted".

[Jim Thomlinson]

Give this a look

dim rngIntersect as range
dim rngToDelete as range

set rngtodelete = activecell.entirerow
on error resume next
set rngIntersect = intersect(rngtodelete,
sheets("Sheet1").range("MyRangeName"))
on error goto 0

if rngintersect is nothing then
msgbox "Sorry. Can't delete those rows."
else
rngtoDelete.delete
endif

This will work so long as the user has selected only one row or cell. This
code only deletes the row of the active cell, not the entire selection.
--
HTH...

Jim Thomlinson


"Matt" wrote:

> I have a spreadsheet that I want to keep protected. Users are allowed to
> change values in certain cells. If need be, they can add a row or delete a
> row. I selected to allow this feature before I protected the sheet. The add
> row works fine. When a row or rows is/are selected and deleted, a message
> pops up indicating there are cells protected. I've written a macro that will
> disable the security, delete the rows, and enable the security again. Here
> is what I need help with: If the user has not selected at least one row, an
> error message will pop up directing the user to do so. Second (if possible)
> to make sure the is within a named range: I have named the range A3:A20
> "Name". If the row(s) selected does not fall in this range, I want the
> delete macro to do nothing or return a message that indicates "The row(s) you
> have selected is/are not available to be deleted".

[Matt]

Jim-
Thanks.

"Jim Thomlinson" wrote:

> Give this a look
>
> dim rngIntersect as range
> dim rngToDelete as range
>
> set rngtodelete = activecell.entirerow
> on error resume next
> set rngIntersect = intersect(rngtodelete,
> sheets("Sheet1").range("MyRangeName"))
> on error goto 0
>
> if rngintersect is nothing then
> msgbox "Sorry. Can't delete those rows."
> else
> rngtoDelete.delete
> endif
>
> This will work so long as the user has selected only one row or cell. This
> code only deletes the row of the active cell, not the entire selection.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Matt" wrote:
>
> > I have a spreadsheet that I want to keep protected. Users are allowed to
> > change values in certain cells. If need be, they can add a row or delete a
> > row. I selected to allow this feature before I protected the sheet. The add
> > row works fine. When a row or rows is/are selected and deleted, a message
> > pops up indicating there are cells protected. I've written a macro that will
> > disable the security, delete the rows, and enable the security again. Here
> > is what I need help with: If the user has not selected at least one row, an
> > error message will pop up directing the user to do so. Second (if possible)
> > to make sure the is within a named range: I have named the range A3:A20
> > "Name". If the row(s) selected does not fall in this range, I want the
> > delete macro to do nothing or return a message that indicates "The row(s) you
> > have selected is/are not available to be deleted".