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

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
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>