Dropdown List Reset macro, Protect Sheet bug

[scsnow]

Hi, the macro below has been working fine to reset a number or dropdowns I have on a spreadsheet, but if Protect the worksheet I get the following error: Runtime error '424' - Object Required

How can I modify this macro to run when I Protect the worksheet?

Many thanks in advance!


Sub ResetList()
'
' ResetList Macro
'
Dim rngList As Range
Dim ListCell As Range

On Error Resume Next
Set rngLists = Sheets("AP Risk Scorecard").UsedRange.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not rngLists Is Nothing Then
For Each ListCell In rngLists.Cells
ListCell.Value = Range(Trim(Mid(Replace(ListCell.Validation.Formula1, ":", String(99, " ")), 2, 99))).Value
Next ListCell
End If
Range("E15").Select

End Sub

 

[AutoFilterQueen]

Add something like the following line of code to the beginning of your subroutine:

Sheets("AP Risk Scorecard").Unprotect Password:="abcd"

and add something like this line of code to the end of your subroutine:

Sheets("AP Risk Scorecard").Protect Password:="abcd", DrawingObjects:=True, Contents:=True, Scenarios:=True