Code fails when sheet is protected

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Perfect. Thanks Mike.

Mike H said:
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
Click to expand...
 
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)
 
Back
Top