Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a protected row
I have an excel sheet which is protected. In this sheet there are some cells which are locked and some cells which are not. While protecting the excel sheet I have checked the option for allowing the users to delete rows. But still its not allowing me to delete the rows saying that you are trying to delet the cells which are locked. How do I do this? -- reena ------------------------------------------------------------------------ reena's Profile: http://www.excelforum.com/member.php...o&userid=30440 View this thread: http://www.excelforum.com/showthread...hreadid=509805 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a protected row
Hi reena,
You can't delete any range that contains a locked cell. You must remove the sheet protection, delete the row, then restore protection. You can use a macro to do the job, with very simple coding. Try this: Activesheet.Unprotect Password:="myPassword" Selection.EntireRow.Delete ActiveSheet.Protect Password:="myPassword", True, True, True, _ OtherOption1:=Option1Value, _ OtherOption2:=Option2Value, _ ..and so on Note that the options available for Excel2002 and later won't work in earlier versions, so you'll have to modify the "ActiveSheet.Protect..." line of code something like this if you want those features for later versions: ActiveSheet.Protect Password:="myPassword", True, True, True If Val(Application.Version) 9 Then OtherOption1:=Option1Value _ OtherOption2:=Option2Value _ 'and so on End If Regards, GS "reena" wrote: I have an excel sheet which is protected. In this sheet there are some cells which are locked and some cells which are not. While protecting the excel sheet I have checked the option for allowing the users to delete rows. But still its not allowing me to delete the rows saying that you are trying to delet the cells which are locked. How do I do this? -- reena ------------------------------------------------------------------------ reena's Profile: http://www.excelforum.com/member.php...o&userid=30440 View this thread: http://www.excelforum.com/showthread...hreadid=509805 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a protected row
Better:
Activesheet.Unprotect Password:="myPassword" Selection.EntireRow.Delete WksProtect() Sub WksProtect() With ActiveSheet If val(Application.Version) = 10 Then .Protect " ", _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ Userinterfaceonly:=True, _ AllowFiltering:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True Else .Protect " ", DrawingObjects:=True, Contents:=True, Scenarios:=True, Userinterfaceonly:=True End If ' .EnableSelection = 'optional parameters (xlNoRestrictions, OR xlNoSelection, OR xlUnlockedCells) -choose only 1 End With End Sub Regards, GS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a protected row
Substitute .Protect " ", in the WksProtect() procedure example with
..Protect "myPassword", OR .Protect Password:="myPassword", Regards, GS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows form Protected Sheet | Excel Discussion (Misc queries) | |||
Deleting Rows Containing Protected Cells | Excel Worksheet Functions | |||
Help !!! Deleting rows in protected document ?!? | Excel Discussion (Misc queries) | |||
Excel 97 - Prevent Renaming/Deleting Protected Worksheet | Excel Programming | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |