Validation List Cutting off Values

[hawley]

I have a sheet that contains values that I will use for the data validation list. When I run this code the list cuts off. Is there some sort of restriction when creating the validate list for a drop down? My list is not reaching 65000 characters which is the string cut off.

Code:

Dim intAdEventCol As Integer, intAdEventNmCol As Integer, intRow As Integer
Dim intLastRow As Integer, intLastCol As Integer
Dim strAdEvent As String, strAdEventNm As String
Dim strAllAdEvent As String

intAdEventCol = 2
'Find last row in the column
intLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Do Until intRow > intLastRow
strAdEvent = Worksheets(gstrcRawDataWorkSheet).Cells(intRow, intAdEventCol).Value
If intRow = 2 Then
strAllAdEvent = strAdEvent
Else
strAllAdEvent = strAllAdEvent & "," & strAdEvent
End If
intRow = intRow + 1
Loop

Worksheets(gstrcWorkingWorkSheet).Select
Worksheets(gstrcWorkingWorkSheet).Range(Cells(11, 10), Cells(12, 13)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strAllAdEvent
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please choose one of the Ad Events."
.ShowInput = True
.ShowError = True
End With

Any help or ideas would be appricated. I know I have run into this before but can't find the solution. Another thing is the dropdown is directly on the sheet not a dialog box.

 

[SkipVought]

Hi,

If you have a drop down with more than 1000 entries, you need to rethink your design. Divide your list in some logical way and use multiple drop downs to narrow the selections available.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}