Insert Command Button to reset multiple Data Validation Dropdowns using VBA

[Dellpanther]

Hi all,

I have a single worksheet with 20+ data validation dropdowns. I want to insert a Command Button that will reset all the dropdown cells to the first option on the dropdown list when I click it. Having never used Macro/VBA before, I’ve tried researching the appropriate code to make this happen with zero success. Can someone please explain how to do this and include the appropriate code?

For the sake of simplicity, assume my worksheet is titled “SheetA” and data validations are in cells D19, D21... D31. I will update as necessary.

Thank you in advance!

 

[Norie]

How have you set up the lists in the data validation?

If it's simply by using something like 1,2,3,4,5 you could try this.

Sub ResetDV()
Dim rngDV As Range
Dim lst As Variant

    
    For Each rngDV In Sheets("SheetA").Cells.SpecialCells(XlCellType.xlCellTypeAllValidation)
        
        If rngDV.Validation.Type = xlValidateList Then
        
            lst = rngDV.Validation.Formula1
            
            rngDV.Value = Split(lst, ",")(0)
        End If
        
    Next rngDV
    
End Sub

If you've used a range for the list things will be a bit more complicated.

 

[Dellpanther]

Hi Norie, thanks for your reply! As I mentioned I am a complete novice when it comes to Macro/VBA...

When I am entering the code for my Command Button, the initial code that appears is:

—

Private Sub CommandButton1_Click()


End Sub

—

Where do I enter your code in relation to the above? Do I start a new code thread? Does it go in between Private Sub/End Sub?

 

[Norie]

Try this.

Private Sub CommandButton1_Click()
Dim rngDV As Range
Dim lst As Variant

    
    For Each rngDV In Sheets("SheetA").Cells.SpecialCells(XlCellType.xlCellTypeAllValidation)
        
        If rngDV.Validation.Type = xlValidateList Then
        
            lst = rngDV.Validation.Formula1
            
            rngDV.Value = Split(lst, ",")(0)
        End If
        
    Next rngDV

End Sub

 

[Dellpanther]

Thank you!