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

 

[Fluff]

What problems do you get?
Also what is the length of FullList?

 

[Excelacity]

What problems do you get?
Also what is the length of FullList?

There's an image of the error. The length of FullList is 304 characters.

.

 

[rlv01]

What line of code triggered the error?

 

[Excelacity]

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=FullList

 

[Fluff]

Is the worksheet protected?

 

[Excelacity]

Is the worksheet protected?

Yes

 

[Fluff]

In that case you will need to unprotect the sheet before adding the DV & then reprotect it afterwards

 

[Excelacity]

That worked. Thanks for your help.

 

[Excelacity]

The validation goes on fine if I remove protection & re-apply all with VBA. If I then save the file & re-open it, the file gets corrupted on opening, loses all the sheet formatting and DV. I have tried taking protection off completely and it still gets corrupted after running the code. Any offers on what is wrong. The code as it stands now is below. Additional information - I have hidden columns from T onwards & hidden rows from 208 downwards.

With Worksheets(StrWsTo).Range(StrRngDvalName).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=FullList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Engineer ID"
.ErrorTitle = "Invalid Entry"
.InputMessage = "Type or select Engineer's IRIS Diary ID from in-cell drop down"
.ErrorMessage = "This name is not on the IRIS Diary ID list, please try again"
.ShowInput = True
.ShowError = True
End With