Reset Validation cells to first list value

  • Thread starter Thread starter FirstVette52
  • Start date Start date
F

FirstVette52

I have several cells that use named lists with the Validation feature on a
form. How do I reset those cells to their first list option?

Is there a way to search the form range for validated cells and then reset
them?

Thank You for any help you may be able to give : )
 
Select all cells.

F5>Special>Data Validation>OK

Edit>Replace

What: *

With: first list option

Replace all.


Gord Dibben MS Excel MVP
 
This looks great, however I use a different list for each validation cell. Is
there a way to refer to the cell and reset it to the first entry of its list
without referencing the list itself?

Thanks for your help!
 
Put value of first element of each list:

Sub raz()
For Each c In Cells.SpecialCells(xlCellTypeAllValidation)
If Left(c.Validation.Formula1, 1) = "=" Then
NomList = Mid(c.Validation.Formula1, 2)
c.Value = Range(NomList)(1)
Else
temp = c.Validation.Formula1
a = Split(temp, ",")
c.Value = a(0)
End If
Next c
End Sub

http://cjoint.com/?bjxJWEFNK7

JB
 
I'm not very good at Visual Basic and I can't get this construct to work
(probably because I need to replace elements of the code with my own
elements).

When the code runs as posted it fails in runtime. Debugging shows c is
empty, 'For Each c...', as well as 'NomList'.

There is also a type mismatch, 'c.Value = a(0)'
 
Back
Top