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

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

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

Deleting a protected row
 

Substitute .Protect " ", in the WksProtect() procedure example with
..Protect "myPassword", OR .Protect Password:="myPassword",

Regards,
GS