drop down list using named range from another workbook

[Naveen N]

Dear Reader,


I am creating dropdown using named ranges from another workbook. I am getting an error telling the excel cannot find named ranges “_Release_WR” in ‘Release_Weekoff_Dropdown.xlsx”.

I have workbook “Release_Weekof_Dropdown” and in sheet Release_Weekoff

I have defined two named ranges
_Release_WR
_Week_of

I have am “Output_Dropdown” –I am creating named ranges referring _Release_WR and _Weelof.
Using the named ranged I am creating drop down list.
It work fine when the “Release_Weekof_Dropdown” workbook is open.

When I close the workbook “Release_Weekof_Dropdown” and open Output_Dropdown workbook.

The drop down would be empty and I get an error the named ranges in source are not found.

Please help me to resolve the error.
Please find enclosed sample work book.

Regards,

Naveen N

 

[Luke M]

In short, what you ask can't be done. You can't access a named range from a closed workbook.

 

[NARAYANK991]

Hi ,

Is this acceptable ?

Narayan

 

[Naveen N]

HI,
I am creating named ranges referring to other excel.

I added Open and before_close event to resolve the issue (open the linked workbook and close the same when the main sheet is closed).

When I open book , I get a gray box. and not see the sheets in excel.
In the developer tab I can see the excel is open (names of the excel.)

Please help me resolve the issue.

Please find enclosed work book

Private Sub Workbook_Open()

Dim LinkList As Variant
On Error Resume Next

Application.DisplayAlerts = False
Application.ScreenUpdating = False
ApplicationEnableEvents = False
Application.AskToUpdateLinks = False

LinkList = ThisWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(LinkList) Then
For i = LBound(LinkList) To UBound(LinkList)
Application.Workbooks.Open Filename:=LinkList(i)
'ActiveWindow.Visible = False
Next i
End If

ThisWorkbook.Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
ApplicationEnableEvents = True

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim LinkList As Variant
    Dim wbName As String
   
    Dim i As Long
    Application.ScreenUpdating = False
    On Error Resume Next
    LinkList = ThisWorkbook.LinkSources(xlExcelLinks)
    On Error GoTo 0
    If Not IsEmpty(LinkList) Then
        For i = LBound(LinkList) To UBound(LinkList)
            wbName = LinkList(i)
            'Trim down to just workbook name, not full path
           wbName = Mid(wbName, InStrRev(wbName, "\") + 1)
           
            Application.Workbooks(wbName).Close savechanges:=False
        Next i
    End If
   
    ThisWorkbook.Activate
End Sub
Private Sub Workbook_Activate()
Application.AskToUpdateLinks = False
ApplicationEnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
End Sub