Data Validation Text List using VBA

[Excelacity]

I am trying to create a drop down validation list from a dynamic range on another sheet. Having read lots of posts I end up with the code below but cannot understand why the code line
".Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=FullList" is causing a problem. Any help would be much appreciated

Dim ListOrder As Byte
Dim StrName() As Variant
Dim FullList As String
Dim RngList, CellNow As Range
Dim Wb As Workbook
Dim WsFrom, WsTo As Worksheet
Set Wb = ThisWorkbook
Set WsTo = Wb.Worksheets("CurrentWeek")
Set WsFrom = Wb.Worksheets("Info")
Set RngList = WsFrom.Range("ListIrisID") 'Dynamic range name
ReDim StrName(RngList.Cells.Count)
'Build array list
For Each CellNow In RngList.Cells
StrName(ListOrder) = CellNow.Value
ListOrder = ListOrder + 1
Next CellNow
FullList = ""
'Create single comma separated string of entire ID List
For ListOrder = LBound(StrName) To UBound(StrName)
Debug.Print StrName(ListOrder)
FullList = FullList & StrName(ListOrder) & ","
Next ListOrder
FullList = Left(FullList, Len(FullList) - 2)
'Test
Debug.Print Len(FullList); FullList

'Add Validation list to cells
With WsTo.Range("E8:E207").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=FullList
.IgnoreBlank = True
.InCellDropdown = True
End With

 

[Akuini]

As far as I know, using strings as the source of data validation (like your code does) is limited to 255 characters max.
If the string length is more than 255 characters then initially the data validation will work but when you save the workbook then it won't work.

For dynamic range, here's an example of formula to define the named range:
=Sheet1!$A$1:INDEX(Sheet1!$A:$A, COUNTA($A:$A), 1)

then use the named range as the source of data validation.

 

[Fluff]

The problem is the length of the string you're putting into the DV.
Is the dynamic name range with the data set at worksheet or workbook scope?

 

[Excelacity]

The problem is the length of the string you're putting into the DV.
Is the dynamic name range with the data set at worksheet or workbook scope?

The dynamic name range is set at workbook level. The dynamic range is on the same workbook but a different worksheet (protected) to the data that needs the validation. The Dynamic range name was already set up in a similar fashion to Akuini's suggestion.
Initially, I tried to populate the data validation directly from the dynamic range, but couldn't work out the code to do that, which Is why I tried to populate the DV using a VBA array. I would appreciate it if anyone could suggest code to populate the DV directly from the dynamic range.

 

[Fluff]

How about

With WsTo.Range("E8:E207").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=ListIrisID"
.IgnoreBlank = True
.InCellDropdown = True
End With

 

[Excelacity]

Perfect. That makes my code a lot neater. Thanks so much.

 

[Fluff]

You're welcome & thanks for the feedback