Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Bug?
Excel 2003: I have a column where cells are locked on a protected sheet and
protection is turned on. In turning on protection, Excel offers checkbox options, such as "Allow users to detlete rows." The problem is When the user tries to delete a row they get a message that "cannot delete cells that are locked." This is a catch 22, because to get the benefits of sheet protection, you lock cells. And if you check "Allow users to detlete rows." in creating the password, but you can't delete a row containing protected cells, what good is that checkbox? Is this a bug? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Bug?
You can delete rows where all the cells in that row are unlocked.
Perico wrote: Excel 2003: I have a column where cells are locked on a protected sheet and protection is turned on. In turning on protection, Excel offers checkbox options, such as "Allow users to detlete rows." The problem is When the user tries to delete a row they get a message that "cannot delete cells that are locked." This is a catch 22, because to get the benefits of sheet protection, you lock cells. And if you check "Allow users to detlete rows." in creating the password, but you can't delete a row containing protected cells, what good is that checkbox? Is this a bug? Thank you. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Bug?
While it seems odd I would say it is not a bug. Would it be any more correct
to allow you to delete locked and protected cells? How about deleting a header row or a row with grand totals in it? It seems that there is no right answer to that question. To me the key is understanding the limitations and working around them. -- HTH... Jim Thomlinson "Perico" wrote: Excel 2003: I have a column where cells are locked on a protected sheet and protection is turned on. In turning on protection, Excel offers checkbox options, such as "Allow users to detlete rows." The problem is When the user tries to delete a row they get a message that "cannot delete cells that are locked." This is a catch 22, because to get the benefits of sheet protection, you lock cells. And if you check "Allow users to detlete rows." in creating the password, but you can't delete a row containing protected cells, what good is that checkbox? Is this a bug? Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Bug?
Dave, where am I going wrong in this code snippet? After it runs I still am
unable to delete the row (rw): If whichsheet = "ResData" Then ThisWorkbook.Sheets("ResData").Unprotect ("pduct") Intersect(Range("resRecNum"), rw).Interior.ColorIndex = 7 'pink rw.Locked = False Intersect(Range("resRecNum"), rw).Offset(-1, 0).Interior.ColorIndex = 50 'green Intersect(Range("resMeasure"), rw) = "DUP" 'rw.Offset(-1).Locked = False ThisWorkbook.Sheets("ResData").Protect ("pduct"), AllowDeletingRows:=True "Dave Peterson" wrote: You can delete rows where all the cells in that row are unlocked. Perico wrote: Excel 2003: I have a column where cells are locked on a protected sheet and protection is turned on. In turning on protection, Excel offers checkbox options, such as "Allow users to detlete rows." The problem is When the user tries to delete a row they get a message that "cannot delete cells that are locked." This is a catch 22, because to get the benefits of sheet protection, you lock cells. And if you check "Allow users to detlete rows." in creating the password, but you can't delete a row containing protected cells, what good is that checkbox? Is this a bug? Thank you. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Bug?
Is rw a row in a range--less than all the columns?
maybe rw.entirerow.locked = false would help Perico wrote: Dave, where am I going wrong in this code snippet? After it runs I still am unable to delete the row (rw): If whichsheet = "ResData" Then ThisWorkbook.Sheets("ResData").Unprotect ("pduct") Intersect(Range("resRecNum"), rw).Interior.ColorIndex = 7 'pink rw.Locked = False Intersect(Range("resRecNum"), rw).Offset(-1, 0).Interior.ColorIndex = 50 'green Intersect(Range("resMeasure"), rw) = "DUP" 'rw.Offset(-1).Locked = False ThisWorkbook.Sheets("ResData").Protect ("pduct"), AllowDeletingRows:=True "Dave Peterson" wrote: You can delete rows where all the cells in that row are unlocked. Perico wrote: Excel 2003: I have a column where cells are locked on a protected sheet and protection is turned on. In turning on protection, Excel offers checkbox options, such as "Allow users to detlete rows." The problem is When the user tries to delete a row they get a message that "cannot delete cells that are locked." This is a catch 22, because to get the benefits of sheet protection, you lock cells. And if you check "Allow users to detlete rows." in creating the password, but you can't delete a row containing protected cells, what good is that checkbox? Is this a bug? Thank you. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection Bug?
I agree. It was simply a matter of writing some code to unlock the cells in
the target row. Thanks. "Jim Thomlinson" wrote: While it seems odd I would say it is not a bug. Would it be any more correct to allow you to delete locked and protected cells? How about deleting a header row or a row with grand totals in it? It seems that there is no right answer to that question. To me the key is understanding the limitations and working around them. -- HTH... Jim Thomlinson "Perico" wrote: Excel 2003: I have a column where cells are locked on a protected sheet and protection is turned on. In turning on protection, Excel offers checkbox options, such as "Allow users to detlete rows." The problem is When the user tries to delete a row they get a message that "cannot delete cells that are locked." This is a catch 22, because to get the benefits of sheet protection, you lock cells. And if you check "Allow users to detlete rows." in creating the password, but you can't delete a row containing protected cells, what good is that checkbox? Is this a bug? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
sheet protection | Excel Discussion (Misc queries) | |||
Sheet protection error msg - Unrequested sheet activation | Excel Programming | |||
Help with sheet protection | Excel Programming |