Fill a ListBox control with values from closed workbook using VBA in Microsoft Excel

In this article, we will fetch data from closed workbook to List box in userform using VBA.

Raw data for this example is in range A2:B10 on “23SampleData.xls” workbook, which is placed in file path “D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\”.

We have created two command button on the main worksheet for running two different userforms. Each command button is linked to different userforms.

Logic explanation

In this example, two different ways are used to fetch data from the closed workbook. These are:-

1. Open the closed workbook and get the data

2. Using ADODB Connection

Open the closed workbook and get the data

It’s possible to set the RowSource property of a ListBox control to get data from other workbook by assigning value to RowSource property as follows:

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

ListBox Control will display values only if the other workbook is open.

So to fetch the data from closed workbook, we will create a macro to open the other workbook without the user noticing it and fetching data from the workbook to add items in the List Box and closing the workbook.

Clicking the “Select” button will activate the userform “UserForm1”. Initialize event of the userform is used for adding items in the list box. This event firstly opens the closed workbook and then assigns the value in the range to “ListItems” variant. After assigning the value, the workbook is closed and items are added to list box.

List box is used to select name from the existing list values. Pressing the “OK” button will display the selected name.

Using ADODB Connection

ActiveX Data Objects (ADO) is a high-level, easy-to-use interface for OLE DB connection. It is a programming interface to access and manipulate data in a database.

In order to create ADODB connection, we will need to add the ADO library to the project.

To add reference, Choose from the Tools menu > Reference.

Clicking “ADODB Connection” button on the worksheet will activate the “UFADODB” userform. In the initialize event of this userform, we have used ADODB connection to fetch data from the closed workbook. We have created a custom User Defined Function (UDF) “ReadDataFromWorkbook” to establish the connection and fetch the data from the closed workbook to array.

We have used another UDF “FillListBox” to add items in the List box during initialization of the userform. List Box will display data in two columns, one column contains the name and second column contains the age.

Pressing “OK” button after selecting the item in the List box will display the information message about the selected item.

Please follow below for the code

Option Explicit

Sub running()

UserForm1.Show

End Sub

Sub ADODBrunning()

UFADODB.Show

End Sub

'Add below code in UFADODB userform
Option Explicit

Private Sub CommandButton1_Click()
      
Dim name1 As String
Dim age1 As Integer
Dim i As Integer

'Assign the selected value in list box to variable name1 and age1
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        name1 = ListBox1.Value
        age1 = ListBox1.List(ListBox1.ListIndex, 1)
        Exit For
    End If
Next

'Unload the userform
Unload Me

'Displaying output
MsgBox "You have selected " & name1 & ". His age is " & age1 & " yrs."

End Sub

Private Sub UserForm_Initialize()

'Filling ListBox1 with data from a closed workbook


Dim tArray As Variant

'Calling function ReadDataFromWorkbook for getting data from specified range to array
'Change path according to your requirement, "Sample_data" is named defined range
tArray = ReadDataFromWorkbook("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", "Sample_Data")

'Calling function FillListBox  for adding items in List Box
'Assign List box object and tarray as parameter
FillListBox Me.ListBox1, tArray

'Releasing array variables and deallocate the memory used for their elements.
Erase tArray

End Sub

Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant)

'Filling List box lb with data from RecordSetArray

Dim r As Long, c As Long

With lb
    .Clear
    
    'Assigning value to listbox
    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
    
    'Selecting no item in the List box by default
    .ListIndex = -1
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)


Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String

'Declaring a connection string and the driver require for establishing connection
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile

'Creating a new ADODB connection
Set dbConnection = New ADODB.Connection

On Error GoTo InvalidInput

'Open the database connection
dbConnection.Open dbConnectionString

'Getting the recordset from defined named range
Set rs = dbConnection.Execute("[" & SourceRange & "]")

On Error GoTo 0

'Returns a two dimensional array with all records in rs
ReadDataFromWorkbook = rs.GetRows

'Close the recordset and database connection
rs.Close
dbConnection.Close

Set rs = Nothing
Set dbConnection = Nothing

Exit Function

'Code for handling error
InvalidInput:
MsgBox "The source file or source range is invalid!", _
    vbExclamation, "Get data from closed workbook"


End Function

'Add below code in UserForm1

Option Explicit

Private Sub CommandButton1_Click()
      
Dim name1 As String
Dim i As Integer

'Assign the selected value to variable name1
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        name1 = ListBox1.Value
        Exit For
    End If
Next

'Unload the userform
Unload Me

'Display the selected name
MsgBox "You have selected " & name1 & "."

End Sub




Private Sub UserForm_Initialize()

Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook

'Turning screen updates off
Application.ScreenUpdating = False

With Me.ListBox1
    'Remove existing entries from the listbox
    .Clear

    'Open the source workbook as ReadOnly
    Set SourceWB = Workbooks.Open("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", _
        False, True)
    
    'Get the range of values you want
    ListItems = SourceWB.Worksheets(1).Range("A2:A10").Value
    
    'Close the source workbook without saving changes
    SourceWB.Close False

    Set SourceWB = Nothing

    Application.ScreenUpdating = True
        
    'Convert values to a vertical array
    ListItems = Application.WorksheetFunction.Transpose(ListItems)
    
    For i = 1 To UBound(ListItems)
        'Populate the listbox
        .AddItem ListItems(i)
    Next i
    
    'Selecting no items by default, set to 0 to select the first item
    .ListIndex = -1
End With

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com