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

Do it in Excel with the macro recorder on. That will give you the code.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"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 ?

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 ?

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 ?

Angeles,

In Excel's Tools menu choose "Macros" then choose "Record New Macro".
Choose to save it to ThisWorkbook. The Record Macro toolbar will appear on
the screen. Dthe steps to enter your validation and then press the Stop
button on the Macro Recorder toolbar. Then go into the Visual Basic Editor.
Your recorded macro will be in "Module1" or similar in the workbook you did
the validation in.

hth,

Doug

"Angeles" wrote in message
...
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 ?

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 ?

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 ?

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 ?