Macro doesn't work when Sheets are protected UserInterfaceOnly
 

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

Macro doesn't work when Sheets are protected UserInterfaceOnly
 

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...
--
HTH...

Jim Thomlinson


"RyanH" wrote:

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