PDA

View Full Version : Resetting Dropdown list in all sheets (Excel Workbook)



Hawkz
12-27-2016, 09:24 AM
Dear All,


I am a newbie to Excel Macros, and i have been trying to work out on resetting the value of all drop-down list available in Excel Workbook (created via Data Validation) to Default value (i.e. First Value of the list), but I am stuck with Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

Attached is my workbook, can someone please guide.

Clear button on "Button" sheet.
Drop down present on "Drops" sheet and Multiple Test sheet.

Below is the code that I am trying currently.


Sub ResetDropDowns() Dim rngLists As Range
Dim ListCell As Range
Dim ws As Worksheet


Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Activate
On Error Resume Next
Set rngLists = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not rngLists Is Nothing Then
For Each ListCell In rngLists.Cells
If InStr(ListCell.Validation.Formula1, "!") > 0 Then
ListCell.Value = Sheets(Mid(Split(ListCell.Validation.Formula1, "!")(0), 2, Len(ListCell.Validation.Formula1))).Range(Split(Split(ListCell.Validation.F ormula1, "!")(1), ":")(0)).Value
Else
ListCell.Value = Range(Trim(Mid(Replace(ListCell.Validation.Formula1, ":", String(99, " ")), 2, 99))).Value
End If
Next ListCell
End If

Set rngLists = Nothing
Next ws
Application.ScreenUpdating = True
End Sub


Regards
Hawkz
17907