Run-time error '1004' when I'm referring to a range that is not named

Question

I'm trying to put a dropdown menu in an Excel worksheet, with options from another worksheet, in another workbook. I'm using VBA because I want to customize those options, based on another dropdown menu. I managed to do it by naming the range in the source sheet, but that file is read-only so I can't save the name to automate the process. So I tried to use only the column name in the reference (like $B:$B, I only need the B column), but when I run the macro it stops with Run-time error 1004.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("H6")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Call main
    End If
End Sub

Private Sub main()
    reason = Range("H6").Value
    List = ""

    If reason = "Project ID/Task ID" Then
        List = "=PIDTID"
    Else
        List = "=Cost_Center"
    End If

    With Range("I6").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=List
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

This is my macro, as you can see, main() is called when H6 is changed, because that is where my other dropdown menu is. Then, based on the value from that cell, List gets assigned a value equal to the defined name that refers to the range in the other worksheet.

As an example, if PIDTID name is equal to ='[20190812_WP_and_CostCenters_Responsible.xls]Projects responsibles'!WBSName, the script works. Note that WBSName is the name given to column B in the source sheet.

If I set PIDTID to ='[20190812_WP_and_CostCenters_Responsible.xls]Projects responsibles'!$B:$B I will get the error on line

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

Cost_Center name acts the same.

I want to mention that these tests were done having the source file opened. If I tried with it being closed, I would get the same error.

Is there a way to get rid of the error, or another way by which I can refer to cells in sheets of other workbooks without naming them? I would also like to work without having to open the source file. Thank you!

Answer 1

"Is there a way to get rid of the error, or another way by which I can refer to cells in sheets of other workbooks without naming them? I would also like to work without having to open the source file. Thank you!"

Let me give it a go :)

As per this documentation, you cannot use referces to another workbook (without a named range). And certainly not in a closed workbook. Refer to @Gh3ttoKinG his answer for that :).

So this might be done much smarter but to do this without opening a workbook I thought about using ExecuteExcel4Macro. With a little help from this answer by @SiddharthRout, we can start pulling in information from the closed workbook:

Sub Test()

Dim wbPath As String, wbName As String
Dim wsName As String, cellRef As String
Dim Ret As String
Dim lr As Long
Dim arr() As String

wbPath = "C:\Users\....\SO\"
wbName = "SO.xlsx"
wsName = "Sheet1"

'Get the last row from B column, notice we need R1C1 notation for Excel4Macro
lr = ExecuteExcel4Macro("COUNTA('" & wbPath & "[" & wbName & "]" & wsName & "'!C2)")

'Let's use an array to fill our validation list with later on
ReDim arr(1 To lr)
For x = 1 To lr
    arr(x) = ExecuteExcel4Macro("'" & wbPath & "[" & wbName & "]" & wsName & "'!R" & x & "C2")
Next x

'Join the array together to make a string which will be accepted in the validation list
With Range("I6").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(arr, ",")
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

End Sub

There are some things I would try to "smoothen" as for example COUNTA is not the best to get the lr variable, plus there might be a way to pull a complete range into the arr() variable at once.

However, this might spark some creative ideas ;)

---

EDIT

Another way to retrieve the last row on string data could be to use MATCH like so:

lr = ExecuteExcel4Macro("MATCH(""zzz"",'" & wbPath & "[" & wbName & "]" & wsName & "'!C2)")

Answer 2

As JvdV wrote in the comments it's because you can't use data validation where the list reference to another workbook, at least not in a simple way.

You can circument it by adding a named range to your writeable workbook with this formula (assuming Cost_Center is in column C)

=IF(Sheet1!H6="PIDTID",OFFSET('[20190812_WP_and_CostCenters_Responsible.xls]Projects responsibles'!$B:$B,0,0),OFFSET('[20190812_WP_and_CostCenters_Responsible.xls]Projects responsibles'!$C:$C,0,0))

And then use this named range in your data validation / macro. Downside is that the source workbook has to open for the dropdown menu to work