Method to set all cells with validation enabled in a worksheet to the first value in validation drop-down list

[ptkeat]

Hello,

I have a worksheet with hundreds of cells with list driven data validation applied.

Is there a way to automatically set (i.e. by clicking an object button called "Reset Template") every cell with list based data validation applied to the first value in the data validation list for that cell?

There are several different data validation lists used throughout the worksheet.

Thank you for your help!

 

[AFPathfinder]

Good question! I had to do some playing around with this one, but I think this will do what you want. You just need to make sure the active sheet is the one with all the lists you want to reset to the first option.

Sub SetValidationCells()
    Dim inputRange As Range
    Dim cell As Range
    
    Set inputRange = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
    
    For Each cell In inputRange
        cell.Value = Evaluate(cell.Validation.Formula1).Cells(1, 1).Value
    Next cell
End Sub