Code fails when sheet is protected
 

Hi,

I am very new to writting code. Through this forum I have pieced together a
macro which will allow other users of our small company to delete a row. The
macro errors out on the Else If.Cells line but only when the sheet is in
protected mode (works fine when not protected) and I have absolutely no clue
how to fix it. Thanks in advance. My code follows:

Sub DeleteZeroStatusClient()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 6
EndRow = 999
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "I").Value = "delete" Then .Rows(Lrow).Delete
'This will delete each row with the Value "delete" in Column
I.

End If
Next
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Code fails when sheet is protected
 

You need to unprotect it at runtime

ActiveSheet.Unprotect Password:="mypass"

Your code

ActiveSheet.Protect Password:="mypass"


make the above the first and last lines in your macro

Mike
"Tail Wind" wrote:

Hi,

I am very new to writting code. Through this forum I have pieced together a
macro which will allow other users of our small company to delete a row. The
macro errors out on the Else If.Cells line but only when the sheet is in
protected mode (works fine when not protected) and I have absolutely no clue
how to fix it. Thanks in advance. My code follows:

Sub DeleteZeroStatusClient()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 6
EndRow = 999
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "I").Value = "delete" Then .Rows(Lrow).Delete
'This will delete each row with the Value "delete" in Column
I.

End If
Next
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Code fails when sheet is protected
 

Perfect. Thanks Mike.

"Mike H" wrote:

You need to unprotect it at runtime

ActiveSheet.Unprotect Password:="mypass"

Your code

ActiveSheet.Protect Password:="mypass"


make the above the first and last lines in your macro

Mike
"Tail Wind" wrote:

Hi,

I am very new to writting code. Through this forum I have pieced together a
macro which will allow other users of our small company to delete a row. The
macro errors out on the Else If.Cells line but only when the sheet is in
protected mode (works fine when not protected) and I have absolutely no clue
how to fix it. Thanks in advance. My code follows:

Sub DeleteZeroStatusClient()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 6
EndRow = 999
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "I").Value = "delete" Then .Rows(Lrow).Delete
'This will delete each row with the Value "delete" in Column
I.

End If
Next
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Code fails when sheet is protected
 

You can also protect the sheet with the UserInterfaceOnly flag set to True.
This locks out the user, but allows VBA to do whatever it wants.

ActiveSheet.Protect UserInterfaceOnly:=True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Tail Wind" wrote in message
...

Perfect. Thanks Mike.

"Mike H" wrote:

You need to unprotect it at runtime

ActiveSheet.Unprotect Password:="mypass"

Your code

ActiveSheet.Protect Password:="mypass"


make the above the first and last lines in your macro

Mike
"Tail Wind" wrote:

Hi,

I am very new to writting code. Through this forum I have pieced
together a
macro which will allow other users of our small company to delete a
row. The
macro errors out on the Else If.Cells line but only when the sheet is
in
protected mode (works fine when not protected) and I have absolutely no
clue
how to fix it. Thanks in advance. My code follows:

Sub DeleteZeroStatusClient()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 6
EndRow = 999
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in
the
cell

ElseIf .Cells(Lrow, "I").Value = "delete" Then
.Rows(Lrow).Delete
'This will delete each row with the Value "delete" in
Column
I.

End If
Next
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub