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

 

[Micron]

Now, I want to reset

Yes, but to what? A value? Zero length string?

You could use the same procedure that pastes to reset the cell values?

Sub YourPasteSubNameHere()
'paste stuff code is here, then
With Sheets("yourSheetName")
   .Range("A1") = "dog"
   .Range("B1") = "cat"
   .Range("C1") = "bird"
   etc.
End With

End Sub

 

[Sheripres]

Thank you for responding quickly.

I have "Choose" as a starter for each part of the part number. Example: Choose Power Supply, next drop down: Choose Communication, etc. There are seven options to create the part number, but I have 7 different categories where the Choose xxx could be different.

Is there a way to just recognize "CHOOSE" and it would know to display that line?

The attached pic will show one part of it. I have over 2,000 rows that are affected.

Thank you!

 

[Micron]

My suggestion works on the cell content and does not interact with a validation list. Unfortunately I don't know much about validation lists, especially the vba side of them. I might find time to play round in the evenings out of simple curiosity, but if your first list option was e.g. Choose... and your column header was e.g. Thermostat Model then it ought to be quite simple to change the cell value to the first item in the list as they'd all be the same. If you go that route I think I'd play with a wb copy and not your original file.

 

[Sheripres]

I know I can't put a link to another forum, but here is a pic of the code I took from that site and this is the error it gave me in my VBA.

Yes, I changed the sheet name to mine. I am not a pro at interpreting VBA that is this detailed where it is yellow. Also, what is in the black box is another reply, but I don't understand it at all.

FYI, this is from 2012; not current. I do work on 365 Excel.

Thanks!

 

[Micron]

Please don't post pics of code - copy/paste within vba code tags. I'll see what I can do later. Have been poking around and getting somewhere but not where I need to be.

 

[Sheripres]

Please don't post pics of code - copy/paste within vba code tags. I'll see what I can do later. Have been poking around and getting somewhere but not where I need to be.

Sorry! I hope I did this right.

Thank you for helping; much appreciated!

[TABLE]
[TR]
[TD]Sub ResetDropDowns()[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Dim rngLists As Range[/TD]
[/TR]
[TR]
[TD]Dim ListCell As Range[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]On Error Resume Next[/TD]
[/TR]
[TR]
[TD]Set rngLists = Sheets("VC FORM").UsedRange.SpecialCells(xlCellTypeAllValidation)[/TD]
[/TR]
[TR]
[TD]On Error GoTo 0[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]If Not rngLists Is Nothing Then[/TD]
[/TR]
[TR]
[TD]For Each ListCell In rngLists.Cells[/TD]
[/TR]
[TR]
[TD]ListCell.Value = Range(Trim(Mid(Replace(ListCell.Validation.Formula1, ":", String(99, " ")), 2, 99))).Value[/TD]
[/TR]
[TR]
[TD]Next ListCell[/TD]
[/TR]
[TR]
[TD]End If[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
[/TABLE]

[/CODE]

 

[Micron]

Not right. Where did you copy the code from that caused you to post html tags within it? Should look more like this

Sub ResetDropDowns()
Dim rngLists As Range
Dim ListCell As Range

On Error Resume Next
Set rngLists = Sheets("VC FORM").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

I will continue to explore until/unless someone else fixes it. Been and am somewhat busy but I want to figure this out if no one else chimes in first.

 

[Sheripres]

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.
When uploading here, I clicked on the VBA button and pasted. Did I do that wrong?
Thank you!

 

[Micron]

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. Busy today (yet not much accomplished - air compressor acting up) so couldn't devote any time to this.