Delete rows from a protected sheet (write macro)
 

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".

Delete rows from a protected sheet (write macro)
 

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".

Delete rows from a protected sheet (write macro)
 

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".