VBA to Reset multiple Data Validation lists to first entry in list

[Sheripres]

Hello:
I found a VBA code that was strictly for lists and would not work for me. My data validation lists/drop downs are created with many formulas and some that are just lists.
After the end user creates the part number through the various drop downs, they push a button and it pastes on another sheet tab.
Now, I want to reset what they put on the Form sheet tab so they can start over. I will just create another form button.

Thank you for any help.

Sheri

 

[Akuini]

Maybe this:
You need to adjust the sheet reference, if it's the active sheet then:

Sub ResetDropDowns()

Dim rngLists As Range
Dim ListCell As Range
Dim c As Range

On Error Resume Next
Set rngLists = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not rngLists Is Nothing Then
    For Each ListCell In rngLists.Cells
            Set c = Evaluate(ListCell.Validation.Formula1)
            If Not c Is Nothing Then ListCell.Value = c.Cells(1)
    Next ListCell
End If

End Sub

Sorry... I copied it from another forum and pasted it in Excel so I can easily change the sheet names and I always keep a copy of the macro within the workbook as notes for future references.

Somehow you ended up with html tags - possibly Excel retained them from a website. I dumped your code into Notepad++ (nice, free code editing program) and used find/replace (ctrl+H) to remove sets of tags one by one.

They are BBcode tags, you can remove them via this website:
bbcode-to-text

 

[Fluff]

I always keep a copy of the macro within the workbook as notes for future references.
When uploading here, I clicked on the VBA button and pasted. Did I do that wrong?

Yes, you need to copy the code from the VB Editor not from a sheet, otherwise you are posting a table.

 

[Micron]

That code does the same thing as mine, which I haven't had the time to figure out yet. It picks a value from 2 columns over to the right (3.6r). That column is clearly not in the validation range.

I've also just figured out that I was making a blunder that's too embarrassing to share except to say it had something to do with sheets. One might want to add turning off events if it will stop other code from running, such as change event?

 

[Micron]

Ignore my last post until I look some more. I didn't realize that column Q was also a validation list using the same range. However, the list range doesn't contain the value of 3.6r

 

[Micron]

OK, no issues. Sorry for the false alarm.
@Akuini - nice and concise code!

 

[Sheripres]

Maybe this:
You need to adjust the sheet reference, if it's the active sheet then:

Sub ResetDropDowns()

Dim rngLists As Range
Dim ListCell As Range
Dim c As Range

On Error Resume Next
Set rngLists = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not rngLists Is Nothing Then
    For Each ListCell In rngLists.Cells
            Set c = Evaluate(ListCell.Validation.Formula1)
            If Not c Is Nothing Then ListCell.Value = c.Cells(1)
    Next ListCell
End If

End Sub

They are BBcode tags, you can remove them via this website:
bbcode-to-text

I tried running the code and it is getting Error Code 424. Activesheet is fine for me to use, so I just copied this exactly.

Am I doing something wrong?

Thank you,
Sheri

 

[Akuini]

OK, no issues. Sorry for the false alarm.
@Akuini - nice and concise code!

Thanks for reviewing my code.

I tried running the code and it is getting Error Code 424. Activesheet is fine for me to use, so I just copied this exactly.

Am I doing something wrong?

1. In which line the code stopped?
2. What is the data validation formula?

 

[Sheripres]

Thanks for reviewing my code.

1. In which line the code stopped?
2. What is the data validation formula?

It stopped at Set c = Evaluate (listCell.Validation.Formula1)

My first Data Validation list is truly a list of three items. But, I have an event code that will open up four more rows that have IF statements attached to what you choose in the first list.

Example: If you choose FRUIT, then the next Data Validation will provide a list of only fruit. If you had chosen VEGETABLES, that IF statement would provide only vegetables.

 

[Akuini]

My first Data Validation list is truly a list of three items.

Can you show us the formula? is it use only string or it refers to a range?

 

[Akuini]

I think you're using string as the list., something like: apel;lemon;banana
Try this one:

Sub ResetDropDowns_1()

Dim rngLists As Range
Dim ListCell As Range
Dim c As Range

On Error Resume Next
Set rngLists = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not rngLists Is Nothing Then
    For Each ListCell In rngLists.Cells
            If IsError(Evaluate(ListCell.Validation.Formula1)) Then
                ListCell.Value = Split(ListCell.Validation.Formula1, ";")(0)
            Else
                Set c = Evaluate(ListCell.Validation.Formula1)
                If Not c Is Nothing Then ListCell.Value = c.Cells(1)
            End If
    Next ListCell
End If

End Sub