How to create a long validation list?
 

Hi

I'm trying to create validation for a cell with a long list of names.
I started it with a naive attitude of:

mylist = ""
For I = 1 To 300
person = next person
If person should be added Then
mylist = mylist & IIf(mylist = "", "", ",") & person
End If
Next
With Target.Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween, mylist, ""
...
End With

The problem with this issue is that the name list exceed 255 characters
so it is being cut.

I wanted to try to use it as an array or to define it as name but I
failed to define it correctly (I added my not working code at the end,
this is one solution I tried but maybe there is simpler)

I must do it pragmatically since I have several different conditions to
decide what value will be in the list and it changes for every cell.

Can any one supply me with the direction on how to do it?

Thanks
Haim

This code didn't work (the name creation is wrong, I don't know how to
do it):

Dim mylist As Variant
mylist = Array()

For I = 1 To 300
person = next person
If person should be added Then
mylist = Array(mylist, person)
End If
Next

Application.ThisWorkbook.Names.Add name:="workers", RefersTo:=mylist

With Target.Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween, _
Formula1:="=workers"
...
End With

How to create a long validation list?
 

I'm not sure I understand what you want to do. If you want to setup a Data
Validation/List in some cell and you want to create the list by VBA, simply
code your macro to place the names, one at a time, in some column somewhere
in your file, and name the list "mylist" when all the names have been
placed. Then setup the Data Validation. HTH Otto
wrote in message
oups.com...
Hi

I'm trying to create validation for a cell with a long list of names.
I started it with a naive attitude of:

mylist = ""
For I = 1 To 300
person = next person
If person should be added Then
mylist = mylist & IIf(mylist = "", "", ",") & person
End If
Next
With Target.Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween, mylist, ""
...
End With

The problem with this issue is that the name list exceed 255 characters
so it is being cut.

I wanted to try to use it as an array or to define it as name but I
failed to define it correctly (I added my not working code at the end,
this is one solution I tried but maybe there is simpler)

I must do it pragmatically since I have several different conditions to
decide what value will be in the list and it changes for every cell.

Can any one supply me with the direction on how to do it?

Thanks
Haim

This code didn't work (the name creation is wrong, I don't know how to
do it):

Dim mylist As Variant
mylist = Array()

For I = 1 To 300
person = next person
If person should be added Then
mylist = Array(mylist, person)
End If
Next

Application.ThisWorkbook.Names.Add name:="workers", RefersTo:=mylist

With Target.Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween, _
Formula1:="=workers"
...
End With