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
Bookmarks