Creating an Excel list

[KenCriss]

I am creating a Validation list with VBA. For this code, it only puts the first 64 items on the list and ignores the others. Is this some type of Excel limitation I have hit? It works to reference a range on the Worksheet but I don't want to have to do that because I am creating it on the fly and don't want to have this list viewable to the user.

Columns("G:G").Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlEqual, Formula1:="100,101,102,110,111,150,151,152,153,154,155,159,160,161,
190,194,195,196,197,198,199,200,201,202,203,210,300,301,302,303,304,
305,306,307,308,400,401,402,403,404,405,406,407,408,409,410,411,412,
413,414,415,416,500,600,601,602,603,700,701,702,703,800,850,900,950,
1000,1001,2000,2010,2020,2030,2040,2060,2070,2080,2090,2100,2110,
3000,3010,3040,4000"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

 

[xapie128]

The problem isn't the limit of 64, the problem is the Formula1 string "100,101,102,110,111,150..." is limited to 256 characters. You can test this by making the first 4 or 5 to have only single character numbers (1,2,3,4...) and see that you will now have more than 65 options in the pull-down. Creating a string variable and pointing Formula1 to it won't work either because it will still only read the first 256 characters of the string.

The best way I can think of to do it is to write your values into a far righthand column that isn't being used (hide the column or set the color to white if you don't want the numbers visible). In your example you have 82 numbers, and suppose you use column FZ, you would then use Formula1:="=$FZ$1:$FZ$82"

Clear the column before you write a new list. This scheme will require your code to have a test so it knows what the bottom cell is (FZ82 in this case).

 

[KenCriss]

Thanks for the advice. I had thought of that option, but was trying to avoid it if I could.