Excelacity
New Member
- Joined
- Apr 5, 2020
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
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
".Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=FullList" is causing a problem. Any help would be much appreciated
VBA Code:
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