data_validation_list reset on excel start up

[VonFeed]

Hello, I am looking for a way to reset all my dropdown lists in a workbook to their top choice. Right now I found the following code, but it is for a certain sheet only, whereas I want all sheets with a dropdown to do this. plus I want this to only work when the sheet is opened automatically so people do not have to think. Thank you for any help.

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

 

[Fluff]

Untested, but try

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim rngLists As Range
Dim ListCell As Range

On Error Resume Next
Set rngLists = Sh.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

This needs to go in the ThisWorkbook module