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 : )
--
FirstVette52

I suppose the list is named MyList:

http://cjoint.com/?bjuMxnT58R

Sub raz()
For Each c In Cells.SpecialCells(xlCellTypeAllValidation)
c.Value = Range("MyList")(1)
Next c
End Sub

JB
http://boisgontierjacques.free.fr

On 9 jan, 19:07, FirstVette52 <(My User name is Firstvette 52, too)
firstvet52@(my ISP E-mail provider is) netzero.com wrote:
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 : )
--
FirstVette52

Select all cells.

F5SpecialData ValidationOK

EditReplace

What: *

With: first list option

Replace all.


Gord Dibben MS Excel MVP

On Fri, 9 Jan 2009 10:07:00 -0800, FirstVette52 <(My User name is Firstvette
52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote:

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 : )

If you have many Lists:

http://cjoint.com/?bjw7iyh5Sz

Sub raz()
For Each c In Cells.SpecialCells(xlCellTypeAllValidation)
nameList = Mid(c.Validation.Formula1, 2)
c.Value = Range(nameList)(1)
Next c
End Sub

JB



On 9 jan, 19:51, JB wrote:
I suppose the list is named MyList:

http://cjoint.com/?bjuMxnT58R

Sub raz()
* For Each c In Cells.SpecialCells(xlCellTypeAllValidation)
* *c.Value = Range("MyList")(1)
* Next c
End Sub

JBhttp://boisgontierjacques.free.fr

On 9 jan, 19:07, FirstVette52 <(My User name is Firstvette 52, too)
firstvet52@(my ISP E-mail provider is) netzero.com wrote:





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 : )
--
FirstVette52- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

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!
--
FirstVette52


"JB" wrote:

I suppose the list is named MyList:

http://cjoint.com/?bjuMxnT58R

Sub raz()
For Each c In Cells.SpecialCells(xlCellTypeAllValidation)
c.Value = Range("MyList")(1)
Next c
End Sub

JB
http://boisgontierjacques.free.fr

On 9 jan, 19:07, FirstVette52 <(My User name is Firstvette 52, too)
firstvet52@(my ISP E-mail provider is) netzero.com wrote:
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 : )
--
FirstVette52

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


On 9 jan, 22:15, FirstVette52 <(My User name is Firstvette 52, too)
firstvet52@(my ISP E-mail provider is) netzero.com wrote:
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!
--
FirstVette52



"JB" wrote:
I suppose the list is named MyList:

http://cjoint.com/?bjuMxnT58R

Sub raz()
* For Each c In Cells.SpecialCells(xlCellTypeAllValidation)
* *c.Value = Range("MyList")(1)
* Next c
End Sub

JB
http://boisgontierjacques.free.fr

On 9 jan, 19:07, FirstVette52 <(My User name is Firstvette 52, too)
firstvet52@(my ISP E-mail provider is) netzero.com wrote:
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 : )
--
FirstVette52- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

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)'
--
FirstVette52


"JB" wrote:



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


On 9 jan, 22:15, FirstVette52 <(My User name is Firstvette 52, too)
firstvet52@(my ISP E-mail provider is) netzero.com wrote:
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!
--
FirstVette52



"JB" wrote:
I suppose the list is named MyList:

http://cjoint.com/?bjuMxnT58R

Sub raz()
For Each c In Cells.SpecialCells(xlCellTypeAllValidation)
c.Value = Range("MyList")(1)
Next c
End Sub

JB
http://boisgontierjacques.free.fr

On 9 jan, 19:07, FirstVette52 <(My User name is Firstvette 52, too)
firstvet52@(my ISP E-mail provider is) netzero.com wrote:
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 : )
--
FirstVette52- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -