Excel Validation lista larger than 255 char error

[lovalles]

i have this procedure and works fine, what i do is make a validation list from a range of anothe sheet.


i = 4
list = ""

Do While Not Sheets("Sheet2&quot.Cells(i, 1) = ""
If i <> 4 Then lista = lista & &quot;,&quot;

lista = lista & Sheets(&quot;sheet2&quot.Cells(i, 1)
i = i + 1
Loop


With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=lista
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = &quot;&quot;
.ErrorTitle = &quot;&quot;
.InputMessage = &quot;&quot;
.ErrorMessage = &quot;Error&quot;
.ShowInput = True
.ShowError = True

End With

the problem is the following, when len(list)>255 i get the following error
Run-time error'-2147417848(80010108)'
Method 'Add'of object 'Validation'failed'

and guess what? when you end this process and try to quit excel ( 2002 my case ) Cannot quit Microsoft Excel. i have to end the excel.exe process.

Is the a way i could use a more that 255 chars validation list?

thankx
lovalles

 

[DrBowes]

You can do this without vba, call the range on the other sheet whatever you want (eg. myrange), then enter the validation as a list =myrange

If you want the range to be dynamic, when entering the name of the range, if the range starts in D1 say then enter

=OFFSET(Sheet2!$A$4,0,0,COUNTA(Sheet2!$A4:$A65536),1)

This will make the length of the named range equal to the number of entries in it.

 

[DrBowes]

I meant A4 not D1!!!