Resetting Dropdown list in all sheets (Excel Workbook)

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)

I cam across a piece of code on a different thread http://www.excelforum.com/showthread.php?t=847615 which works perfectly only for one sheet i.e the name of the sheet given in the code ("Entry Sheet"), Can someone please guide/help me to enhance the below code to work on the workbook (multiple sheets).

Sub ResetDropDowns()
    
    Dim rngLists As Range
    Dim ListCell As Range
    
    On Error Resume Next
    Set rngLists = Sheets("Entry Sheet").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
    
End Sub

Thanks,
Hawkz

Hi Yasser,

Thanks for the reply. I tried the above code, but it gives me the following error. (Attached image) Run-time error '1004': Method 'Range' of object '_Worksheet' failed. Thanks.

Capture.PNG

Yeah,sure.Drop.xlsm

Hi Yaseer,

I really appreciate your help! This seems to work, however the only issue now is after clicking the button, my focused page/sheet shifts. For example: The button is on "Test" sheet, and when i click that the drop-down do resets to default, however I am taken to "Data" sheet, i.e. I am no longer on "Test" sheet where I originally was.

Regards
Hawkz.

Hi Yasser,

It worked on my sample, however if i integrate with my complete sheet I start getting 1004 Error again. Attached is the workbook.
New Drop.xlsm. Kindly bail me out through this condition.

Regards
Hawkz

I have removed that validation and External link, but still it shows me the same error.

Nopes, all of the Validation List has been referred from "Constant" Sheet i.e. (Constant!$F$9:$F$11). The problem is not only in the "Cover" Sheet but also in the "Drops" sheet.
I tried, using individual sheet specific drop down reset button, but the error persists. Thanks.

Hi All,

Can anyone please bail me out from this issue. Thanks.

I have used the VBA code:

Sub clearcheck()
Dim sh As Worksheet
For Each sh In Sheets
On Error Resume Next
sh.CheckBoxes.Value = False
On Error GoTo 0
Next sh
End Sub

But now need to have this only apply to a specific set of worksheets. Can anyone tell me how to make this change?

any help is appreciated.