My formula is ... =OFFSET(INDIRECT(SUBSTITUTE($L6,"
","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($L6," ","")&"Col")),1)

If I write ...

With Worksheets(1).Range("A1").Validation
strFormula1 = .Formula1 & =OFFSET(INDIRECT(SUBSTITUTE($L6,"
","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($L6," ","")&"Col")),1)

.Delete
.Add Type:=xlValidateList, Formula1:=strFormula1
End With

I am getting an error message , which is the correct way for writing the
formmula ?

Thank you
Angeles



"Doug Glancy" wrote:

Angeles,

It is possible. You have to capture the old list, delete the validation and
then create new validation it with the new list. Here's an example to get
you started. Obviously, it doesn't address where the new user validation
item comes from:

Sub test()
Dim strFormula1 As String

With Worksheets(1).Range("A1").Validation
strFormula1 = .Formula1 & ",Class 224"
.Delete
.Add Type:=xlValidateList, Formula1:=strFormula1
End With
End Sub

hth,

Doug

"Angeles" wrote in message
...
I got it , thank you.

One more question , is it posible to let the user adds more values to this
list , for example , if the list values are : Class 1, Class 2, Class 3.
Is
it posible that the user writes one new value and add that value to my
Validation List (if the value is new)?

"Angeles" wrote:

Sorry , but how do I activate the Macro Recorder ?

"Doug Glancy" wrote:

Angeles,

Try turning on the Macro Recorder and then adding some validation to
some
cells. The recorded code will give you a very good start.

hth,

Doug

"Angeles" wrote in message
...
Hi everybody , I need to define an validation list to somes cells in
my
worksheet using source code, is it possible ?