Populate listbox with a named range from another workbook

[zdodson]

I have two workbooks:

1) Settings
2) Schedule

The named range I am using ("Dates") is in the Schedule workbook, and it is updated dynamically using an offset formula.

The consolidation of such workbooks would make the file lag badly, so I don't want the named ranges consolidated into one worksheet.

I have tried all sorts of things to resolve this, but I am having some severe trouble.

Here was the most promising (adapted) solution I could find, but still no luck:

Dim wb As Workbook
Dim wks As Worksheet
Dim path As String


'Turn off screen updating
Application.ScreenUpdating = False


path = "C:\Users\Zack\Documents\Debate Program"
Set wb = Workbooks.Open(path & "Schedule and Entries Database.xlsm", True, True)


'Open source workbook
With ThisWorkbook.Worksheets("Schedle")
    'read data from source workbook
    .Range("Tournaments").Formula = wb.Worksheets("Schedule").Range("Tournaments").Formula
    End With


'Close the source book without saving changes
wb.Close False
Set wb = Nothing


'Turn on screen updating
Application.ScreenUpdating = True
End Sub

Any pointers?


Zack

 

[zdodson]

bump--need help

 

[Jerry Sullivan]

Hi Zack,

There's two parts you need to address:
1. Getting data from the dynamic range "Dates" in the closed workbook "Schedule" into the open workbook "Settings"
2. Taking that data and populating the listbox in the "Settings" workbook.

If your approach for getting the data from the closed workbook is to open it temporarily using VBA, then the 2nd part can be done using Listbox.List and/or Listbox.AddItem in the same way that it would be the if "Dates" range was in the Settings workbook.

Do you have some code to populate a Listbox from a named range within the same workbook?
What type of listbox are you using? (Userform, Embedded in Worksheet-ActiveX, Embedded in Worksheet-Forms Control?)