Creating an Excel list

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
326
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
Code:
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

xapie128

Board Regular
Joined
Aug 26, 2006
Messages
82
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

Active Member
Joined
Jun 6, 2005
Messages
326
Thanks for the advice. I had thought of that option, but was trying to avoid it if I could.
 

Forum statistics

Threads
1,141,158
Messages
5,704,636
Members
421,360
Latest member
Rhodia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top