I'm trying to get a validation list drop down to be created from a named range in another workbook, but I'm having issues even getting it to do it from the same workbook.
It's the .Add Type line, but I'm not sure how to correct it.
Sub Test()
Dim vArray() As Variant
Dim rng As Range
Dim wbk As Workbook
Set wbk = Excel.Application.Workbooks("Sample.xlsm") ' this will be Workbooks("Library.xlam")
Set rng = wbk.Worksheets("Sheet1").Range("Test") ' this will be wbk.Worksheets("A1").Range("Test"))
vArray = rng
With ActiveSheet.Range("mm.1").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(vArray, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Bookmarks