Fill a ListBox-control with values from another workbook

From another open workbook

It's possible to set the RowSource-property for a ListBox- or ComboBox-control to get data from another workbook by filling in something like this:

'[Filename.xls]Sheet1'!$B$1:$B$15

The other workbook must be open for the control to display any values.

With the macro below you can fill a ListBox-control with data from another closed workbook. The macro opens the other workbook without the user noticing it, fills the ListBox with data and closes the workbook without saving any changes. This macro is supposed to be saved in the UserForm's modulesheet.

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
    With Me.ListBox1
        .Clear ' remove existing entries from the listbox
        ' turn screen updating off, 
        ' prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False 
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\FolderName\SourceWorkbook.xls", _
            False, True)
        ListItems = SourceWB.Worksheets(1).Range("B2:B21").Value 
        ' get the values you want
        SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        Application.ScreenUpdating = True
        ListItems = Application.WorksheetFunction.Transpose(ListItems) 
        ' convert values to a vertical array
        For i = 1 To UBound(ListItems)
            .AddItem ListItems(i) ' populate the listbox
        Next i
        .ListIndex = -1 ' no items selected, set to 0 to select the first item
    End With
End Sub

From a closed workbook that is opened and closed again without the user noticing it

With the macro below you can fill a ListBox control with input from another closed workbook. The macro opens the closed workbook without the user noticing it, gets the values for the ListBox and closes the source workbook without saving any changes. Copy the macro into the UserForm's module sheet.

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
    With Me.ListBox1
        .Clear ' remove existing entries from the listbox
        ' turn screen updating off, 
        ' prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False 
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\FolderName\SourceWorkbook.xls", _
            False, True)
        ListItems = SourceWB.Worksheets(1).Range("B2:B21").Value
        ' get the values you want
        SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        ListItems = Application.WorksheetFunction.Transpose(ListItems) 
        ' convert values to a vertical array
        For i = 1 To UBound(ListItems)
            .AddItem ListItems(i) ' populate the listbox
        Next i
        .ListIndex = -1 ' no items selected, set to 0 to select the first item
        Application.ScreenUpdating = True
    End With
End Sub

From a closed workbook by using ADO

With the macros below you can fill in an ListBox/ComboBox with input from another workbook without opening it. Copy the macros into the UserForm's module sheet.

Private Sub UserForm_Initialize()
' fill ListBox1 with data from a closed workbook
' can also be used from other applications to read data from an open workbook
Dim tArray As Variant
    tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
    FillListBox Me.ListBox1, tArray
    Erase tArray
End Sub

Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant)
' fills lb with data from RecordSetArray
Dim r As Long, c As Long
    With lb
        .Clear
        For r = LBound(RecordSetArray, 2) To UBound(RecordSetArray, 2)
            .AddItem
            For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray, 1)
                .List(r, c) = RecordSetArray(c, r)
            Next c
        Next r
        .ListIndex = -1 ' no item selected
    End With
End Sub

Private Function ReadDataFromWorkbook(SourceFile As String, _
    SourceRange As String) As Variant
' requires a reference to the Microsoft ActiveX Data Objects library 
' (menu Tools, References in the VBE)
' if SourceRange is a range reference:
'   this function can only return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
'   this function can return data from any worksheet in SourceFile
' SourceRange must include the range headers
' examples:
' varRecordSetData = _
    ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")
' varRecordSetData = _
    ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
' varRecordSetData = _
    ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName")
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
    dbConnectionString = _
        "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    On Error GoTo 0
    ReadDataFromWorkbook = rs.GetRows 
    ' returns a two dim array with all records in rs
    dbConnection.Close ' close the database connection
    rs.Close
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Function
InvalidInput:
    MsgBox "The source file or source range is invalid!", _
        vbExclamation, "Get data from closed workbook"
    Set rs = Nothing
    Set dbConnection = Nothing
End Function

From a closed workbook by using worksheet formulas/links

You can get input from another closed workbook by creating a (hidden) worksheet with formulas like this:

='C:\FolderName\[SourceWbName.xls]WorkSheetName'!A1

Copy this formula as far down and to the right as necessary to get the input values you want from the closed workbook.

Set the UserForm's RowSource property to the worksheet range (in the hidden worksheet) containing the values from the closed (or open) workbook. The disadvantage with this method is that the user will be prompted to update the links every time the workbook containing the formula links is opened.

Document last updated 2000-09-16 12:47:20 Printerfriendly version

User comments:

Pixelpusher from Gloucestershire UK wrote (2005-06-03 13:48:46 CET):

Allmost what Im looking for
Can anyone tell me if you can substibtute the cell referance for a Range referance I cant get my example to work
'[Filename.xls]Sheet1'!$B$1:$B$15

'[Filename.xls]Sheet1'!RangeName

Michael G. from Germany, near Munich wrote (2005-01-31 15:18:50 CET):

Thank ...
... you for the code! It´s what I needed.

Ole P. from Norway wrote (2004-09-10 10:20:32 CET):

Re: How do you create a listbox in an Excel Document?
Take a look at the ComboBox example on this page.

Cclark from Atlanta, GA wrote (2004-09-10 09:39:34 CET):

How do you create a listbox in an Excel Document?
Would it be possible is someone would be so kind as to walk me through the steps to accomplishing a listbox? You guys have provided lots of wonderful information, although I've invested so much time in just learning what it all means that my project is being delayed, because I don't have time to workout the bugs. Help, please help if you can. Thanks for all that you do!

Ole P. from Norway wrote (2004-06-21 11:12:07 CET):

Re: Fill a ListBox-control...
The examples on this page works just fine on my computer, both as is and with your suggested change.

Peter.Kolada@t-online.de from Erkelenz, Germany wrote (2004-06-20 23:23:34 CET):

Fill a ListBox-control with values from another workbook
When executing your code, I had to incorporate the following change:

For i = 1 To UBound(ListItems)
.AddItem ListItems(i,1) ' populate the listbox
Next i

With an one-dimensional array I received the error message: Index out of valid range (Translation from German).

Perhaps I should send you an E-Mail with the slightly modified code.

Ceyhun Kazel from Istanbul,Turkiye wrote (2003-10-22 12:04:46 CET):

Thanks for code
This is what I look for and after searching google an hours I found it here. Thanks for your code