Macro doesn't work when Sheets are protected UserInterfaceOnly

  • Thread starter Thread starter RyanH
  • Start date Start date
R

RyanH

I have a macro that deletes all conditonal formatting from several
worksheets. I set all worksheets protection UserInterfaceOnly:= True when
the workbook is opened. But when I run the macro below I get an Error:
Application-defined or object-defined error. If I unprotect the sheets it
works fine. I thought UserInterfaceOnly = True would allow all macros to
work. Anyone have any ideas?

Private Sub ClearDeptSchedules()

Dim wks As Worksheet
Dim lngLastRow As Long

For Each wks In colDepts
With wks
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If lngLastRow >= 5 Then
.Rows("5:" & lngLastRow).Delete Shift:=xlUp
End If
ERROR>> .Cells.FormatConditions.Delete
.Range("H1") = Now
End With
Next wks

End Sub


Cheers,
Ryan
 
UserInterface only is very hit and miss as to what it allows a macro to do. I
personally don't use it. I protect and unprotect as required...
 
Back
Top