Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows while sheet protected | Excel Discussion (Misc queries) | |||
How do I undo write protected to excel sheet send to floppy disk | Excel Discussion (Misc queries) | |||
How do I write a macro to delete all rows from the first empty ro. | Excel Programming | |||
how to allow macro to write on a protected sheet | Excel Programming | |||
Macro to look for blank rows in sheet, delete the row and autofit | Excel Programming |