Dropdown validation from a named range referencing another workbook

Question

I've got two workbooks, "Test" and "Screen_Reference_Data_Sheet". I am trying to create a dropdown list in "Test" by using a named range with data from the "Screen_Reference_Data_Sheet" workbook.

Below is the code I'm using, and it is throwing error Run Time Error 1004 on the ".Add Type:..." line.

I am able to accomplish this when only using data from one workbook, but it's when I am pulling data from another workbook that I am getting this error.

Sub GetScreenNames()
Path = Application.ActiveWorkbook.Path
RefFilePath = Path & "\Screen_Reference_Data_Sheet.xls"
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(RefFilePath)

Dim rng1 As Range
Set rng1 = objWorkbook.Sheets("Keywords_Action_Screen").Range("B2:B4")
ThisWorkbook.Names.Add Name:="screenslistdata", RefersTo:=rng1

With ThisWorkbook.Sheets("Sheet1").Range("A1:A3").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween,    Formula1:="=screenslistdata"
End With

objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
End Sub

Any ideas on what I'm doing wrong here?

David Zemens David Zemens · Accepted Answer · 2013-10-15T17:27:59

You may not use references to other workbooks for Data Validation criteria.

If you try to do this manually, the error/warning will appear:

enter image description here

The next best solution I think is to convert the range's value to a variant array, and use that array to assign the list formula:

With ThisWorkbook.Sheets("Sheet1").Range("A1:A3").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween,
        Formula1:=Join(Application.Transpose(rng1.Value), ",")
End With

Dropdown validation from a named range referencing another workbook

1 Answers