Link to home
Start Free TrialLog in
Avatar of scsnow2310
scsnow2310

asked on

Dropdown List Reset macro, Protect Sheet bug

Hi, the macro below has been working fine to reset a number or dropdowns I have on a spreadsheet, but if I 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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of scsnow2310
scsnow2310

ASKER

Perfect!

Many thanks!